How to set up caption on a button at startup?

[HammerJoe]

Hi,

I have a small spreadsheet with a command button called makebusy.
I also have a macro called Auto_open to automatically run whenever the
worksheet is open.
What I would like to have is that at startup (somehwere in the
auto_open sub) to set up the caption of my button to "start".

The reason is that when the button is clicked I change the caption of
it, and I would like it to start by daefault as "start" whenever the
sheet is opened.

Any help would be appreciated.
Thanks

 

[HammerJoe]

I just want to add that the button is not created with Auto_open sub.
It is already in the spreadsheet. I just want to make sure that the
caption is changed to "start"
Thanks

 

[ward376]

maybe: yoursheet.makebusy.caption = "Start"

How do you change the caption in the sub that's triggered by the
button? You should be able to use the same method to change it in the
auto open sub, just refer to it explicitly. (instead of "me.caption"
"yoursheet.makebusy.caption")

Cliff Edwards

 

[OssieMac]

Hi,

There are two types of buttons. Need to know what type. One is created from
the Forms toolbar and the other is and ActiveX control created from the
Control Toolbox toolbar. (Both look alike)

Right click on the button.

If you get a dialog box with a number of options including Cut, Copy Paste
etc then it is a Forms button.

If nothing happens or only a dotted line around just inside the perimeter,
it is an ActiveX control.

Let me know what type of button you have and I'll provide sample code and
instructions on how to identify the button name for the code.

 

[HammerJoe]

Hi,

Thanks for all the help.

It is a Forms button.
I used the control toolbox to create the button adn I do get all those
option when right clicking on it in design mode.
Under properties I gave it the name 'makebusy'.

Thanks again for all the help.

 

[OssieMac]

Hi again,

If created under the Control Toolbox it is actually an ActiveX control. You
do see the dialog box if the Design mode is turned on when you right click
it. With a Forms button, you don't use Design Mode and you see the dialog box
simply by right clicking on the button.

Anyway example of code the code is:-

Sheets("Sheet1").CommandButton1.Caption = "Start"

You said that you gave it the name 'makebusy'. Did you mean the Name or the
Caption? They are different. In the above code CommandButton1 is the name.
You can see both name and caption in Properties. If 'makebusy' is the name
then your code will be:-

Sheets("Sheet1").makebusy.Caption = "Start"

Of course you will need to edit the sheet name to suit your sheet name.

Or if it is on a userform then:-

UserForm1.makebusy.Caption = "Start"

Again you will need to edit the userform name to suit your userform.

Hope this helps. Feel free to get back to me if you have any further
problems with it.

 

[HammerJoe]

That worked thanks

It was the Sheets("Sheet1") that was missing.

I did try this and it didnt work:
Set wks = ThisWorkbook.Worksheets("sheet1")
wks.makebusy.caption="start"

Why is that?
Once again, thanks for the help and on to the next challenge.
Cheers.

 

[OssieMac]

Hi again,

I am not really full bottle on using OLEObjects and while the code I gave
you works, the syntax is not complete. If you include everything then it
works with objects assigned to variables as per the following example:-

Sub Test_Caption_2()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

ws.OLEObjects("CommandButton1").Object.Caption = "run me"

End Sub

Check out the following Microsoft web site for more information on using
ActiveX controls (Worth adding to your favourites):-

http://msdn2.microsoft.com/en-us/library/aa297495(office.10).aspx