enable/disable checkboxes based on other data

R

robert.hatcher

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 :blush:

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
 
C

Corey

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....
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 :blush:

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
 
R

robert.hatcher

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...
 
C

Corey

WorkSheet.
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Arrays and Checkboxes 3
Looping Checkboxes on Worksheet 2
Using Checkboxes to enable/disable vb 6
CheckBoxes on MultiPage 4
Checkboxes 1
Newbie : question on VBA and checkboxes 6
Macro to Hide Row based on Value and then change Checkbox value 2
add multiple checkboxes to userform at runtime 2

Top