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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
enable/disable checkboxes based on other data
CheckBox1.Value = False
CheckBox1.Value = True will Tick/Untick the CheckBox for you. You will need to add something like: If range("A1:A10").value = "" then Checkbox1.value = False else CheckBox1.value = true Not 100% with your code where the values will be in relation tot he CheckBox inserted, but you may need to refer to those cells as an Offset from the CheckBox Row/Column. Corey.... wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
enable/disable checkboxes based on other data
thanks Corey, I will work with that, the data locations are actualy
"named ranges" I can refer to. One more question, Should this code reside in the Sheet code or in the modules? to start with I'm going to make it part of the installing code (previously posted). But, I need the test to be ran anytime someone is using the worksheet or I need the Named Range to watched somehow... Corey wrote: CheckBox1.Value = False CheckBox1.Value = True will Tick/Untick the CheckBox for you. You will need to add something like: If range("A1:A10").value = "" then Checkbox1.value = False else CheckBox1.value = true Not 100% with your code where the values will be in relation tot he CheckBox inserted, but you may need to refer to those cells as an Offset from the CheckBox Row/Column. Corey.... wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
enable/disable checkboxes based on other data
WorkSheet.
wrote in message ups.com... thanks Corey, I will work with that, the data locations are actualy "named ranges" I can refer to. One more question, Should this code reside in the Sheet code or in the modules? to start with I'm going to make it part of the installing code (previously posted). But, I need the test to be ran anytime someone is using the worksheet or I need the Named Range to watched somehow... Corey wrote: CheckBox1.Value = False CheckBox1.Value = True will Tick/Untick the CheckBox for you. You will need to add something like: If range("A1:A10").value = "" then Checkbox1.value = False else CheckBox1.value = true Not 100% with your code where the values will be in relation tot he CheckBox inserted, but you may need to refer to those cells as an Offset from the CheckBox Row/Column. Corey.... wrote in message ups.com... 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 |
Reply |
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 |