VBA to move and resize command buttons

[volkl77]

Hi all,

I have a set of inventory workbooks (20 different files with the exact same layout and sheet names) with command buttons on the individual sheets to run macros. I have tried every possible solution that I can find online to prevent the buttons from being moved and resized (don't move or resize, lock aspect ratio, print object, & protect worksheet and button) but somehow they are still getting moved and resized. Is there a way to write a macro that will select each individual button, resize it and move them to a specific location on each sheet? I have tried using the record macro option but when I try and run on different sheets it fails. Besides driving me crazy, the buttons are sometimes being resized so small that they can't be clicked and I have to go in and fix the sheet.

Any help would be greatly appreciated!

Thanks!

 

[busdriver12]

Hi all,

I have a set of inventory workbooks (20 different files with the exact same layout and sheet names) with command buttons on the individual sheets to run macros. I have tried every possible solution that I can find online to prevent the buttons from being moved and resized (don't move or resize, lock aspect ratio, print object, & protect worksheet and button) but somehow they are still getting moved and resized. Is there a way to write a macro that will select each individual button, resize it and move them to a specific location on each sheet? I have tried using the record macro option but when I try and run on different sheets it fails. Besides driving me crazy, the buttons are sometimes being resized so small that they can't be clicked and I have to go in and fix the sheet.

Quick question - do you have design mode enabled? I have a similar spreadsheet where the command buttons are used primarily for navigation throughout the sheet, and the only way I can move/resize etc is to have design mode enabled.

Just a thought...

 

[macrubco]

Hi All,
I had the same problem. Fixed it with a simple little bit of code.
Sub FixSize ()
With CommandButtonX
.height = 24
.width = 80
End With

Have this run on start up on the CommandButtonX code