PDA

View Full Version : Grid of Buttons - Sizing and Positioning



ronjon65
03-12-2012, 04:55 AM
I would like to create a grid of buttons. I currently just do a copy/paste and manually position, but that will not work well for a large grid.

Is there a way to center all of the buttons to the nearest cell? Is there a way to make them all the same size?

If not, perhaps there is a way to precisely position the buttons (via VBA) and then line the cells up to them?

Bob Phillips
03-12-2012, 05:16 AM
Create a table of positions and sizes on a worksheet and then use those values to set the actual buttons.

Paul_Hossler
03-12-2012, 06:14 AM
If you want to lay buttons over one or more worksheet cells, this might work for you


Option Explicit
Sub drv()

Call AddButtonOverCell(Range("D4"), "DummyMacro", "D4")
Call AddButtonOverCell(Range("G6"), "DummyMacro", "G6")
Call AddButtonOverCell(Range("L10"), "DummyMacro", "L10")
Application.CutCopyMode = False
End Sub

Sub AddButtonOverCell(ButtonCell As Range, ButtonMacro As String, ButtonCaption As String)
With ButtonCell.Parent
On Error Resume Next
.Buttons(ButtonCaption).Delete
On Error GoTo 0

.Buttons.Add( _
ButtonCell.Cells(1, 1).Left, ButtonCell.Cells(1, 1).Top, _
ButtonCell.Cells(1, 1).Width, ButtonCell.Cells(1, 1).Height).Name = ButtonCaption
.Buttons(ButtonCaption).Text = ButtonCaption
.Buttons(ButtonCaption).OnAction = ButtonMacro
End With
End Sub

Sub DummyMacro()
MsgBox "Hello World"
End Sub



I used .Cells(1,1), but you could relax that and allow a button to cover any number of cells, instead of just the first one

An extension might be to resize the worksheet cells to a desired height and width (e.g. squares, etc.) firstand then apply the buttons


Paul

ronjon65
03-13-2012, 04:00 PM
I also saw this code:

Sub TextBox2Cell()
With ActiveCell
ActiveSheet.Shapes.AddTextbox _
msoTextOrientationHorizontal, .Left, _
.Top, .Width, .Height
End With
End Sub

It works pretty well by fitting a text box to the size of the cell (and then I can assign a macro to it - instead of using a button). But is there a way to make the cell slightly smaller (centered in cell) such that it looks like a action? As it is, they all run together and they just look like cells rather than actions.

Bob Phillips
03-14-2012, 01:12 AM
Sub TextBox2Cell()
With ActiveCell
ActiveSheet.Shapes.AddTextbox _
msoTextOrientationHorizontal, .Left + 2, _
.Top + 2, .Width - 4, .Height - 4
With .Offset(1, 0)
ActiveSheet.Shapes.AddTextbox _
msoTextOrientationHorizontal, .Left + 2, _
.Top + 2, .Width - 4, .Height - 4
End With
End With
End Sub