I am stuck on something. I keep getting a Run time error on this line:

Code:
WS2.Range("Rng" & k).Value = "NA"

It states that the Method 'Range' of Object_Worksheet failed. Here is my complete procedure:

Code:
Public Sub CommandButton4_Click()

Dim WS1 As Worksheet, WS2 As Worksheet
Dim Rng As Range
i As Integer
j As Integer
k As Integer

Set WS1 = Sheets("Step 1")
Set WS2 = Sheets("Step 2")
Set Rng1 = WS2.Range("C11, J11, C27, J27")
Set Rng2 = WS2.Range("D11, K11, D27, K27")
Set Rng3 = WS2.Range("E11, L11, E27, L27")
Set Rng4 = WS2.Range("F11, M11, F27, M27")
Set Rng5 = WS2.Range("G11, N11, G27, N27")

For i = 1 To 29
If WS2.OLEObjects("CheckBoxSpecies" & i).Object.Value = True Then
For j = 1 To 5
If WS1.OLEObjects("CheckBoxBedrockL" & j).Object.Value = False _ Then
For k = 1 To 5
WS2.Range("Rng" & k).Value = "NA"
Next
End If
Next
End If
Next

End Sub


Any suggestions?

I dont think I've structured my code the correct way. What I want it to do is, if these two conditions are met:

If WS2.OLEObjects("CheckBoxSpecies" & i).Object.Value = True

AND

If WS1.OLEObjects("CheckBoxBedrockL" & j).Object.Value =False


Then IF and only IF those 2 options are met for each, then display an "NA" in the cell range thats listed. The ranges need to be looked at individually.

OR alternatively:

Loop thru the 29 checkboxes on sheet2 and those that are selected (i.e. true) then loop thru the 5 checkboxes on sheet1 and those that are not selected (i.e. false) place an "NA" in the Cell ranges as listed.

It should work out like this, the combination would vary of course dependant upon the 2 conditions listed above, but each defined range is directly correlated with each checkbox, for example:

IF CheckBoxBedrockL1=true Then do nothing to these Cells("C11, J11, C27, J27") in Range 1
IF CheckBoxBedrockL2=false Then enter an NA in these Cells("D11, K11, D27, K27") in Range 2
IF CheckBoxBedrockL3=true Then do nothing to these Cells ("E11, L11, E27, L27") in Range 3
IF CheckBoxBedrockL4=false Then enter an NA in these Cells("F11, M11, F27, M27") in Range 4
IF CheckBoxBedrockL5=false Then enter an NA in these Cells("G11, N11, G27, N27") in Range 5

Still with me??

I'm not sure if that clarifies what I'm trying to do, I hope so because I'm stuck.

*Plus you've set Rng1, Rng2 etc. as OBJECTS so you are going to get an error if calling them as a RANGES.

*You can't apply the value to the range like that. You're better off with this.


For each cell in Range WS2.Rng1
.Value = "NA"
Next cell


You have 29 Checkboxes "CheckBoxSpecies#" and 5 "CheckBoxBedrockL# What is the sequence for the Logic comparison? 29/5 is not even? You will either have to use a loop with a step value or put all your object names in a two dimensional array to know what 29 CBSpecies go with which 5 CBBedrock#

You can do a Boollean comparison like this

bCBVals = (WS1.Checkboxes("FirstCB") = True AND WS2.Checkboxes("SecondCB") = False)

This will set bCBVals True if the condition is True or False.

... The non-contiguous cells defined as ranges and checkboxes association by code is hard to code and will require lots of work to maintain if you change the worksheets add controls etc. From what I see this would be better suited to do with on-sheet formulas NOT VB.

This is what I would do:

First WS2.OLEObjects??? If you are using OLE Checkboxes or ActiveX controls, remove them and use the XL Checkboxes which can be found on the Forms Toolbar. These can be referred to as WS.Checkboxes("name") or WS.Checkboxes(index)

The Excel Checkbox can be linked to a cell and will show True/False in the cell. Map each checkbox to a cell, usu a cell in the same row as the control, or if you have controls 1,2,3... Link to cells 1,2,3 in a corresponding column.

Next create a formula to put in each cell to be controlled by the checkbox controls.

=IF($A$1=TRUE,IF(Sheet2!$A$1=FALSE,"","N/A"),"N/A")

With this formula if the Checkbox on sheet 1 is TRUE and the Checkbox on sheet 2 is FALSE then it will be "" otherwise the value will be "N/A"

The ($) in front of the cell address makes the formula an Absolute Reference. This means the formula and can be moved without the formula changing, relative to the cell location, its hard coded to the checkboxes you set.

Thanks for the good suggestions, some of those I wouldn't have thought of.
The sheets will be static and wont change.

However, I'm still working on different options.

I've attached a text file thats the long version of what I'm trying to do. Maybe it will make more sense and a pattern can be found.

Thanks for your help