Worksheet_SelectionChange event not triggered

[RedStarryGirl]

Hello,

I am not an experienced programmer, so I apologize if this is a simple question. I am using Excel 2007, and I have a macro (that is working) that I would like to run whenever there has been a new selection in a dropdown list on my worksheet. I have done this many times before in other workbooks, and I have always used:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

To my knowledge this should trigger the macro when the dropdown selection changes. However, this time it is not working. The macro runs fine manually, but it does not run when the dropdown selection changes. Any help would be greatly appreciated!

 

[Diablo II]

have you tested the others on excel 07?

 

[RedStarryGirl]

Yes, this works on other files in Excel 2007.

 

[Colin Legg]

Hi,

I think you're looking for the worksheet object's Change event handler, not SelectionChange event hander:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Does that help?

 

[RedStarryGirl]

Unfortunately, no. Still nothing happens when I change the dropdown selection.

 

[Colin Legg]

It works for me....

This is a data validation drop down list?

And is the Change event handler in the correct sheet class module?

And have you made sure that Application.EnableEvents is set to true?

 

[bk]

What are you calling a "drop-down"? A data validation drop-down? Would have to be unless it's a dropdown from the ActiveX Control or Forms toolbars.

If it's from one of those toolbars, I don't "think" the Worksheet events catch it like that. They're expecting Target to be a cell. If so, you can right-click on the control, go to their code section and paste in the exact same code. You just would be changing the event.

If it's Data Validation, then it's changing a cell and should work. Conceivably (I've not tested this, nor the above, so you know), it wouldn't catch the change until you hit Enter or changed to another cell.

Hope some of that helps.

 

[c_m]

Hello,

I am not an experienced programmer, so I apologize if this is a simple question. I am using Excel 2007, and I have a macro (that is working) that I would like to run whenever there has been a new selection in a dropdown list on my worksheet. I have done this many times before in other workbooks, and I have always used:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

To my knowledge this should trigger the macro when the dropdown selection changes. However, this time it is not working. The macro runs fine manually, but it does not run when the dropdown selection changes. Any help would be greatly appreciated!

there is no instruction between the sub and endsub statement.
you need something like:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

 

[carsten]

try the Private Sub ComboBox1_Change() event, if you use a combo box from the Control Toolbox.

 

[RedStarryGirl]

Okay, this is actually a Combo Box, so it is a control. However, I put a cell link on the worksheet. So when the value in the combo box changes, the value of the cell link changes. In effect, this is a change in a cell.

I use the exact same control and private sub in other files, and it works. This makes me wonder if there is some security setting that is different. I have a macro-enabled workbook.

I will still try pasting the code from the control and let you know. Thanks!