Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through buttons on a worksheet.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through buttons on a worksheet.
Sub ddd()
Dim o As OLEObject Dim t As msforms.ToggleButton For Each o In ActiveSheet.OLEObjects If TypeOf o.Object Is msforms.ToggleButton Then Set t = o.Object MsgBox t.Name & " value is " & t.Value & _ " at cell " & o.TopLeftCell.Address End If Next End Sub depressed is true, not depressed is false. -- regards, Tom Ogilvy "Libby" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through buttons on a worksheet.
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 :o) "Tom Ogilvy" wrote: Sub ddd() Dim o As OLEObject Dim t As msforms.ToggleButton For Each o In ActiveSheet.OLEObjects If TypeOf o.Object Is msforms.ToggleButton Then Set t = o.Object MsgBox t.Name & " value is " & t.Value & _ " at cell " & o.TopLeftCell.Address End If Next End Sub depressed is true, not depressed is false. -- regards, Tom Ogilvy "Libby" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through buttons on a worksheet.
On Feb 22, 11:51 pm, Libby wrote:
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 :o) "Tom Ogilvy" wrote: Sub ddd() Dim o As OLEObject Dim t As msforms.ToggleButton For Each o In ActiveSheet.OLEObjects If TypeOf o.Object Is msforms.ToggleButton Then Set t = o.Object MsgBox t.Name & " value is " & t.Value & _ " at cell " & o.TopLeftCell.Address End If Next End Sub depressed is true, not depressed is false. -- regards, Tom Ogilvy "Libby" wrote: 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. 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 o.Object Is msforms.ToggleButton Then Set t = o.Object Workbooks(t.Tag).Open End If Next o End Sub Sincerely, Leith Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping over a worksheet | Excel Programming | |||
'Save current worksheet'; 'Open next worksheet' - two command buttons | Excel Discussion (Misc queries) | |||
Adding buttons while looping through files Question | Excel Programming | |||
looping thru the worksheet | Excel Programming | |||
Looping Through Worksheet | Excel Programming |