How do I change a button size and location on an excel sheet?
-
The only thing that I found is adding a button. I also found ActiveSheet.Shapes("Button 1") but did not get it to work.Steven– Steven2011-10-31 20:30:45 +00:00Commented Oct 31, 2011 at 20:30
-
4Why was this post down-voted? If you are going to down-vote someone, please explain your reasoning. That's why we're all here: to learn from each other.PowerUser– PowerUser2011-10-31 20:52:57 +00:00Commented Oct 31, 2011 at 20:52
Add a comment
|
2 Answers
You are almost there with ActiveSheet.Shapes("Button 1")
If you dim a variable as Shape and assign the button to it, you can use intellisence to explore the available properties. From there you will find .Height, .Width, .Left, .Top
Dim btn As Shape
Set btn = ActiveSheet.Shapes("Button 1")
btn.Height = 50
btn.Width = 100
2 Comments
MT1
The units of the height and width measurement are in points. learn.microsoft.com/en-us/office/vba/api/…
MT1
More details about points, twips and millimetres davetallett26.github.io/makeform.html
Steven, to learn basic VBA in Excel, I recommend experimenting with the Record Macro option. It's location varies depending on your version of Excel (In 2007, you need to activate the Developer tab in your ribbon).
With this button turned on, use the standard menus to do whatever you wanted to do in the first place (in this case, make a custom sized button). Then stop the recording and look at the auto-generated code you just made.
2 Comments
TCN
Excellent tip, but all I see on record macro is .ScaleWidth and not .Width
PowerUser
You are correct. I had to try it for myself. For some reason the macro recorder only records commands for scaling the button's size. It doesn't record absolute changes in the size. Please use @chris neilsen's answer.