Worksheet form control shape properties not all available when workbook is minimised

[Mrock1]

I have been using the function Workbook("file name.xlsm").Worksheets("Sheet 1").Shapes("command button 1").texrframe.characters.text = "my text". This works perfectly and changes the text on the command button. .Caption and .text don't seem to work at all

However, if the workbook is minimised, this command errors. I've watched the value in th VBE of the shape and that property resolves with an error while the workbook is minimised. if I restore the workbook to the desktop while the macro is suspended in debug, the command works and the macro proceeds as normal.

I'm trying to change the text on a command button within the workbook_resize event macro, which runs after the workbook minimises - I check to see if the windows state is minimised to change hot button text. My current workaround is to restore the window state to xlNormal, change the text, then re-minimise

Does anyone know why these,properties fail when the workbook is minimised and if there is a better way too do this?

Thanks

 

[rlv01]

Excel does not seem to like what you are trying to do. But if you switch from a form button to an active X button control , it should work.

Workbooks("file name.xlsm").Worksheets("Sheet1").OLEObjects("CommandButton1").Object.Caption = "my text"

 

[Mrock1]

Excel does not seem to like what you are trying to do. But if you switch from a form button to an active X button control , it should work.

Workbooks("file name.xlsm").Worksheets("Sheet1").OLEObjects("CommandButton1").Object.Caption = "my text"

Thanks for that suggestion, but I'm trying to avoid AxtiveX
I am still very curious as to why Excel doesn't like what I'm trying to do. Some other properties seem to be available but quite a number become unresolvable when the workbook is minimised. My workaround of restoring the workbook to be able to set the text value causes a momentary blink so it good enough and qui alert, I think, to your suggestion but I appreciate the feedback. Maybe it's just some wierd technical limitation or Excel VBA and how it retrieves object properties

 

[Mrock1]

Excel does not seem to like what you are trying to do. But if you switch from a form button to an active X button control , it should work.

Workbooks("file name.xlsm").Worksheets("Sheet1").OLEObjects("CommandButton1").Object.Caption = "my text"

Ok I think I've found a better workaround it seems that while forms control objects are touted as being similar to shape, evidence suggests they're not - certainly at least when the workbook is minimised the workaround is to create rectangle shape and set it up to emulate a button by using 3D effect and changing then with the on-click assigned macro using a rectangle shape allows the properties such as .textframe.characters.text to be available when the workbook is minimised. Unfortunately it only looks like a button and doesn't have the nice mouse down/up features that would otherwise allow a held left mouse to be dragged off the button to release it without action. Fortunately all I want this button for is to toggle a value so it's not that important that it doesn't behave like a real command button

I'd still be interested to know if anyone has yet another approach

Thx