MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Checkbox Code - Help!


Posted by Chris on October 26, 2001 7:10 AM

I am writing some code to go down column A, and if any cell in column A has a value I want to insert a checkbox on the corresponding role in column B. I can get that to work no problem.

I then have another bit of code that will make the caption of the textbox equal the value of the cell in column A. This is the bit that wont work. The problem is that as the checkbox doesn't exist at the point of compiling the VBA module Excel doesn't recognise that the checkboxes will create these during the code thus the sub will not work.

I have tried to split this process down into 2 subroutines - one to create the checkboxes, and another to name them with the understanding that when the second VBA module is compiled the checkboxes will exist and therefore the code will work. But this doesn't work either.

I'm sure the answer is simple - but I can't figure it out. I've attached the code below.

Anyone know what i'm doing wrong?

Chris

Public myName(100) As String
Public N As Integer


Sub TextCheckBox()

Dim l As Single
Dim T As Single
Dim W As Single
Dim H As Single
Dim CellAddress As Variant

N = 1

Do Until ActiveCell.Row = 100
If ActiveCell.Value <> "" Then
myName(N) = ActiveCell.Value
N = N + 1
CellAddress = ActiveCell.Address

ActiveCell.Offset(0, 1).Select

l = ActiveCell.Left
T = ActiveCell.Top
W = ActiveCell.Width
H = ActiveCell.Height

ActiveSheet.OLEObjects.Add(classtype:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=l, Top:=T, Width:=W, Height:= _
H).Select

Range(CellAddress).Select
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

formatcheckboxes

End Sub


Sub formatcheckboxes()

Dim i As Integer
Dim chkBox(100) As CheckBox

i = 1

Do
Sheet1.chkBox(i).Caption = myName(i)
i = i + 1
Loop Until i = N

End Sub


Posted by Juan Pablo on October 26, 2001 8:00 AM

Use this code... it's simple. I assumed column A starting at A1 if not, then change Cells(i,1) to Cells(i, Colnumber) and change For i = 1 to 100 to For i = startrow to 100

Sub TextCheckBox()

Dim l As Single
Dim T As Single
Dim W As Single
Dim H As Single
Dim i As Single
Dim CBNew As OLEObject

For i = 1 To 100
If Cells(i, 1).Value <> "" Then

l = Cells(i, 2).Left
T = Cells(i, 2).Top
W = Cells(i, 2).Width
H = Cells(i, 2).Height

Set CBNew = ActiveSheet.OLEObjects.Add("Forms.CheckBox.1", Left:=l, Top:=T, Width:=W, Height:=H)
CBNew.Object.Caption = Cells(i, 1)
End If
Next i

Juan Pablo ActiveCell.Offset(0, 1).Select