Type mismatch

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
Try using

Code:
If Target.value = "Activities"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,690
Office Version
2007
Platform
Windows
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
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,025
Thanks for the reply guys, I will try those answers when I get back to work on Friday.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,690
Office Version
2007
Platform
Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,077,731
Messages
5,335,893
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top