Run time error '424' - Help!

[vu_lan_nhi]

Hi

I am relatively new to macro, so excuse my question. I keep receiving error '424' (object required) while trying to run the macro below:

Private Sub MsgBoxProcessingCredits()


Application.EnableEvents = True
Application.ScreenUpdating = False
If Target.Address = Range("processingdropdown").Address Then
    Application.EnableEvents = False
    If Range("processingdropdown").Value = "Monthly" Then
msgbox ("Select number of months in dropdown list in column C")
Else
msgbox ("Overwrite column C with the flat rate amount for processing credit incentives")
End If
End If
     Application.EnableEvents = True
End Sub

Any help is very much appreciated!

 

[V_Malkoti]

You are using an object named Target in your If statement but its not initialized anywhere in your Sub.

 

[nardagus]

It seems you are trying to check if selected cell is within a named range. So why not use Selection.Address?

Private Sub MsgBoxProcessingCredits()

Application.EnableEvents = True
Application.ScreenUpdating = False
      If Selection.Address = Range("processingdropdown").Address Then
             Application.EnableEvents = False
             If Range("processingdropdown").Value = "Monthly" Then
                   msgbox ("Select number of months in dropdown list in column C")
             Else
                   msgbox ("Overwrite column C with the flat rate amount for processing credit incentives")
             End If
End If
     Application.EnableEvents = True
End Sub

Nard

 

[vu_lan_nhi]

It seems you are trying to check if selected cell is within a named range. So why not use Selection.Address?

Private Sub MsgBoxProcessingCredits()

Application.EnableEvents = True
Application.ScreenUpdating = False
      If Selection.Address = Range("processingdropdown").Address Then
             Application.EnableEvents = False
             If Range("processingdropdown").Value = "Monthly" Then
                   msgbox ("Select number of months in dropdown list in column C")
             Else
                   msgbox ("Overwrite column C with the flat rate amount for processing credit incentives")
             End If
End If
     Application.EnableEvents = True
End Sub

Nard

Hey thanks, Nard! It works now if I go into macro and hit run, but if I only changing the dropdown selection in my worksheet, the macro does not run when I switch back and forth between different dropdown selection. How should I fix this?

 

[nardagus]

For that you need Target.Address However to fire up the macro automatically you need to create an event which will keep a record of changes made to a worksheet.

Just place this in your desired worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
Application.ScreenUpdating = False
      If Target.Address = Range("processingdropdown").Address Then
             Application.EnableEvents = False
             If Range("processingdropdown").Value = "Monthly" Then
                   MsgBox ("Select number of months in dropdown list in column C")
             Else
                   MsgBox ("Overwrite column C with the flat rate amount for processing credit incentives")
             End If
End If
     Application.EnableEvents = True
End Sub

This will see all changes made to a worksheet but Target.Address will tell excel to fire up the macro only when target's address is equal to the address of your dropdown.

Nard

 

[vu_lan_nhi]

For that you need Target.Address However to fire up the macro automatically you need to create an event which will keep a record of changes made to a worksheet.

Just place this in your desired worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
Application.ScreenUpdating = False
      If Target.Address = Range("processingdropdown").Address Then
             Application.EnableEvents = False
             If Range("processingdropdown").Value = "Monthly" Then
                   MsgBox ("Select number of months in dropdown list in column C")
             Else
                   MsgBox ("Overwrite column C with the flat rate amount for processing credit incentives")
             End If
End If
     Application.EnableEvents = True
End Sub

This will see all changes made to a worksheet but Target.Address will tell excel to fire up the macro only when target's address is equal to the address of your dropdown.

Nard

thank you! You're a life saver

 

[nardagus]

No problem. Glad to help.

Nard