Attaching VBA code to an inserted Excel button to hide rows and change caption

[TheScott]

I am having a problem that I can't seem to figure out. The code below works well when attached to a button on a userform but
I cannot for the life of me make it work with a button inserted into an excel sheet. Depending on what I do, I get Runtime errors that point to an
object error. I am guessing that a button inserted into excel does not have a property option called 'caption' thus causing the problem... but I am
just guessing.

Any guidance would be helpful as I am pretty new to all things vba and am easily stumped.

................................

Private Sub Hidebtn_Click()

'Operates a button that displays the status of rows 10 to 31
'If rows are visible then the button displays a caption of "Hide Register"
'Once pressed rows 10:31 are hidden and the caption changes to "Show Register"
'Pressed again the rows are unhidden and the caption on the button changes back to "Hide Register"

If Hidebtn.Caption = "Hide Register" Then
Range("10:31").EntireRow.Hidden = True
Hidebtn.Caption = "Show Register"
Else
Range("10:31").EntireRow.Hidden = False
Hidebtn.Caption = "Hide Register"
End If

End Sub
...............................................

Thanks In Advance
Scott

[RudiS]

Hi Scott,

Excel has two types of worksheet buttons.
1. Form Controls and
2. Active X controls

You must use the Active X controls to access the Caption property.
Form controls do not have properties accessible to VBA

Once you set up the Active X button, ensure that you change its Name property and event property to match Hidebtn_Click()
And also remember to set its Caption property to Hide Register initially

See image: 1.jpg

[romperstomper]

Form controls do not have properties accessible to VBA

That's not true - all of a Forms control's properties are available to VBA.

[RudiS]

How would you refer to the Form Control button?
If I have two buttons on sheet 1; one an Active X called cmdButton and another as a Form Control button
I can go to the sheet 1 object in the Projects window, and on its module I can type Me.cmdButton, but how do I refer to the Form button?

TX

[romperstomper]

Me.Buttons("Button name")

They don't expose themselves directly as members of the sheet class, unlike ActiveX, but that also is part of what makes it easier to assign code to them.

[RudiS]

Me.Buttons("Button name")

They don't expose themselves directly as members of the sheet class, unlike ActiveX, but that also is part of what makes it easier to assign code to them.

TX Rory... I rarely reference Form Controls via VBA.
Cheers!

[Kaper]

see attached file
both tyoes in sheet 1, form button calls procedure in standard module:

Sub test()
ActiveSheet.Shapes("CommandButton1").ScaleWidth 0.5 + Rnd, msoFalse, _
        msoScaleFromTopLeft
End Sub

and activex button procedure is in sheet1 code:

Private Sub CommandButton1_Click()
ActiveSheet.Buttons(1).Text = Format(Now, "dddd hh:mm:ss")
End Sub

first changes text on second (this what you asked above) the second changes width of the first

[TheScott]

I thank the three of you for your help. While your information moved me in the right direction and helped me understand the process better, the learning curve, atleast for me, is high and it created more questions than answers.

Rory and Kapers gave me confidence that what I want to do can be done. Kapers examples were informative but up to now my use of the form button and shapes have met with limited success. I think that the biggest problem that I am having being a beginner at any programing,... and an aging fart as well, is finding clear documentation that shows all of the command structure purmutations.

I managed to get the original code to attach to an activex button will little trouble. It works as expected... HT Rudi

My ultimate goal is to use shapes as buttons as I have already successfully used them for a couple other functions on the same spreadsheet and would like a consistant look.
Shapes of course look better and have more visual flexibility.

When attempting to use the form button, I had to rewrite the code as I could not get the event (hiding or unhiding rows) to trigger off of the text contained in the button. I managed to change the text by using the status of the rows as the trigger. It operates but the reason I wanted to use the text as the IF trigger is to cause other actions that may not have any relation to the state of the rows

>>>>>>>>>>>>>>>>
If Range("10:20").EntireRow.Hidden = True Then
Range("10:20").EntireRow.Hidden = False
ActiveSheet.Buttons(1).Text = "Hide US Register"

Else

Range("10:20").EntireRow.Hidden = True
ActiveSheet.Buttons(1).Text = "Show US Register"

End If

>>>>>>>>>>>>>>>>>>>>>>>>>>>

Using shapes I could only manage to write something that would cause the rows to hide or reveal but could not effect the text at all..

If you could nudge me forward with more words of wisdom and perhaps some example I would appreciate it.

Regards

Scott

[romperstomper]

What problem did you have with using the text as criterion? It would just be:

If ActiveSheet.Buttons(1).Text = "Hide US Register" Then
...
End If

[TheScott]

Thank you so much Rory.

I must have gotten the syntax wrong somewhere but went back and tried again and it worked perfectly

Also solved the problem using and changing text in a shape... again it was syntax

ActiveSheet.Shapes("Rect1").TextFrame.Characters.Text =

Might get the hang of this yet

Thanks again to all three of you!

[romperstomper]

Glad to help.