Dynamic Event Handling in Excel VB

[sadman1]

Hi

I'm a VBforums newbie and VB amateur. I've search the www and this forum extensively but still can't find the answer to my question (although I'm sure it's there). (Great forum BTW).

The problem:

In Excel VB I am dynamically creating a form with sliders (from the MSComctlLib.Slider library), the number of which is varyable (5 to 100). I can name the sliders and create textboxes with corresponding numbers to display the value of the slider (i.e. slider01 : textbox01, slider02 : textbox02...slider98 : textbox98 etc).

But I cannot get a compact code that will basically say "when you click a slider, find the number of the slider and do stuff".

I've tried:
Private WithEvents newSlider As Slider
but can't get it to work. Similarly when I try Private WithEvents newlbl As Label (for instance), I get the error "Object does not source automation events".

I've fiddled with class modules but failed.

Can anyone out there give me any pointers?

Thanks - sorry if this is a repeated question

TW

[zaza]

Welcome to the Forums.

Can you not just pass the name of the slider to a sub to tell you how to act:

Sub Slidecode(ByVal slider as string)
  Select Case slider
   Case "slider1"
    'Do something
   Case "slider2"
    'Do something else
  End Select
 End Sub

and

Call Slidecode("Slider1")

or something along those lines?

Or is the issue around attaching a generic slider handling code to an indeterminate slider? How are you generating the sliders?


zaza

[sadman1]

Thanks for your prompt reply. I probably didn't explain correctly - I'm not having a problem managing the slider, I'd just rather not have to write hundreds of :
Sub slider1_Click()
...
Sub slider2_Click()
...
Sub slider3_Click()
...
with the ... code being virtually the same.

Is there a way to have a:
Sub genericslider_Click(carry the object and its value)
?

[si_the_geek]

I don't think you can use control arrays in VBA (which is what Excel etc use instead of VB), which takes the best option away.

What you can do tho is write your generic sub with a header like this:

Sub genericslider_Click(theSlider as Slider)
  'you can now use [b]theSlider[/b] as whichever slider was passed.

..and you can call it like this:

Sub slider1_Click()
  genericslider_Click slider1
End Sub

While it's not a perfect solution, it should be a significant improvement.

[sadman1]

Yep - I'd already thought of that. It does mean writing a hundred Sub sliderNN_Click() routines and I was hoping to be a little more elegant than that!!

[tooff]

I'm having the same problem. Did you find a solution?

[si_the_geek]

I don't think there is any way (in VBA) of improving on what I posted above.