toggle button caption toggling

[JordanGoodchild]

Hi, im trying to get the caption on a toggle button to change dependant on whether a certain set of rows are hidden or showing.

currently the code i have to hide the rows is this

Sub ToggleButton1_Click()
If Rows("3:10").EntireRow.Hidden = False Then
    Rows("3:10").EntireRow.Hidden = True    
Else
    Rows("3:10").EntireRow.Hidden = False
End If

End Sub

everytime i try something it gives me the object required error.

 

[repairman615]

Hello,

Try like this:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ToggleButton1_Click()<br><br><SPAN style="color:#00007F">If</SPAN> Rows("3:10").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    Rows("3:10").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>    ToggleButton1.Caption = "Hello"<br><SPAN style="color:#00007F">Else</SPAN><br>    Rows("3:10").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>    ToggleButton1.Caption = "GoodBye"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[JordanGoodchild]

i still get the object required message.
the toggle button is on the spreadsheet itself so i have to make the macro public so i can assign the button to it.

 

[Sektor]

Repairman's code is OK. What does it mean "I have to make the macro public so i can assign the button to it"?

 

[JordanGoodchild]

when i place the button on the spreadsheet in the function line i get =EMBED("Forms.ToggleButton.1","")

i have to delete this and then right click the button and asign a macro, the macro has to be public

 

[Norie]

You can't assign a macro to a toggle button.

They are ActiveX controls and they have their own events, like the click event in repairman's post.

That code has to go in the worksheet module of the worksheet the toggle button is on.

Anywhere else you would need to add a reference to that worksheet when trying to refer to the toggle button.

Even if you did that the event belongs in the worksheet module, if it's not there it's not going to get triggered when you click the toggle button.

Why do you need the code to be public?

 

[JordanGoodchild]

Thank You Norie and Repairman,
The code did work i just needed to figure out how to use the toggle button without deleting the string of text in the function line.

 

[repairman615]

Glad you got it working.

As Norie has stated and to add a little to that...

After inserting an ActiveX control, right click and select veiw code. This is where the code should go. This method can be used for all ActiveX controls.

 

[Norie]

Just out of interest, why did you want to delete what was in the formula bar?

I tried it just for fun and because I've never tried it before.

I got some invalid reference message when I tried to exit the formula bar after clearing it of the =EMBED(...).