Validatiion Drop Down List Creates Run-Time Error In VBA

Are you sure that that is the code that you are actually running?

When I try it it crashes my Excel 2010 application completely and I think I can see why. However, before I explain why I think the code is not working, I have to ask a question.

What are you trying to do with this line:

If Target.Row And Target.Cells.Count = 1 Then

I understand that If Target.Cells.Count = 1 is used to make sure that only one cell has changed, but I don’t know what If Target.Row is used for. As far as I know, If Target.Row will always return True because Target.Row is going to return a number. Since VBA will consider any number that is not 0 to be “Not False”, and therefore True, I don’t think your are accomplishing anything with If Target.Row. Am I missing something subtle? I’m always willng to learn. 😉

OK, that said, let’s look at your Worksheet_Change code.

Debugging Worksheet_Change macros can be a pain since they don’t easily allow you to Single Step through the code like a regular macro does. The trick is to force an syntax error which stops the code as soon as it is triggered allowing you to correct the error and then use the Single Step feature.

Try this:

Edit your code so that If becomes Iff. Ignore the red line error indication for now.

Private Sub Worksheet_Change(ByVal Target As Range)
 Iff Target.Row And Target.Cells.Count = 1 Then
     Range("Q" & Target.Row) = Date
 End If
End Sub

When you make a change to your spreadsheet, the code should throw up a Syntax Error while leaving the first line Private Sub Worksheet_Change(ByVal Target As Range) highlighted in yellow and the Iff line highlighted as an error.

Now, delete the extra F in that line. This will leave you in Debug mode and you can use F8 to single step through the code.

Pressing F8 continually should show you what the problem is. When the code puts the Date in Column Q, that is considered a change to the worksheet, which triggers the code to run again, putting the Date in Column Q which is considered a change to the worksheet, which triggers the code to run again, putting the Date in Column Q which is considered a change to the worksheet, which triggers the code to run again, putting the Date in Column Q which… and on and on forever.

On the system I am using right now, in which Excel runs as a cloud based application, I believe the application crashes because it can’t keep up with the rapidly repeating requests to keep putting the Date in the same cell. It make act differently when Excel is running on your local machine, but I can’t test that right now. In any case, I believe that to be the problem.

One way to deal with that issue is to disable Events until after the Date has been placed in the cell and then enable Events before exiting the code. That way the change doesn’t trigger the Event code to run again and again and again.

When I use this code, I don’t have any problems, even when using a Data Validation drop down:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    If Target.Row And Target.Cells.Count = 1 Then
       Range("Q" & Target.Row) = Date
    End If
  Application.EnableEvents = True
End Sub

One key point: If your code crashes or you stop the code after…

Application.EnableEvents = False

but before:

Application.EnableEvents = True

…Events will remain disabled and no event driven code will run. You can re-enable Events in 3 ways:

1 – Use a macro to enable Events:

Sub Enable_Events()
  Application.EnableEvents = True
End Sub

2 – Open the Immediate window in the VBA editor, paste in this line and hit Enter:

Application.EnableEvents = True

3 – Quit Excel and reopen the application.

One other option that can be employed is to check and see what Column was changed and only run the code if the change was made to one of the Columns that you want to trigger the code. (A, B, C, D OR N). When the code puts the Date in Column Q it will still retrigger the code but since the change was not made to one of the desired columns, the code will quit. As your code is written now, the date is going to be placed in Column Q regardless of what Column was changed.

Click Here Before Posting Data or VBA Code —> How To Post Data or Code.