Displaying a message box when a Named Range is not present

[Boulevardier]

Hello all!

I'm just getting into using the VBA for Excel and learning a lot. I'm currently writing a code that grabs an initial named range, and autofills from that 'Start' range thru a second range defined as 'Whole', and then returns the user to the active cell they were in when they ran the macro.

Right now, if the user hasn't set up the 'Start' and 'Whole' named ranges, the code won't work and they get a runtime error. I'd like the code to begin by checking for the named ranges and if it can't find them, then to display a message box to the user to define them, and end the code. Just a simple 'ok' button in the box.

What I've got so far is:
Sub MetFill()

Set myCell = ActiveCell

Range("Start").Select
Selection.AutoFill Destination:=Range("Whole"), Type:=xlFillDefault

myCell.Select

End Sub

All help would be appreciated, thanks!


--Jimmy

 

[JoeMo]

Welcome to the forum. Maybe something like this - change the messages to suit.

Sub test()
'assumes named ranges, if any, 'Start' and 'Whole' are scoped to workbook level
Dim S As Range, W As Range, msg As String, ct As Long
On Error Resume Next
Set S = Range("Start")
If S Is Nothing Then
    msg = "There is no range named 'Start' in this workbook"
    ct = ct + 1
    On Error GoTo 0
End If
On Error Resume Next
Set W = Range("Whole")
If Not W Is Nothing Then
    If ct = 1 Then
        MsgBox msg
        GoTo Nx
    End If
Else
    If ct = 1 Then
        msg = msg & vbNewLine & "There is no range named 'Whole' in this workbook"
        On Error GoTo 0
        MsgBox msg
        Exit Sub
    Else
        msg = "There is no range named 'Whole' in this workbook"
        On Error GoTo 0
        MsgBox msg
        Exit Sub
    End If
End If
MsgBox "Named Ranges 'Start' and 'Whole' both exist"
Nx:
'rest of code
End Sub

 

[Micron]

Might one need the sheet name as part of the named range name?

 

[JoeMo]

Might one need the sheet name as part of the named range name?

Not if the name is scoped to workbook level (Excel's default when creating a named range) per my assumption in the first comment line I posted earlier.

 

[Boulevardier]

Welcome to the forum. Maybe something like this - change the messages to suit.

Sub test()
'assumes named ranges, if any, 'Start' and 'Whole' are scoped to workbook level
Dim S As Range, W As Range, msg As String, ct As Long
On Error Resume Next
Set S = Range("Start")
If S Is Nothing Then
    msg = "There is no range named 'Start' in this workbook"
    ct = ct + 1
    On Error GoTo 0
End If
On Error Resume Next
Set W = Range("Whole")
If Not W Is Nothing Then
    If ct = 1 Then
        MsgBox msg
        GoTo Nx
    End If
Else
    If ct = 1 Then
        msg = msg & vbNewLine & "There is no range named 'Whole' in this workbook"
        On Error GoTo 0
        MsgBox msg
        Exit Sub
    Else
        msg = "There is no range named 'Whole' in this workbook"
        On Error GoTo 0
        MsgBox msg
        Exit Sub
    End If
End If
MsgBox "Named Ranges 'Start' and 'Whole' both exist"
Nx:
'rest of code
End Sub

Thank you for the warm welcome and the solution to my problem! It works great!

 

[JoeMo]

Thank you for the warm welcome and the solution to my problem! It works great!

You are welcome - thanks for the reply.