VBA Type Mismatch

M

MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. I'm busy creating an application form for our company that will change based on the selections a user makes. I have VBA code that the MrExcel community helped me with a while ago that works fine with hiding/showing tick boxes.
The first selection the user makes is to pick from a dropdown what type of form he wants, namely "new account", "change account" or "termination of account". If new or change is selected, VBA shows a type mismatch error and highlights
VBA Code: 
If UCase(Target.Value) = "YES" Then
That is only a part of the code in that line. The full line is
VBA Code: 
If UCase(Target.Value) = "YES" Then Vis = True Else Vis = False

How can I fix this? I'm still very new to VBA. Here is the full code:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Vis As Boolean
    If Target.Address = "$B$7" Then
        Application.ScreenUpdating = False
'        ActiveSheet.Unprotect "abc123"
            If ActiveSheet.Range("B7").Value = "New Account" Then
                Range("C14:E14").Select
'                Performance Contract dropdown
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=Data!$G$2:$G$3"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = "Select ""Yes"" if the employee does not need any form of IT/phone/alarm code access, otherwise select ""No""."
                    .ErrorMessage = "Please select Yes or No"
                    .ShowInput = True
                    .ShowError = True
                End With
                Range("D7:E7,B15,B26,D9").Select
                Range("B26").Activate
                Selection.Locked = True
                Selection.FormulaHidden = False
                Range("B9:B12,D10:D11").Select
                Range("D11").Activate
                Selection.Locked = False
                Selection.FormulaHidden = False
                Selection.ClearContents
                Range("B8").Select
            Else
                If ActiveSheet.Range("B7").Value = "Termination of Account" Then
'                    ActiveSheet.Unprotect "abc123"
'                    Application.ScreenUpdating = False
                    Range("B9:B12,D7:E7,D9:E11,C14:E14").Select
                    Range("C14:E14").Activate
                    Selection.Locked = False
                    Selection.FormulaHidden = False
                    Range("D9").Select
                    Selection.Locked = False
                    Selection.FormulaHidden = False
                    Range("B9").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C7,""Unknown Employee"")))"
                    Range("B10").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C2,""Unknown Employee"")))"
                    Range("B11").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C8,""Unknown Employee"")))"
                    Range("B12:B13").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C9,""Unknown Employee"")))"
                    Range("D9").Select
                    ActiveCell.Formula2R1C1 = _
                        "=IF(R8C2="""","""",IF(R9C3<>"""",IF(XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")=0,"""",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C11,"""")),""""))"
                    Range("D10:E10").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C14,""Unknown Employee"")))"
                    Range("D11:E11").Select
                    ActiveCell.FormulaR1C1 = _
                        "=IF(R8C2="""","""",IF(R7C2=""Termination of Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C12,""Unknown Employee"")))"
                    Range("B9:B13,D10:E11").Select
                    Range("D10:E11").Activate
                    Selection.Locked = True
                    Selection.FormulaHidden = False
                    Range("B8").Select
                Else
                    If ActiveSheet.Range("B7").Value = "Change Account" Then
'                        ActiveSheet.Unprotect "abc123"
'                        Application.ScreenUpdating = False
                        Range("B9:B12,D9:D11,C14:E14").Select
                        Range("C14").Activate
                        Selection.Locked = False
                        Selection.FormulaHidden = False
                        Selection.ClearContents
                        Range("B9").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C7,""Unknown Employee"")))"
                        Range("B10").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C2,""Unknown Employee"")))"
                        Range("B11").Select
                        ActiveCell.FormulaR1C1 = _
                            "=IF(R8C2="""","""",IF(R7C2=""Change Account"",XLOOKUP(R8C2,'User Data'!C6,'User Data'!C8,""Unknown Employee"")))"
                        Range("B9:B11").Select
                        Range("B9:B11").Activate
                        Selection.Locked = True
                        Selection.FormulaHidden = False
                        Range("B8").Select
                    Else
                        Range("B9:B12,D9:E11").Select
                        Range("D10").Activate
                        Selection.Locked = False
                        Selection.FormulaHidden = False
                        Selection.ClearContents
                        Range("B8").Select
                    End If
                End If
                Range("C14:E14").Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
                    :=xlBetween
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
                Range("B8").Select
            End If
'        ActiveSheet.Protect "abc123"
'        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Application.ScreenUpdating = True
        End If
   
        If Target.Address = "$B$9" Then
            Application.ScreenUpdating = False
            ActiveSheet.Unprotect "abc123"
            If ActiveSheet.Range("B9").Value = "Contract" Or ActiveSheet.Range("B9").Value = "Councillor" Then
                Range("D9:E9").Select
                Range("D9").Activate
                Selection.Locked = False
                Selection.FormulaHidden = False
                Selection.ClearContents
                Range("B10").Select
            Else
                Range("D9:E9").Select
                Range("D9").Activate
                Selection.Locked = True
                Selection.FormulaHidden = False
                Range("B10").Select
            End If
'        ActiveSheet.Protect "abc123"
'        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Application.ScreenUpdating = True
        End If

    If UCase(Target.Value) = "YES" Then Vis = True Else Vis = False

'   Performance Assist
    If Target.Address = "$B$165" Then
        HiLiteShapes "Perf_", Vis
    End If

