Running a macro based on a specific ListBox selection

[jmpatrick]

Happy Friday!

Today I'm trying to run a macro based on a specific ListBox selection on a Sheet and in a named range.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("CalendarGarageHandlingColumn")) Is Nothing Then
 
    Select Case Range("CalendarGarageHandlingColumn")
 
        Case "<ADD NEW>": OpenAddNewGarageHandling

    End Select
    End If
 
End Sub

<ADD NEW> is one of the choices and I need to run OpenAddNewGarageHandling only when it is selected. The macro is in a Module.

I'm getting an error: Run-time error '13': Type mismatch

It's bumming me out.

 

[jmpatrick]

After messing with this it's starting to look like the fact that the drop-down is on a sheet rather than a UserForm is what's tripping me up.

 

[My Aswer Is This]

I have looked at you script but not sure what you trying to do.
You have your script as a sheet change event script but then say your script is in a module.
OK: What does the script in the module look like? Show me the script.
And what is:
If Not Intersect(Target, Range("CalendarGarageHandlingColumn")) Is Nothing
Is this a Column on your worksheet name Calendar Garage?

 

[jmpatrick]

Some progress. The problem appears to be the named range. When I change to a single cell rather than a range it works for that cell...

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("H868")) Is Nothing Then
 
    Select Case Range("H868")
 
        Case "<ADD NEW>": OpenAddNewGarageHandling

    End Select
    End If
 
End Sub

Now I need to get it working with a range.

 

[jmpatrick]

I have looked at you script but not sure what you trying to do.
You have your script as a sheet change event script but then say your script is in a module.
OK: What does the script in the module look like? Show me the script.
And what is:
If Not Intersect(Target, Range("CalendarGarageHandlingColumn")) Is Nothing
Is this a Column on your worksheet name Calendar Garage?

CalendarGarageHandlingColumn is the named range. The macro simply opens a UserForm to a specific tab and field...

Sub OpenAddNewGarageHandling()
Settings.Show
Settings.MultiPage1.Value = 7
Settings.NewGarageHandlingName.SetFocus
End Sub

 

[My Aswer Is This]

So talk to me a little.
So Settings is the Name of the userform Correct?
And what does this suppose to do?
Settings.NewGarageHandlingName.SetFocus

Set focus to what?

 

[jmpatrick]

So talk to me a little.
So Settings is the Name of the userform Correct?
And what does this suppose to do?
Settings.NewGarageHandlingName.SetFocus

Set focus to what?

Correct, Settings is the Userform.

NewGarageHandlingName is a field on the UserForm.

The Macro works fine. This code works fine with a specific cell targeted:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("H868")) Is Nothing Then
 
    Select Case Range("H868")
 
        Case "<ADD NEW>": OpenAddNewGarageHandling

    End Select
    End If
 
End Sub

I'm now trying to get it to work on a named range (CalendarGarageHandlingColumn).

 

[My Aswer Is This]

Well here is a script I wrote that gives you a example:
Range is named "ans"
If you enter "Yes" in Range named "ans" you get a message box to pop up.

Private Sub Worksheet_Change(ByVal Target As Range)
 'Modified  11/19/2021  12:24:55 PM  EST
    If Not Intersect(Target, Range("Ans")) Is Nothing Then
        Select Case Range("Ans")
            Case "Yes": MsgBox "Hello"
        End Select
    End If
 
End Sub

 

[jmpatrick]

Well here is a script I wrote that gives you a example:
Range is named "ans"
If you enter "Yes" in Range named "ans" you get a message box to pop up.

Private Sub Worksheet_Change(ByVal Target As Range)
 'Modified  11/19/2021  12:24:55 PM  EST
    If Not Intersect(Target, Range("Ans")) Is Nothing Then
        Select Case Range("Ans")
            Case "Yes": MsgBox "Hello"
        End Select
    End If
 
End Sub

That's exactly the code that I put in my original post, other than I used my existing named range.

 

[My Aswer Is This]

And I do not understand this:
"<ADD NEW>"

Your actually entering: <ADD NEW>
Or is it ADD NEW