Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
enable/disable checkboxes based on other data
I'm not sure what background is needed here so I'm going to
probably over post ... I have a macro that inserts checkboxes directly to a worksheet. The worksheets are created programmatically in a separate module prior to the checkboxes being inserted. Each checkbox is placed at the intersection of a unique column and a row common to all of the checkboxes. Each checkbox is independently named based on the column heading, for example, "chkBx_DC_RES". I need to enable or disable the checkboxes based on the presence or absence data in a different row in the parent column. Basicly the checkbox provides the choice of using the data if it is present. if the data is not present the box should be unchecked and disabled (not selectable) I need a suggestion or starting point for the code, obviously :o Should this code reside in the Sheet code or in the modules? If it needs to be in the worksheet code, I need to be able to created the sheet code each time I create a new sheet so I would do that as part of or subsequent to installing the checkboxes. Thanks Robert The checkbox installation code follows: Public Sub InsertChkBxs() 'from addcondlimitnames Dim colRng As Range Dim rowRng As Range Dim isect As Range Dim SheetName As String Dim chkBxRow As Range Dim elctCompCol 'Electrical Component Collection Dim elctCompObj 'Electrical Component Object Dim isectLeft Dim isectTop Dim OLEObj As OLEObject Dim chkBxNm As String Dim chkBxCap As String Application.ScreenUpdating = False SheetName = ActiveSheet.Name 'collection of Electrical Components types (column headings) elctCompCol = Array("DC_RES", "IMP_100_Hz", "PHASE_100_Hz", "LC_100_Hz", "QD_100_Hz", _ "IMP_200_Hz", "PHASE_200_Hz", "LC_200_Hz", "QD_200_Hz", _ "IMP_400_Hz", "PHASE_400_Hz", "LC_400_Hz", "QD_400_Hz", _ "IMP_1_kHz", "PHASE_1_kHz", "LC_1_kHz", "QD_1_kHz", _ "IMP_2_kHz", "PHASE_2_kHz", "LC_2_kHz", "QD_2_kHz", _ "IMP_4_kHz", "PHASE_4_kHz", "LC_4_kHz", "QD_4_kHz", _ "IMP_10_kHz", "PHASE_10_kHz", "LC_10_kHz", "QD_10_kHz", _ "IMP_20_kHz", "PHASE_20_kHz", "LC_20_kHz", "QD_20_kHz", _ "IMP_40_kHz", "PHASE_40_kHz", "LC_40_kHz", "QD_40_kHz") 'establish checkbox row Set chkBxRow = Cells.Find("SpecU").Offset(rowoffset:=-1, columnoffset:=0).EntireRow 'cycle through the Electrical Components For Each elctCompObj In elctCompCol Set colRng = ActiveSheet.Cells.Find(elctCompObj) Set isect = Application.Intersect(colRng.EntireColumn, chkBxRow) isect.Select isectLeft = isect.Left isectTop = isect.Top chkBxNm = "chkBx_" & elctCompObj chkBxCap = "Use Spec" 'insert checkbox at intersection (isect) With ActiveSheet Set OLEObj = ..OLEObjects.Add(classtype:="Forms.CheckBox.1", _ Left:=isect.Left, Top:=isect.Top, Height:=11.25, Width:=46.5) OLEObj.Name = chkBxNm OLEObj.Object.Caption = chkBxCap OLEObj.Object.Alignment = 0 OLEObj.Object.AutoSize = True OLEObj.Object.Font.Size = 6 OLEObj.Object.MousePointer = 14 OLEObj.Object.BackStyle = 0 'somehow fixes location problem when worksheet is zoomed With OLEObj .Left = isect.Left .Top = isect.Top End With End With Next Application.ScreenUpdating = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enable/disable button based on cell value | Excel Discussion (Misc queries) | |||
Enable/Disable CommandBar Buttons Based on Events | Excel Programming | |||
Using Checkboxes to enable/disable vb | Excel Programming | |||
Conditional formatting based on decision to enable/disable macros? | Excel Discussion (Misc queries) | |||
How can enable and disable menuitems based on the type of sheet. | Excel Programming |