Grid of Buttons - Sizing and Positioning

[ronjon65]

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]

Create a table of positions and sizes on a worksheet and then use those values to set the actual buttons.

[Paul_Hossler]

If you want to lay buttons over one or more worksheet cells, this might work for you

[vba]
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

[/vba]

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]

I also saw this code:

[vba]Sub TextBox2Cell()
With ActiveCell
ActiveSheet.Shapes.AddTextbox _
msoTextOrientationHorizontal, .Left, _
.Top, .Width, .Height
End With
End Sub
[/vba]
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]

[vba]

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
[/vba]