Looping through buttons on a worksheet.

[Libby]

hello folks

I have a worksheet with a number of toggle buttons on it, which is menu for
opening other workbooks. When the user double clicks a toggle button it
opens a workbook.
However, the idea is that if the user wants to open more than one workbook,
then they can select the desired sheets via the togglebuttons and then click
a command button to open all where the togglebuttons are true.

How can I loop through the togglebuttons and if they are true call the
double click event? I need the code to be faily adaptable as there will be
new buttons added in the future.

I'm using XL2000

Many thanks in advance.

 

[Tom Ogilvy]

Sub ddd()
Dim o As OLEObject
Dim t As msforms.ToggleButton
For Each o In ActiveSheet.OLEObjects
If TypeOf bject Is msforms.ToggleButton Then
Set t = bject
MsgBox t.Name & " value is " & t.Value & _
" at cell " & o.TopLeftCell.Address
End If
Next

End Sub


depressed is true, not depressed is false.

 

[Libby]

Thanks Tom. Thats great for loop though the buttons and finding their value,
but do you know how I would:

1) call the double click event for the button if it was true

2) change the value of the button from true to false or vice versa

Many thanks )

 

[Leith Ross]

Thanks Tom. Thats great for loop though the buttons and finding their value,
but do you know how I would:

1) call the double click event for the button if it was true

2) change the value of the button from true to false or vice versa

Many thanks )

Hello Libby,

To trigger the double click event is not a trivial task. It requires
hooking into the mouse messaging pump. Since you already have a
command button to open multiple workbooks, why not maintain that
continuity for the user. That is always use the command buttom to open
one or more workbooks

You can further simply your code by initializing the toggle buttons
Tag property when the UserForm is activated with the name of the
Workbook it will open. Here is an example adding this to Tom's code...

Sub UserForm1_Activate()
ToggleButton1.Tag = "Workbook1"
ToggleButton2.Tag = "Workbook2"
ToggelButton3.Tag = "Workbook3"
End Sub

Sub ddd()
Dim o As OLEObject
Dim t As msforms.ToggleButton
For Each o In ActiveSheet.OLEObjects
If TypeOf bject Is msforms.ToggleButton Then
Set t = bject
Workbooks(t.Tag).Open
End If
Next o
End Sub

Sincerely,
Leith Ross