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

Mrock1

New Member
Joined
Oct 7, 2014
Messages
45
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,159
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

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

Mrock1

New Member
Joined
Oct 7, 2014
Messages
45
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.

VBA Code:
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

New Member
Joined
Oct 7, 2014
Messages
45
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.

VBA Code:
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
 

Forum statistics

Threads
1,148,242
Messages
5,745,588
Members
423,963
Latest member
lwilson3

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