"Reading" Excel VBA checkboxes and their captions

[Eliezer]

I have a loop that calls this once for each of 44 sales
reps names, passing the name of the rep to this function
as an argument. There are 44 corresponding checkboxes
("ctl") which I want to "read" (i.e. determine whether
they're checked or not) and output a "true" or "false"
depending on whether the user checked that particular
rep's checkbox.


I have the following code:


Function isDesiredRep(repName As String) As Boolean
'loop through all rep names and read their respective
checkbox;
' if the rep's checkbox is checked, return TRUE
Dim ctl As Control
For Each ctl In mainForm.repsNames_frame.Controls
If ctl.Value = True And ctl.Caption = repName Then
isDesiredRep = True
Else
isDesiredRep = False
End If
Next
End Function



On the line that begins with "If ctl.value", I am getting
the error "Object doesn't support this property or method."

What am I doing wrong, please?

Thanks,
Eliezer

 

[JMMach, MCSC]

Why not define the ON CHANGE event for each check box to define the
variable, or write it to a textbox, or maybe write it to a cell in your
spreadsheet as well? Are the checkboxes on a form or in the spreadsheet?
Are the checkboxes Active-X controls, or are they from the Forms toolbar?
The type of control will determine what properties the control has.
TTFN
JMMach

 

[Eliezer]

JMMach,

The checkboxes are on a form from the Forms toolbar. I
really want to have them exactly as they are, not the
other suggestions you had mentioned...

This line of code is the line I need and does *not* work:
If ctl.Value = True And ctl.Caption = repName Then

This line is *not* what I need, but *does* work:
If ctl.Name = "rep2_cb" Then

How - PLEASE - can I accomplish what I am trying to do in
that first line of code (above)?

Thanks so much,
Eliezer

 

[Dave Peterson]

How about:

Option Explicit
Function isDesiredRep(repName As String) As Boolean
Dim CBX As CheckBox
isDesiredRep = False
For Each CBX In ActiveSheet.CheckBoxes
If LCase(CBX.Caption) = LCase(repName) Then
If CBX.Value = xlOn Then
isDesiredRep = True
End If
Exit For
End If
Next CBX
End Function

I think I might have taken a slightly different approach and named each checkbox
after the representative.

Then you would only have to check that one checkbox:

Option Explicit
Function isDesiredRep(repName As String) As Boolean
isdesiredrep = false
on error resume next
isdesiredrep = cbool(activesheet.checkboxes(repname).value = xlon)

 

[Eliezer]

Dave,

Thanks so much for straightening me out on my programming
logic! The syntax you suggested didn't work, though.
This did:

Function isDesiredRep(repName As String) As Boolean
'loop through all rep names and read their respective
checkbox;
' if the rep's checkbox is checked, return TRUE
Dim cntrl As Control
isDesiredRep = False
For Each cntrl In mainForm.repsNames_frame.Controls
If cntrl.Caption = repName Then
If cntrl.Value = True Then
isDesiredRep = True
End If
Exit For
End If
Next cntrl
End Function

Thanks again!
Eliezer

 

[Dave Peterson]

I misread your "The checkboxes are on a form from the Forms toolbar."

Since you wrote that they were from the Forms toolbar, I figured your Form was a
nicely formatted worksheet.

But glad you got it working.