Help: weild checkbox problem
 

Hello,

I have a sub which draws checkbox dynamicly. Most of the time it worked
fine. However, from time to time, the checkbox object name will not be
changed as instructed in the code. But if I run the sub again, the
problem will go away. Any ideas what is wrong?

Thank you very much.

------------------------------------------------------------
Sub drawCheckBox(nYears As Integer)
'This sub is used to dynamically draw checkBox in the DashBoard sheet
'
'
Dim i As Integer
Dim j As Integer
Dim leftCB As Integer
Dim topCB As Integer
Dim widthCB As Integer
Dim heightCB As Integer
Dim checkBoxName As String
Dim obj As OLEObject
Dim tempCB As OLEObject

For Each obj In Worksheets("DashBoard").OLEObjects
If obj.Name Like "CheckBox*" Then
'obj.Object.Value = False
obj.Object.Value = True ' this step is to unhide any hidden column
to avoid error
obj.Delete
End If
Next obj

widthCB = 67.5
heightCB = 15.75
topCB = 0

For i = 0 To nYears
If i < 6 Then leftCB = 500
If i = 6 And i < 12 Then leftCB = 500 + widthCB
If i = 12 And i < 18 Then leftCB = 500 + 2 * widthCB

checkBoxName = "CheckBox" & Year(Now()) - i

j = i Mod 6
'topCB = 329.5 + j * heightCB
topCB = 325.5 + j * heightCB
Set tempCB =
Worksheets("DashBoard").OLEObjects.Add(ClassType:= "Forms.CheckBox.1",
Link:=False, _
DisplayAsIcon:=False, Left:=leftCB, Top:=topCB,
Width:=widthCB, Height:= _
heightCB)

tempCB.Name = checkBoxName
'Worksheets("DashBoard").OLEObjects(checkBoxName). Object.Value =
False
Worksheets("DashBoard").OLEObjects(checkBoxName).O bject.Value =
True
Worksheets("DashBoard").OLEObjects(checkBoxName).O bject.Caption =
"Year " & Year(Now()) - i
Worksheets("DashBoard").OLEObjects(checkBoxName).O bject.BackColor =
&HC0FFC0
Next i

End Sub


--
huangx06
------------------------------------------------------------------------
huangx06's Profile: http://www.excelforum.com/member.php...o&userid=25014
View this thread: http://www.excelforum.com/showthread...hreadid=385420