Checkbox with "true" value not appearing checked 1

[rabley]

I have been teaching myself VBA to create a data entry form for entering my company's products into a spreadsheet. The form includes 30 textboxes where the user can enter the specific brand’s possible colors, and a checkbox for each to indicate whether that color should be used for a specific product (e.g., Product A comes in Red, Blue and Green, but Product B only comes in Red and Green). Ideally, this form allows the user not to have to reenter the colors for every product, since they are all drawn from the same collection of possibilities.

I am trying to create a “Select All Colors” feature that will only check boxes where the user has entered a color. Here is a sample of what I have right now:

Dim varTxtColor(3) As Variant
    varTxtColor(0) = txtColor0
    varTxtColor(1) = txtColor1
    varTxtColor(2) = txtColor2

Dim varChkColor(3) As Variant
    varChkColor(0) = chkColor0
    varChkColor(1) = chkColor1
    varChkColor(2) = chkColor2

Dim intM As Integer
    
    For intM = 0 To 2
        If Not varTxtColor(intM) = "" Then
            varChkColor(intM) = True
        End If
    Next

When I test it using message boxes, the state of varChkColor(intM) shows up as True when varTxtColor(intM) isn’t blank, so that part is somewhat working, but the checkboxes never appear checked on the form and my error-catching message of “You haven’t selected a color” pops up when I try to submit. I’ve tried changing to varChkColor(intM).value = true, but the debugger says I need an object.

I know this must be related to the fact that you can’t make an array of controls. I’ve used this kind of workaround elsewhere in the form:

For Each ctrl In frmTPMSDataEntry.Controls
     If ctrl.Tag = "4" Then
          ctrl.Value = True
     End If
Next

But this only lets me select *all* color checkboxes (Tag 4), even when no color has been entered in the corresponding text box. Is there any way around this? Thanks in advance for any advice.

 

[Crowley16]

you're not testing for nulls...

why would you need to create an array of text boxes when you can just store the values of the text boxes?

also, there is a controls collection you can loop thorough, expecially if you have a good naming scheme...

--------------------
Procrastinate Now!

 

[rabley]

Thanks for answering my post. Do you mean I should put:

if not isnull(varTxtColor(intM)) then

instead of:

if not varTxtColor(intM) = "" then
?

I'm sorry, I don't know what you mean by "store the values of the text boxes." Where would I store them? I created the array because I couldn't figure out a way to increase the last digit in the name of the control by one to get through all 30.

I also don't know what you mean by looping through the controls collection. Can you point me to an example? My naming scheme is as in the example - "chkColor0", "chkColor1", etc.

 

[PHV]

but the debugger says I need an object
Replace this:
varChkColor(0) = chkColor0
with this:
Set varChkColor(0) = chkColor0

As for the Controls collection:
For intM = 0 To 2
frmTPMSDataEntry.Controls("chkColor" & intM) = (Trim(frmTPMSDataEntry.Controls("txtColor" & intM) & "") <> "")
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rabley]

That worked perfectly , PH. Thank you so much! Now I wish I hadn't spent so much time tearing my hair out before giving up and asking for help