Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
problem with checkbox control | Excel Worksheet Functions | |||
Problem copying checkbox values | Excel Programming | |||
Problem in adding item to a checkbox | Excel Programming | |||
Excel VBA - Userform Checkbox/Textbox Problem | Excel Programming |