Worksheet_Change

Snowshoeken

Active Member
Joined
Aug 8, 2002
Messages
306
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??
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Snowshoeken

Active Member
Joined
Aug 8, 2002
Messages
306

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Data Validation with a List has a dropdown.

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

Snowshoeken

Active Member
Joined
Aug 8, 2002
Messages
306

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
Aug 8, 2002
Messages
306
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You would need to write a macro in a General module that does what you want, then assign it to the drop down.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,398
Messages
5,831,404
Members
430,064
Latest member
Shahrukh kha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top