Getting an Error on a Pop Up Box

[mrjrobinson]

Hi All,

I have created a Pop Up Box when a field contains a phrase, but whenever the cell its looking at is blank I get a "Run-time error '13': Type mismatch.

My code I have used is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


If Worksheets("Member Tracker").Range("E3").Value = "Pay As You Go" Then
MsgBox ("Customer has to pay before entry")
End If
End Sub

Im guessing I need to add in something to say do nothing if the cell is blank, but stumped as to what to put. Any help with this is really appreciated.

Many thanks

 

[Fluff]

Can you please explain what you are trying to do.
Also what sheet is this event running on?

 

[mrjrobinson]

Can you please explain what you are trying to do.
Also what sheet is this event running on?

Hi!

So I have created a Member Tracker for a leisure centre. Part of this doc I have created a SEARCH field that when you enter in the membership number for the customer, along the top of the screen it will show the members information, Name, Membership Type, Expiry etc. So this information changes each time a membership number is entered into the search field. One thing I have added is when the cell E3 has the phrase Pay As You Go in it, I want a Pop Up Box to show up with the phrase "Customer has to pay before entry". This works all fine. But when I remove the membership number from the search box, makes field which shows the membership type blank and then the mismatch error.

Here are screenshots to backup what I have said above.

https://ibb.co/T2v1M0V
https://ibb.co/q0kkmLN
https://ibb.co/8xMBD7y

 

[Fluff]

At a guess cell E3 has a formula that returns #N/A when there is no number in the search box.
What cell is the Search box in?

 

[mrjrobinson]

At a guess cell E3 has a formula that returns #N/A when there is no number in the search box.
What cell is the Search box in?

Search field is B3

 

[mrjrobinson]

https://drive.google.com/file/d/11ML8vPkHsmFNJj4uUObspchO0pLfWTJg/view?usp=sharing

 

[Fluff]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B3" Then
      If Target = "" Then Exit Sub
      If Target.Offset(, 3).Value = "Pay As You Go" Then
         MsgBox ("Customer has to pay before entry")
      End If
   End If
End Sub

 

[mrjrobinson]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "B3" Then
      If Target = "" Then Exit Sub
      If Target.Offset(, 3).Value = "Pay As You Go" Then
         MsgBox ("Customer has to pay before entry")
      End If
   End If
End Sub

Thanks for that, I have tried this and its not worked. I have attached a link to the work book above if you wanted to have a blast on it.

 

[Fluff]

That's because you are using an abomination called merged cells.
try

      If Target.Offset(, 2).Value = "Pay As You Go" Then

 

[mrjrobinson]

That's because you are using an abomination called merged cells.
try

      If Target.Offset(, 2).Value = "Pay As You Go" Then

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Offset(, 2).Value = "Pay As You Go" Then
MsgBox ("Customer has to pay before entry")
End If
End Sub

Would this be what you mean?