Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I stop graphs reading zero in Excel from cells with ""? | Charts and Charting in Excel | |||
How do I stop graphs reading zero in Excel from cells with ""? | Excel Worksheet Functions | |||
Why does my excel file have an extension reading ".xls:1"? | Excel Discussion (Misc queries) | |||
reading variable "from" and "to" ZIP codes | Excel Programming |