Changing Caption on Button in Spreadsheet out of focus

[farhad_mofidi]

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?

 

[Von Pookie]

Try combining the statements (I have not tested this):

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

 

[farhad_mofidi]

I've tried that. I get a Run-time error '438':

 

[Cbrine]

I found an error with the text setting, you are missing textframe.

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


HTH
Cal

 

[farhad_mofidi]

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

 

[farhad_mofidi]

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]

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]

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]

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]

You didn't have textframe in your code in the first email, but that works, so brilliant.

Thanks for all your help Cal.