Type mismatch

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,042
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,975
Office Version
2013
Platform
Windows
Try using

Code:
If Target.value = "Activities"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,923
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,042
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,923
Office Version
2007
Platform
Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,463
Messages
5,340,458
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top