dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
When I open my spreadsheet and delete contents out of a cell, I get a type mismatch error. This is my code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveSheet.Unprotect
        Dim ans As String
        If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Column
            Case Is = 1
                'If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
                If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                
                
                If Target.Value < Date Then
                    If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
                        Target.Value = ""
                    End If
                End If
            Case Is = 2
       [U]         If Target = "Activities" Then[/U]
                    Do
                        ans = InputBox("Please enter the Activities cost." & _
                        vbCrLf & "************************************" & vbCrLf & _
                        "To change an activity cost, select Activities from the Service list again.")
                        If ans <> "" Then
                            Cells(Target.Row, "N") = ans
                            Exit Do
                        Else
                            MsgBox ("You must enter a Activities cost.")
                        End If
                    Loop
                End If
            
        End Select
        End If
    Application.EnableEvents = True
'ActiveSheet.Protect
End Sub

I have underlined the line that gets highlighted. Could someone please help me find with what might the causing the problem as I don't know.

Thanks
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try using

Code:
If Target.value = "Activities"
 
Upvote 0
Try this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'ActiveSheet.Unprotect
  Dim ans As String
  'I suggest this go to the start before turning off the events.
[COLOR=#0000ff]  If Target.Count > 1 Or IsEmpty(Target) Then Exit Sub[/COLOR]
  '
  On Error GoTo App_Events
  If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Column
      Case 1
        If Target.Value < Date Then
          If MsgBox("This input is older than today !....Are you sure that is what you want ???", vbYesNo) = vbNo Then
            Target.Value = ""
          End If
        End If
      Case 2
[COLOR=#0000ff]        If LCase(Target.Value) = LCase("Activities") Then[/COLOR]
          Do
            ans = InputBox("Please enter the Activities cost." & _
              vbCrLf & "************************************" & vbCrLf & _
              "To change an activity cost, select Activities from the Service list again.")
            If ans <> "" Then
              Cells(Target.Row, "N") = ans
              Exit Do
            Else
              MsgBox ("You must enter a Activities cost.")
            End If
          Loop
        End If
    End Select
  End If
App_Events:
  Application.EnableEvents = True
  'ActiveSheet.Protect
End Sub
 
Upvote 0
Thanks for the reply guys, I will try those answers when I get back to work on Friday.
 
Upvote 0
Im glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top