dropdown list to activate Macro
 

Could someone explain the easiest way for me to automatically activate an
existing macro based on clicking on, then selecting a choice from a dropdown
list? Thanks in advance for any advice! Cheers! Brad

dropdown list to activate Macro
 

Define Drop down list. Are you talking about data-Validation, a combo box
from the Control Toolbar or a combo box from the forms toolbar. It makes a
big difference...
--
HTH...

Jim Thomlinson


"Brad" wrote:

Could someone explain the easiest way for me to automatically activate an
existing macro based on clicking on, then selecting a choice from a dropdown
list? Thanks in advance for any advice! Cheers! Brad

dropdown list to activate Macro
 

Brad,

Not sure what kind of drop-down you are using. But set up your macro
something like this:

Dim mcro as String
mcro = MyForm.Combobox1
' or '
mcro = MyForm.Listbox1
' or '
mcro = Sheets("MySheet").Range("A1").Text

Run mcro

--
rand451
"Brad" wrote in message
...
Could someone explain the easiest way for me to automatically activate an
existing macro based on clicking on, then selecting a choice from a
dropdown list? Thanks in advance for any advice! Cheers! Brad

dropdown list to activate Macro
 

Jim/Steve,

I created the drop-down box by the following steps: Data, Validation, Allow,
List, where I then put in 9 entries delimited by commas.

I had been using a few lines of code in this macro where I used the Userform
and its input to fill in the choices, but it would be easier to remember the
various choices if they existed in a list where I could click on one to
choose it. That's how I ended up creating the little drop-down list tied to
a single cell. Now, if I could somehow automate the running of the macro
based on highlighting a new choice in the drop-down list. Thanks, Brad

"STEVE BELL" wrote in message
news:TS2le.1103$m%3.212@trnddc02...
Brad,

Not sure what kind of drop-down you are using. But set up your macro
something like this:

Dim mcro as String
mcro = MyForm.Combobox1
' or '
mcro = MyForm.Listbox1
' or '
mcro = Sheets("MySheet").Range("A1").Text

Run mcro

--
rand451
"Brad" wrote in message
...
Could someone explain the easiest way for me to automatically activate an
existing macro based on clicking on, then selecting a choice from a
dropdown list? Thanks in advance for any advice! Cheers! Brad

dropdown list to activate Macro
 

Using validation you are changing the value of the cell so you can use the
worksheet_change event to fire the macro. Right click the sheet tab of the
sheet you with the drop downs. Now select View Code and the VB editor will
open up. At the top of the code window is a drop dwon which says general.
Select Worksheet. When you do this a sub will be created for you
automatically for selection change. This is not the one you want so you can
just delete it. In the drop down to the right of the Worksheet drop down
select change. This will fire whenever anything is changed on this sheet.
Target is the cell that was just changed.
--
HTH...

Jim Thomlinson


"Brad" wrote:

Jim/Steve,

I created the drop-down box by the following steps: Data, Validation, Allow,
List, where I then put in 9 entries delimited by commas.

I had been using a few lines of code in this macro where I used the Userform
and its input to fill in the choices, but it would be easier to remember the
various choices if they existed in a list where I could click on one to
choose it. That's how I ended up creating the little drop-down list tied to
a single cell. Now, if I could somehow automate the running of the macro
based on highlighting a new choice in the drop-down list. Thanks, Brad

"STEVE BELL" wrote in message
news:TS2le.1103$m%3.212@trnddc02...
Brad,

Not sure what kind of drop-down you are using. But set up your macro
something like this:

Dim mcro as String
mcro = MyForm.Combobox1
' or '
mcro = MyForm.Listbox1
' or '
mcro = Sheets("MySheet").Range("A1").Text

Run mcro

--
rand451
"Brad" wrote in message
...
Could someone explain the easiest way for me to automatically activate an
existing macro based on clicking on, then selecting a choice from a
dropdown list? Thanks in advance for any advice! Cheers! Brad

dropdown list to activate Macro
 

Brad,

I thinkwhat you need here is to use the Worksheet_Change event in the VBE.
From XL press ALT+F11 that will take you to the VBE.
On the left you should see your sheet in the project explorer. double click
on the sheet where you have the drop-down list.
From the first drop down in the code window select Worksheet - this would
probably automatically add the sub Workshet_SelectionChange
In the second drop down list select Change - this will add yet another code
snippet and this is where you should fit your macro if you want it to
trigger on the change of cell (have to trap it though)

Cheer
Vimal
Mauritius


Brad wrote in message
...
Jim/Steve,

I created the drop-down box by the following steps: Data, Validation,
Allow,
List, where I then put in 9 entries delimited by commas.

I had been using a few lines of code in this macro where I used the
Userform
and its input to fill in the choices, but it would be easier to remember
the
various choices if they existed in a list where I could click on one to
choose it. That's how I ended up creating the little drop-down list tied
to
a single cell. Now, if I could somehow automate the running of the macro
based on highlighting a new choice in the drop-down list. Thanks, Brad

dropdown list to activate Macro
 

My thanks to both of you ... it now works with the following code based on
your valuable directions and input! Brad

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$122" Then
PickSymbolOpen1
End If
End Sub


"Brad" wrote in message
...
Could someone explain the easiest way for me to automatically activate an
existing macro based on clicking on, then selecting a choice from a
dropdown list? Thanks in advance for any advice! Cheers! Brad