Worksheet_Change

[Snowshoeken]

I am trying to use a Worksheet_Change in combo with a Drop Down Box to trigger a Macro with no success. Code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$100" Then
If Range("RegionResult").Value = "#N/A" Then
Call CommandButton1_Click
Else
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Summary").Select
Range("A3:A54").Select
Selection.AutoFilter Field:=1, Criteria1:=RegionResult
End If
End If
End Sub


Suggestions??

 

[Andrew Poulsom]

Have you tried using Data Validation with a list instead of a ComboBox?

 

[Snowshoeken]

That would be easier however I was trying to avoid adding another row.

 

[Andrew Poulsom]

Why do you need to add another row? Can't you use B100 (Target)?

 

[Snowshoeken]

Isn't one a Validation list in a cell and the other is an Object? In this case it is all about the visual. I am atempting to give the a drop down list to filter a range.

 

[Andrew Poulsom]

Data Validation with a List has a dropdown.

The alternative is to assign a macro to your Drop Down (from the Forms Toolbar).

 

[Snowshoeken]

And if I use the Validation List method I have to insert a cell for it to reside and display; which I wanted to avoid. The Drop Down that is outside of a user form does not have a VB relationship other than via Worksheet_Change; which isn't working. But I appreciate the wlaking through it help.

PS - I hate having to log in every 10 mins to Mr. Excel!

 

[Andrew Poulsom]

Where is your dropdown from - Forms Toolbar or Control Toolbox? If it's from the Forms Toolbar you can right click it and assign it a macro. If it's from the Control Toolbox you can right click it (in design mode) and View Code.

 

[Snowshoeken]

Straight from the tool box on to the worksheet, no user form. If I go to assign a macro to it, it doesn't list the worksheet_change macro, even if I remove the "Private". And it only references a "Drop Down ###" and that object haming doesn't gel with VB. I guess I could make a really small user form.

 

[Andrew Poulsom]

You would need to write a macro in a General module that does what you want, then assign it to the drop down.