Solved: Selection Triggerred Macro

[antonc]

Hi Guys,

I'd like to know how to get a macro to run once a user has selected his choice from a drop-list list in cell E2.

I know how to code the macro I need, but I'm not sure how to get the macro triggerred by the change in the value of the drop-down list.

I need some help figuring out the first part of the code.

I'm using Excel 2007 if that is of help.

Thanks for your assistance and help in advance.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
' do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mdmackillop]

This a worksheet event6 and is placed in the sheet code window

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "E2" Then
Exit Sub
Else
MsgBox Range("E2").Value
End If
End Sub

[Slyboots]

The preferred way to do this isn't with a Worksheet_Change event. Instead, let's assume you name your dropdown object "drpObj1". In the code behind the sheet, add a routine:

Sub drpObj1_Change()


End Sub

This will fire whenever the value of the dropdown changes.

S


Hi Guys,

I'd like to know how to get a macro to run once a user has selected his choice from a drop-list list in cell E2.

I know how to code the macro I need, but I'm not sure how to get the macro triggerred by the change in the value of the drop-down list.

I need some help figuring out the first part of the code.

I'm using Excel 2007 if that is of help.

Thanks for your assistance and help in advance.

[Bob Phillips]

The preferred way to do this isn't with a Worksheet_Change event.

That is a matter of opinion, not fact.

[Slyboots]

That is a matter of opinion, not fact.

This "opinion" is based 25 years of experience, and the FACT that it's more efficient to attach the event to the control.

When you use the change event of the worksheet, you need to write further code to make sure that the changed cell is the one linked to the dropdown control. In addition, I may not use a linked cell at all, or use one that's located on a different worksheet altogether.

If you want to do something when the dropdown changes, you don't need to worry about any of that, if you've set the event property of the control itself.

S

[Bob Phillips]

This "opinion" is based 25 years of experience, and the FACT that it's more efficient to attach the event to the control.

Opinion is opinion, 25 years or no. And facts should be supported by evidence, or they are not facts.


When you use the change event of the worksheet, you need to write further code to make sure that the changed cell is the one linked to the dropdown control. In addition, I may not use a linked cell at all, or use one that's located on a different worksheet altogether.

No you can just check that the cell value has changed if you use Data Validation as the list facility, or didn't you come across that in your 25 years.


If you want to do something when the dropdown changes, you don't need to worry about any of that, if you've set the event property of the control itself.

IMO (note, opinion, not fact) it is better to not to put controls on a worksheet unless absolutely necessary, and it rarely is.