Checkbox array

dmaxton

Board Regular
Joined
Mar 31, 2004
Messages
61
Is there anyway to set up an array or collection of checkboxes?

This is what I have tried so far (all code is indented and my explanation of what my intent is not indented):

Declare an array containing 7 checkboxes
Public ClaimType(6) as CheckBox

I have 7 checkboxes named "ClaimType0" through "ClaimType6". I want to set each item in the checkbox array to the appropriate checkbox.
Set ClaimType(0) = ClaimType0
Set ClaimType(1) = ClaimType1
Set ClaimType(2) = ClaimType2
Set ClaimType(3) = ClaimType3
Set ClaimType(4) = ClaimType4
Set ClaimType(5) = ClaimType5
Set ClaimType(6) = ClaimType6

Now I want to put a check in each checkbox based on whether or not there is a "X" in a cell in a different sheet ("Docket" is the worksheet that contains the cells with the X's, and "Input_Output" is the sheet that contains the checkboxes). IntColumn is the first column that contains the X's.

intColumg = 27
Do While intColumn <= 27
If Docket.Cells(intRow, intColumn).Value = "X" Then
Input_Output.ClaimType(intIndex).Value = 1
Else: intput_outPut.ClaimType(intIndex).Value = 0
End If
intColumn = intColumn + 1
intIndex = intIndex + 1
Loop

Right now I get error window that says:
Compile Error:
Method or data member not found

and it stops at:
Input_Output.ClaimType(intIndex).Value = 1

with .Claimtype highlited
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
What is Input_Output?

Is it a userform?

You can cycle through all the controls on a userform like this.
Code:
Dim ctl
     
     For Each ctl In Me.Controls ' Me is a reference to the form
          ' do something with the control ctl
     Next
You could test within the loop for the type or name of the control.
Code:
Dim ctl
Dim chks(2) As MSForms.CheckBox
     
     For Each ctl In Me.Controls ' Me is a reference to the form
        If TypeOf ctl Is MSForms.CheckBox Then
            Set chks(i) = ctl
            i = i + 1
        End If
     Next
 

dmaxton

Board Regular
Joined
Mar 31, 2004
Messages
61
Input_Output is a worksheet:

Public Input_Output As Worksheet

Set Input_Output = Worksheets("Input_Output")


Docket is a worksheet also.

I'll work with what you sent and see how far it get.

Thanks!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
What I sent won't work with controls on a worksheet, it's for a userform.

If you want to set the value of a checkbox according to a cell why not link it to the cell or another cell that returns True/False based on the content of the cell?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
"IntIndex" is = to zero on the first loop!

Your first index needs to be 1.

So above your loop add:

intIndex = 1
 

dmaxton

Board Regular
Joined
Mar 31, 2004
Messages
61
I don't think I can link the checkbox to a cell because the cell changes. Basically i have a data sheet with rows of data (this is the Docket sheet). I then have a separate sheet I call an Input_Output sheet. This sheet presents the data from a particular row on the Docket sheet.

There are 7 checkboxes that capture the data in the Docket sheet. If the first column (in my example they start in column 27) contains an "X", I want the check box "ClaimType1" to be checked. If the next column contains and "X", I want check box "ClaimType2" to have a check.

I also have code that as the user selects a different row on the Docket sheet, the information on the Input_Output sheet is updated with that row's data.

I will also have several groupings of checkboxes I need to do this with.

Hope this helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,086
Messages
5,570,136
Members
412,305
Latest member
Mozz
Top