Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Unhappy Unanswered: Method 'Range' of Object_Worksheet failed

    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?

  2. #2
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Unhappy

    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by Excel-erate2004
    I am stuck on something. I keep getting a Run time error on this line:

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

    Any suggestions?
    *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


    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
    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.

    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
    ... 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.
    Last edited by savbill; 08-29-04 at 12:49.
    ~

    Bill

  4. #4
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •