"Reading" Excel VBA checkboxes and their captions
 

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

"Reading" Excel VBA checkboxes and their captions
 

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" wrote in message
...
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

"Reading" Excel VBA checkboxes and their captions
 

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




-----Original Message-----
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" wrote in
message
...
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


.

"Reading" Excel VBA checkboxes and their captions
 

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)
on error goto 0
end function


Eliezer wrote:

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

-----Original Message-----
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" wrote in
message
...
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


.


--

Dave Peterson

"Reading" Excel VBA checkboxes and their captions
 

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


-----Original Message-----
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)
on error goto 0
end function


Eliezer wrote:

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

-----Original Message-----
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" wrote
in
message
...
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


.


--

Dave Peterson

.

"Reading" Excel VBA checkboxes and their captions
 

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.



Eliezer wrote:

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

-----Original Message-----
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)
on error goto 0
end function


Eliezer wrote:

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

-----Original Message-----
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" wrote
in
message
...
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


.


--

Dave Peterson

.


--

Dave Peterson