Solved Validatiion drop down list creates Run-time error in VBA

March 12, 2014 at 15:36:27
Specs: Windows 7
How do I resolve a VBA macro and cell validation conflict.
We have been running the following code without any problem until we added data validation, from lists on another worksheet in the same workbook.
This code worked until today:
Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Row And Target.Cells.Count = 1 Then
     Range("Q" & Target.Row) = Date
 End If
End Sub

Error is:
Run-time error '-2147417848 (80010108)'
Method '_Default' of object 'Range' failed

Debug identifies the following line:

Range("Q" & Target.Row) = Date

We are unsure how to fix this.
We have Columns A to Q, changes in any row are captured in Cell Q of the same row where the change occured.
We need validation in columns A, B, C, D and N wo we can better control filter and sort of values.

See More: Validatiion drop down list creates Run-time error in VBA

Report •

#1
March 13, 2014 at 08:34:42
✔ Best Answer
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.


Report •

#2
March 13, 2014 at 16:44:01
Hi DerbyDad03, thank you for the quick reply, it has proven useful.

I verified the code and it is in fact what we were running. Our system seems to handle it well until any other validation is added, then the feedback loop you described starts to slow everything down. Had it crashed I might have clued in earlier. Thanks for the testing.
Your solution of adding the following lines is what we will use:

  Application.EnableEvents = False
  Application.EnableEvents = True

Rather than exclude column Q like in the following variation:

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

I’ll explain the method to our madness starting with the question about the following command:

Target.Row

Simply put, you are right! It is not needed!
My VBA skills are limited. I have tested the code without it and it still works as intended. All columns are monitored and Column Q gets the date stamp.

We want the code to monitor the entire row rather than just a few columns for the following reasons:
·Robustness; New columns can be added without having to
re-write the code each time.
·Any change in the row needs a date stamp, but why include Column Q?
·The code should include column Q so the average user
cannot accidentally change or deliberately falsify the date.

The code then looks like this but still has the feedback loop problem.

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

The solution that seems to work best incorporates your suggestions and looks like this:

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

Thank you once again Derbydad03,
I hope you have a great... whatever “Ctrl + :” it is there.
M*A*S*H

Report •

#3
March 13, 2014 at 17:50:05
I'm glad it worked out for you.

Just for fun I tested your original code on a machine running Excel 2013 on the local machine. Interesting results, which are reproducible...

The first time I enter data (make a change) in the sheet, it produces the following error:

Run-time error 28 
Out Of Stack Space

When I click End to dismiss the dialog box, it takes me back to the spreadsheet.

If I then make another change, I get the same error that you posted in your OP.

Run-time error '-2147417848 (80010108)'
Method '_Default' of object 'Range' failed

When I click End to dismiss that dialog box, Excel then crashes.

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


Report •
Related Solutions


Ask Question