Macro for Control ListBox

[Linking to specific cells in pivot table]

Hi,

I have a List Box that I created from the Control Toolbox -- I am trying to
have a macro run based off of which value has been selected in the listbox
(there are only two values that can be selected). I tried the code below,
but am getting the following error: "Run-time error 438: Object doesn't
support this property or method".

If ActiveSheet.Object("ListBox1").Value = "Equivalent kwh/yr" Then
Call Module4.togglequiv
Else
If ActiveSheet.Object("ListBox1").Value = "Actual kwh/yr" Then
Call Module4.toggleact
End If
End If

Can someone let me know what the proper coding should be? Thanks for your
help!!

Robert

 

[Rowan]

If the listbox is from the Control Toolbox then this event should work
if placed in the code module for the sheet which holds the listbox:

Private Sub ListBox1_Click()
If Me.ListBox1.Value = "Equivalent kwh/yr" Then
Call Module4.togglequiv
ElseIf Me.ListBox1.Value = "Actual kwh/yr" Then
Call Module4.toggleact
End If
End Sub

Hope this helps
Rowan

 

[Linking to specific cells in pivot table]

Thanks Rowan - the only problem I have now is that when I click on one of the
values in the listbox, the macro is executing, which is great, however, the
value highlighted in the listbox is not refreshing every time (it does so
intermittingly). It looks like this is because I set the
"Application.ScreenUpdating" to "False" at the beginning of the macro and
then turn it back to "True" at the end of the macro. I'm wondering if
there's a command to put in at the end of the macro to refresh the screen or
something so that the list box will be up to date.

Thanks so much for your help!

Robert

 

[Rowan]

Hi Robert

I am pretty sure it is the screenupdating that is causing your listbox
not to refresh properly but unfortunately I don't know how to fix that -
aside from leaving the screenupdating set to true.

Maybe others in the news group could help.

Good luck
Rowan