Error In Assigning a Name To A Set Range

[Ark68]

In my condensed code below a worksheet change event of cell K6 triggers code which defines a particular range basedon the value of K6. That newly defined range is named, and that named range is used in a data validation list for another cell (R6)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr_div As Range

    If Target.Address = "$K$6" Then
        mbevents = False
        With ws_form
            .Unprotect
            With .Range("R6, V6")
                .Value = ""
                .Interior.Color = RGB(189, 215, 238)
                .Borders.Color = RGB(48, 84, 150)
                If .MergeCells = True Then
                    Set mergeRange = .MergeArea
                    mergeRange.Locked = True
                End If
                .Validation.Delete
            End With
        End With
        leag = ws_form.Range("K6").Value
       
         if leag = "WLU" Or leag = "UW" Then
            Set nr_calibre = ws_lists.Range("G5:G9")
            Set nr_div = ws_lists.Range("H22:H24")
            t = 1
        Elseif ...
            ....
        End if
        
        ActiveWorkbook.Names.Add Name:="nr_calibre2", RefersTo:=nr_calibre
       
         With ws_form.Range("R6")
            .Validation.Delete
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = False
            End If
            .Value = ""
            .Interior.Color = RGB(221, 235, 247)
            .Borders.Color = RGB(48, 84, 150)
            .Validation.Add Type:=xlValidateList, Formula1:="=nr_calibre2"
            .Select
        End With

        ws_form.Protect
        mbevents = True
    End If

    If Target.Address = "$R$6" Then
        mbevents = False

        With ws_form  'reset v6
            .Unprotect
            With .Range("V6")
                .Value = ""
                .Interior.Color = RGB(189, 215, 238)
                .Borders.Color = RGB(48, 84, 150)
                If .MergeCells = True Then
                    Set mergeRange = .MergeArea
                    mergeRange.Locked = True
                End If
                .Validation.Delete
            End With
        End With
        calibre = ws_form.Range("R6").Value

        With ws_form.Range("R6") 'prepare for user entry, data validation list
            .Interior.Color = RGB(198, 244, 180)
            .Borders.Color = RGB(55, 86, 35)
        End With
        ActiveWorkbook.Names.Add Name:="nr_division", RefersTo:=nr_div   'define name using set range assigned with K6 change. ERROR: "Application-defined or object-defined error"
        With ws_form.Range("V6")
            .Validation.Delete 'delete any validations assigned to cell V6 before applying a new one
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = False
            End If
            .Value = ""
            .Interior.Color = RGB(221, 235, 247)
            .Borders.Color = RGB(48, 84, 150)
            .Validation.Add Type:=xlValidateList, Formula1:="=nr_division"
            .Select
        End With
        ws_form.Protect
        mbevents = True
    End If
End Sub

I am receiving an "Application-defined or object-defined error" with the line highlighted red in the code above when trying to name the range set previously to nr_div.

Not understanding where I have erred.

 

[Trebor76]

Hi Ark68,

Very hard to answer without having the workbook you're working on but as a guess I'd say the range nr_div is not getting set. Try stepping through the code with a break point on that line and hover over the named range when the code gets to the line in question. If it says nr_div = Nothing it hasn't been set. If that's the case check what's in cell K6 of the ws_form tab.

Robert

 

[Norie]

Does this work to name the range?

nr_div.Name = "nr_division'

 

[Ark68]

Hi Robert and Norie, thanks for your support.

Norie, unfortunately no, that does not work. It triggers an "Object variable of With block variable not set" error. Robert, Norie's suggestion aside, yes ... nr_div was nothing. The two errors would indeed suggest that nr_div isn't being set.

So the question is ... why? I do set that variable (nr_div) in the code highlighted in blue (2nd from the top). Does it get lost between being set the in the code and the user then selecting cell R6 which executes the code to apply the nr_div range to the name and subsequently to the validation list? I have never had this experience in any of the similar operations using similar code.

 

[Trebor76]

If that's the case check what's in cell K6 of the ws_form tab.

Did you do that? Don't forget that IF statements are case sensitive so if wLU is in K6 the code won't recognise it as WLU. Try using these amended lines in the top part of your code:

leag = StrConv(ws_form.Range("K6").Value, vbUpperCase)
       
If StrConv(leag, vbUpperCase) = "WLU" Or leag = StrConv("UW", vbUpperCase) Then

 

[Ark68]

Hi Robert,

The value in K6 is validated by a list validation method, so "WLU" (and all the other's I am experiencing this problem with) is (as far as I can see) the exact value to be matched in the if statement. I have recently replaced the number of If / Then statements with Select Case.
It is also doing it for and several others where two ranges are being set under one condition. The set nr_calibre works, just not nr_dvsion (I changed the name thinking maybe it was a naming issue). I've also restructured my code to make it slightly easier to understand.

Case Is = "WLU", "UW" Then
           Set nr_dvision = ws_lists.Range("H22:H24")           
           Set nr_calibre = ws_lists.Range("G5:G9")

and

Case Is = "WRDSB (Pub)", "WCDSB (Sep)" Then
            Set nr_calibre = ws_lists.Range("G5:G9")
            Set nr_dvsion = ws_lists.Range("H27:H29")

If anyone wishes to take a crack experience and troubleshoot the file, I have made it available here.

Workshetrt FORM will open blank with all fields locked except permit #.
Enter a numeric permit # 1-6 digits.
Event will unlock. ENter anything
Event type will unlock. Select Diaomond from the dropdown.
Rcode unlocks. Select DR.
Function unlocks. Select Slopitch.
League unlocks. Select WLU, UW, WCDSB (sep) or WRDSB (Pub). These all create the error. It is this selection which defines the nr_calibre and nr_dvsion ranges.
Calibre unlocks. Select anything. The calibre change event will trigger and stop. By continuing to run or stepping through, you should reach that error with

.Validation.Add Type:=xlValidateList, Formula1:="=nr_division"

in the calibre (R6) change event. (in the Change_Calibre module)

Thanks all.

 

[Fluff]

I would suggest that you put "Option Explicit" at the very top of the module (before any code) then try & run the macro.
I think it will give you a compile error "Variable not defined" & will highlight one of your variables as it looks as though you have made a typo, or two.

 

[Ark68]

Hi Fluff, so as suggested, I placed Option Explicit in each one of modules. Using the Compile VBA tool, I corrected all the missing undefined variables and I added the appropriate Dim statements. I did this in all the modules until the Compile VBA option is no longer avilable (understanding that means its without VBA errors).

When I run the application, I am still getting the error though. No compile errors.

 

[Fluff]

You have shown both

Set nr_dvision = ws_lists.Range("H22:H24")

and

Set nr_dvsion = ws_lists.Range("H27:H29")

which are spelt differently, have you corrected that?

 

[Ark68]

I must have. A search of "nr_dvision" didn't net any results.

Updated file here.