'   Eunomia
    If Target.Address = "$D$91" Then
        HiLiteShapes "Eunomia", Vis
    End If

'   Alarm Code & Building Keys
    If Target.Address = "$B$61" Then
        HiLiteShapes "Alarm", Vis
    End If

'   IT Equipment
    If Target.Address = "$E$55" Then
        HiLiteShapes "ITEquip", Vis
    End If
 
'   Risk Management
    If Target.Address = "$B$91" Then
        HiLiteShapes "RiskMan", Vis
    End If
   
'   SDBIP
    If Target.Address = "$B$152" Then
        HiLiteShapes "SDBIPAdmin", Vis
    End If
   
End Sub

Sub HiLiteShapes(MyStr As String, IsVis As Boolean)
    Dim MyShape As Shape
  
    For Each MyShape In ActiveSheet.Shapes
        If Left(MyShape.Name, Len(MyStr)) = MyStr Then
            MyShape.Visible = IsVis
        End If
    Next MyShape
End Sub

Sub RiskMan_Action_Owner_Change()
'   Risk Management - Action Owner
    HiLiteShapes "RiskOwn", Range("G144")
End Sub

Sub SDBIPAdmin_KPI_Owner_Change()
'   SDBIP - KPI Owner
    HiLiteShapes "SDBIPOwn", Range("G156")
End Sub

Thank you

EDIT: Just to add, the code works perfectly. Everything does what it is suppose to do. I just cant figure out why I'm getting the error.
 
Sort by date Sort by votes
I'm sorry to say this but the code is kind of a mess. I respond to lots and lots of questions like this not just here but another forum as well, and I just can't keep track of what came a week earlier. However, I do seem to remember giving you a rewrite of some of your code, but I can't find the post. Did you start a separate thread with questions on this same file?

Your code has this line:
VBA Code: 
    If UCase(Target.Value) = "YES" Then Vis = True Else Vis = False
that is executed every time any cell on the entire sheet changes. It also is executed if multiple cells change at the same time, as in the scenario you describe where you delete several cells. In that case, UCase fails with a Type Mismatch error because you are giving it a range of multiple cells--it can only handle a string, or one cell. But you are obviously not expecting YES to be in any cell, only a certain set of cells. So you need to scope down this test. You need to change the code at the end of your Sub to this. It has two improvements:

1. It uses If/ElseIf so you once you find a match for Target Address, it stops. In your original code it continues to test every option, even after it already finds one.
2. It pushes the test for YES down into each case.

VBA Code: 
'   Performance Assist
    If Target.Address = "$B$165" Then
        HiLiteShapes "Perf_", UCase(Target.Value) = "YES"

'   Eunomia
    ElseIf Target.Address = "$D$139" Then
        HiLiteShapes "Eunomia", UCase(Target.Value) = "YES"

'   Alarm Code & Building Keys
    If Target.Address = "$B$61" Then
        HiLiteShapes "Alarm", UCase(Target.Value) = "YES"

'   IT Equipment
    ElseIf Target.Address = "$E$55" Then
        HiLiteShapes "ITEquip", UCase(Target.Value) = "YES"
 
'   Risk Management
    ElseIf Target.Address = "$B$139" Then
        HiLiteShapes "RiskMan", UCase(Target.Value) = "YES"
    
'   SDBIP
    ElseIf Target.Address = "$B$152" Then
        HiLiteShapes "SDBIPAdmin", UCase(Target.Value) = "YES"
        
    End If



Also Your Worksheet_Change code has several If statements that all check to see if the changed cell is B7. All of that logic should be in one If statement. I'm sorry but I can't spare the time to rewrite your entire Sub. It needs some analysis because there may be some redundant processing in these cases.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim Vis As Boolean

    If Target.Address = "$B$7" Then
    End If

    If Target.Address = "$B$7" Then
    End If

    If Target.Address = "$B$7" Then
    End If

    If Target.Address = "$B$7" Then
    End If
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Thanks for the info. I realise my code is probably a mess, since I'm still very green with VBA and cobbling code together that I find on the internet/forums.
Did you start a separate thread with questions on this same file?
Click to expand...
No, everything is in this thread.

Thank you, I will make use of the Ucase code you provided above as it looks like that should fix the issue. It might only happen tomorrow though, since our financial year is ending today.
 
Upvote 0
Thank you very much for your help 6StringJazzer. It is much appreciated. The UCase code you provided fixed all instances of the Type Mismatch error.
I was not aware of the existence of "ElseIf", only "else if" and I have incorporated it in my "If ActiveSheet.Range("B7")" statements, so it should now hopefully be more optimized and less messy.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA optimization advice
Replies
3
Views
95
bobsan42
bobsan42
S
  • Question
Run time error 13 type mismatch
Replies
1
Views
133
Domenic
D
J
  • Question
VBA Code to Add Rows is slowing down
Replies
5
Views
504
JayB0730
J
M
  • Solved
Replacing delete with a value
Replies
3
Views
155
Alex Blakenburg
A
D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
239
John_w
J

Forum statistics

Threads
1,214,920
Messages
6,122,267
Members
449,075
Latest member
staticfluids

Share this page

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