The code below works okay, but it takes four inputboxes to get the info.

I tried to use a single InputBox for the four inputs and use Split to assign those values to the variables for the checkboxes.


So the inputbox window would look like: 5,H,4,MyCkBox

Want 5 checkboxes
In column H
Starting at row 4
Named "MyCkBox" (no quotes, followed by a number)

Split (InputBox string, ",")... etc.


Thanks,
Howard


Sub Add_Checkboxes()

Dim cBx, aNum As Long, aRow As Long
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
Dim aCol As String, MyNme As String


aNum = Application.InputBox(Prompt:="Enter the number of Check boxes you want:", Title:="My Check Boxes", Type:=1)


aCol = Application.InputBox(Prompt:="Enter the column letter of choice.", Title:="Check Box Column", Type:=2)


aRow = Application.InputBox(Prompt:="Enter the row number ." & vbCr & _
"for the first check box.", Title:="First Check Box Row", Type:=1)


MyNme = Application.InputBox(Prompt:="Enter the Caption (Name of Check Box).", Title:="My Name is...?", Type:=2)



Application.ScreenUpdating = False

For cBx = 1 To aNum

CLeft = Cells(aRow, aCol).Left

CTop = Cells(aRow, aCol).Top

CHeight = Cells(aRow, aCol).Height

CWidth = Cells(aRow, aCol).Width

ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select

With Selection

.Caption = MyNme & " " & cBx

.Value = xlOff

.Display3DShading = False

End With

aRow = aRow + 2 ' for every other row else aRow + 1
Next cBx

Application.ScreenUpdating = True

ActiveCell.Offset(1, 0).Activate
End Sub

Hi Howard,

Am Sat, 20 Jun 2015 00:47:11 -0700 (PDT) schrieb L. Howard:

So the inputbox window would look like: 5,H,4,MyCkBox

Want 5 checkboxes
In column H
Starting at row 4
Named "MyCkBox" (no quotes, followed by a number)

modify the prompt for the inputbox.
Try:

Sub Add_Checkboxes()

Dim cBx As Long, aRow As Long
Dim CLeft As Double, CTop As Double, CHeight As Double, CWidth As Double
Dim MyBoxes As String
Dim varBoxes As Variant


MyBoxes = Application.InputBox(Prompt:="Enter the number of CheckBoxes," _
& " the column letter, the row number of the first row and the caption of" _
& " the CheckBoxes comma separated(Name of Check Box)", Title:="myValues", Type:=2)


Application.ScreenUpdating = False

varBoxes = Split(MyBoxes, ",")
aRow = CInt(varBoxes(2))

For cBx = 1 To CInt(varBoxes(0))
CLeft = Cells(aRow, varBoxes(1)).Left
CTop = Cells(aRow, varBoxes(1)).Top
CHeight = Cells(aRow, varBoxes(1)).Height
CWidth = Cells(aRow, varBoxes(1)).Width

ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
With Selection
.Caption = varBoxes(3) & cBx
.Value = xlOff
.Display3DShading = False
End With

aRow = aRow + 2 ' for every other row else aRow + 1
Next cBx

Application.ScreenUpdating = True

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

Exactly what I was looking for.

I was using some similar examples, but could not get it done correctly.

Thanks much.

Howard