Button size vs cell size, they don't calculate

288enzo

Well-known Member
Joined
Feb 8, 2009
Messages
717
Office Version
  1. 2016
Platform
  1. Windows
Cell A1 is 28 wide x 19.5 high.
My code works perfectly where the button is the same height as the cell, but the width isn't matching up. I'm trying to make the button half the size of the cell.
VBA Code:
ActiveSheet.Buttons.Add(Range("B1").Left, Range("B1").Top, -14, 19.5).Select
I don't understand why the height works perfectly, but following the same idea the width isn't even close to being half the cell. How is the width calculated?
Thank you
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
The width is not the same unit of measurement,
VBA Code:
ActiveSheet.Buttons.Add(Range("B1").Left, Range("B1").Top, -Range("A1").Width / 2, 19.5).Select
use
 
Upvote 0
Solution
The width is not the same unit of measurement,
VBA Code:
ActiveSheet.Buttons.Add(Range("B1").Left, Range("B1").Top, -Range("A1").Width / 2, 19.5).Select
use
I see what you did there, genius. Thank you
 
Upvote 0
The width is not the same unit of measurement,
VBA Code:
ActiveSheet.Buttons.Add(Range("B1").Left, Range("B1").Top, -Range("A1").Width / 2, 19.5).Select
use
With that in mind, your code helped add a button in cell A1. If you broke A1 into 4 equal parts, I'm trying to place two buttons of equal size in the right half of A1. Your idea worked perfectly for button A which is to the far right of A1, but I can't figure out how to place button B where the starting location is in the middle of A1. If the width of a cell actually was the same as the location for a button this would be easy. Why in the world would they be calculated differently than the height? That's an entirely different question.
 
Upvote 0
You could use
VBA Code:
With Range("A1")
   ActiveSheet.Buttons.Add(.Left + .Width / 2, .Top, .Width / 4, .Height).Select
   ActiveSheet.Buttons.Add(.Left + .Width, .Top, -.Width / 4, .Height).Select
End With
 
Upvote 0
With that in mind, your code helped add a button in cell A1. If you broke A1 into 4 equal parts, I'm trying to place two buttons of equal size in the right half of A1. Your idea worked perfectly for button A which is to the far right of A1, but I can't figure out how to place button B where the starting location is in the middle of A1. If the width of a cell actually was the same as the location for a button this would be easy. Why in the world would they be calculated differently than the height? That's an entirely different question.
I figured it out after playing around with the numbers.

VBA Code:
ActiveSheet.Buttons.Add(Range("B1").Left / 2, Range("B1").Top, Range("B1").Width / 2, 19.5).Select
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top