1
votes

I have been searching for a solution for the following problem for days now, but I just can't figure it out.

I have a Excel Workbook with multible sheets. Some sheets contain code and there are also multible modules with code. Keep in mind, that the sheet names, postition and quantity can be changed by the end user.

In the sheet with the code.name "Tabelle 1" I have the following code:

Sub Reset_ToggleButton1()
If ToggleButton1.Value = True Then
ToggleButton1.Value = False
End If

End Sub

As far as I found out, I can only activate ToggleButtons with code in their respective sheet. If this is not true, this might be a possible solution to my problem.

Further I would like to call the sub Reset_ToggleButton1() from a modul. But, as there can be multible copies of the sheet with the toggle button and the respective code, I would like to referece the sub in the active sheet.

The following code worked, but only for the sheet named.

Sub test()
Application.Run "Tabelle1.Reset_ToggleButton1"
End Sub

I think what I need is to replace the "Tabelle1" with the code name of the active worksheet. I know, I can get the Code.Name with of the active worksheet with the following code:

Dim SheetCode As String
SheetCode = ActiveSheet.CodeName

But I don't know how to insert it into the Sub test() from above.

Your help is very much appreciated!

Best wishes Anne

2

2 Answers

0
votes

you can use

ActiveSheet.Reset_ToggleButton1

or

Worksheets("Tabelle1").Reset_ToggleButton1

to run it.

0
votes

Worthy of mention, you can specify worksheet:

Sub Test(WS As Worksheet)
    WS.Reset_ToggleButton1
End Sub