29167

Catch user error and re-enable application.events after error

Question:

Amateur coder asking for help, again :-)

I have the below code that works good, it gets a variable from a drop down validation list and executes command automatically because I and using the Private Sub Worksheet_Change(ByVal Target As Range). The issue I have is that sometimes the user types into the box rather then select it.

I get a

<blockquote>

run-time error 91

</blockquote>

because it cannot find the value typed in by user.

How can I catch this error and make a message popup to tell them they must choose from drop down list? And Also since it breaks the code it sets my Application.EnableEvents to False since it doesn't finish the code from the ByVal Target As Range section that resets to True.

How can I reset the Application.EnableEvents to True in the event of a code break?

Sub Copy_From_Borrower_DBase() Dim myVal As String Dim sourceRng As Range myVal = Sheets("Main").Range("F2").Value ' dropdown list Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find(What:=myVal, LookAt:=xlWhole) 'locate column where to copy from Worksheets("Main").Range("F5").Value = Worksheets("Borrower Database").Cells(5, sourceRng.Column).Value 'Borrower Name Worksheets("Main").Range("G6").Value = Worksheets("Borrower Database").Cells(6, sourceRng.Column).Value 'Income End Sub

Answer1:

Check first that the range does not return Nothing (meaning the term was not found). The use of With is basically cosmetic, but I think tidies up the code somewhat.

Sub Copy_From_Borrower_DBase() Dim myVal As String Dim sourceRng As Range myVal = Sheets("Main").Range("F2").Value ' dropdown list With Worksheets("Borrower Database") Set sourceRng = .Range("5:5").Find(What:=myVal, LookAt:=xlWhole) If Not sourceRng Is Nothing Then Worksheets("Main").Range("F5").Value = .Cells(5, sourceRng.Column).Value 'Borrower Name" Worksheets("Main").Range("G6").Value = .Cells(6, sourceRng.Column).Value 'Income" Else MsgBox "whatever" End If End With End Sub

Answer2:

To answer the question about error handling:

Public Sub MyProcedure() Application.EnableEvents = False On Error Goto ENABLE_EVENTS 'if any error occurs after this line events get enabled. 'stuff where an error could occur ENABLE_EVENTS: Application.EnableEvents = True If Err.Number <> 0 Then MsgBox "an error occurred" End If End Sub

For more information about error handling: <a href="https://excelmacromastery.com/vba-error-handling" rel="nofollow">VBA Error Handling – A Complete Guide</a>.

Answer3:

I would first try to solve the problem by not allowing there to be a problem, if that fails then worry about error handling.

Since you are already calling Worksheet_Change and using Data Validation, this checks the value of the validation cell. If it has a value in it then your code will run. If it is empty it will not. Data Validation clears the cell if someone types in it, so no matter what they type by the time it hits Worksheet_Change it should be an empty string.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$F$2" Then ' Your Dropdown Validation Cell If Not Target.Value = "" Then Call Copy_From_Borrower_DBase End If End If End Sub

Recommend

  • How to pass CLI arguments to a “commands” inside a “define” in GDB?
  • Cocoa app with only NSSavePanel
  • Scala map() on a Map[..] much slower than mapValues()
  • Java - Multithreading Issue with too many threads
  • 19.8.28 记录一下flutter中字符串的操作
  • Inserting Rows Without Selecting Anything?
  • php mysql today, yesterday and date from database
  • Backup SQL database using php code but it save blank sql file
  • How do I click on a Cell of a DataGridView programmatically?
  • Grails stops at “Configuring classpath” even at a helloworld project
  • Send post-request with string
  • primefaces datatable selectionMode multiple not working with mojarra
  • jre_home environment variable is not defined correctly while starting tomcat
  • Does OAuth “state” mitigate any genuinely dangerous attacks?
  • exception thrown while building the java application using netbeans
  • What I can use subquery instead for search MAX()?
  • Problem with installing Charm-Crypto for Python3
  • Upload synthesized speech from firebase function node.js server's tmp directory
  • Cannot get Django 1.7 Migrations to detect proper changes to my DB.
  • Limit number of button clicks
  • How to smoothly connect two signals in matlab [closed]
  • why 'read' command in shell script is missing initial characters? [duplicate]
  • Laravel 5 - Cache remember doesn't work
  • Extracting a small subset of data from XMLs
  • How to define something in JavaScript [closed]
  • Neo4j…how to get a visual representation of my data?
  • ssh remote server login script
  • Bitrate JWplayer
  • Capture SIGFPE from SIMD instruction
  • Using Service Component Runtime
  • Accessing Arguments, Workflow Variables from custom activities
  • How do I use TagLib-Sharp to write custom (PRIV) ID3 frames?
  • CAS 4 - Not able to retrieve the LDAP groups after successful authentication