Changing Caption on Button in Spreadsheet out of focus

farhad_mofidi

New Member
Joined
Oct 12, 2006
Messages
7
Hi,

I have two spreadsheets that have exactly the same sheet names and I have the following problem when thay are open at the same time. The sheet that is out of focus pops an alert message to the user and asks him/her if he wants to run a macro. On yes the macro on that sheet starts and one of the things the macro does is select one the Buttons on the sheet (a Form Button, not a comand button) and tries to change the text on that button. This is the code;

ThisWorkbook.Worksheets(sheetName).Shapes(buttonName).Select
Selection.Characters.Text = buttonText & vbLf & Now()

The button on the sheet out of focus is selected fine but the problem is the second line selects a cell in the sheet that is in focus.
How do I change the text on the button that is on the out of focus spreadsheet?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Try combining the statements (I have not tested this):

ThisWorkbook.Worksheets(sheetName).Shapes(buttonName).Characters.Text = buttonText & vbLf & Now()
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I found an error with the text setting, you are missing textframe.

selection.TextFrame.Characters.Text = buttontext & vbLf & Now()


HTH
Cal
 

farhad_mofidi

New Member
Joined
Oct 12, 2006
Messages
7

ADVERTISEMENT

The code works fine when the spreadsheet is in focus, the problem is when its out of focus
 

farhad_mofidi

New Member
Joined
Oct 12, 2006
Messages
7
Its almost as if when excel selects the Button on the outof focus spreadsheet it forgets about it and comes back to the in focus spreadsheet in between the two lines of code.

Its really strange, has anyone seen this before?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

The set a reference to the object, and focus shouldn't matter.

Dim s as shape.
Set s = Worksheets(sheetName).Shapes(buttonName)
s.characters.text=buttontext & vbLf & now()

HTH
Cal
 

farhad_mofidi

New Member
Joined
Oct 12, 2006
Messages
7
Sorry,

I get a runtime error again. The Shape object does't seem to support the character property, which I think is essentially the problem, as in how do I explicitly set the text property of a Shape object.

Any other ideas?

Farhad
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Thats what I said in my first email. use this to change a forms button caption.

dim s as shape
set s = worksheet(sheetname).shapes(buttonName)
s.TextFrame.Characters.Text = buttontext & vbLf & Now()


HTH
Cal
 

farhad_mofidi

New Member
Joined
Oct 12, 2006
Messages
7
You didn't have textframe in your code in the first email, but that works, so brilliant.

Thanks for all your help Cal.
 

Forum statistics

Threads
1,136,621
Messages
5,676,852
Members
419,656
Latest member
lironprofit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top