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?

 

[Cbrine]

I did, but I didn't have the reference setup to the object in the first post.

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

Which may have replaced the first problem with a second. The second post I removed the textframe bit, cause of your post after my first one.

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

Which made me think I was maybe missing something.

Anyway, glad you got it working, which is the main thing.

Cal