0

Multiple page workbook, with hidden sheets shown when on the "Setup" sheet, there is a value placed in a cell (Range is F4:F23). The issue is when you delete the contents of multiple Cells in that range, the pages do not hide, and you get " Run-time error '13': Type mismatch

Tried Target.Range Tried If Not

        Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Column = F And Len(Target.Value) > 0 Then

        If Evaluate("Countif(F:F," & Target.Address & ")") > 1 Then
            MsgBox Target.Value & " is a duplicate entry.", vbExclamation, "Duplicate Entry"
            Range(Target.Address).ClearContents

        End If

         End If

Then goes on for the code to show or hide, and name worksheet based on value entered. (Which works fine)

Selecting and deleting the contents of 1 cell at a time hides the worksheet and does not return an error, deleting the contents of multiple cells returns an error and does not hide the additional worksheets and remove the name. I already have a macro created to clear the setup sheet, but can't use it! I need to be able to clear the contents of multiple cells at a time.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dan
  • 1
  • 1
  • How is the code working in the first place? `If Target.Column = F And Len(Target.Value) > 0 Then` is not *right*. Even if you had `Target.Column = "F"` you will get an error. One more thing. Since you are working with `Worksheet_Change` and clearing the cells, I highly recommend that you read [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Apr 06 '19 at 15:13
  • Sorry, in the Original, Target.Column = 6 – Dan Apr 06 '19 at 16:55
  • Thanks for the answer, it was simple to fix, added "On Error Resume Next" and it fixed it! – Dan Apr 06 '19 at 17:04
  • "On Error Resume Next" is hardly a solution – DisplayName Apr 06 '19 at 18:41
  • It's the solution I deserve, but not the solution I need. Yes, I'm a Batman fan. And yes, I'm new to VBA working on a workbook used for work to make it more efficient. The VBA coding here is not mine, My coding is limited to a few macros that are assigned to buttons, auto-filling a few cells, and setting the scroll area(Because I actually know how to do those things), I prefer to use formulas in cells whenever possible, because I know how limited I am with VBA.... Watched several classes just to do the macros, and I still get the feeling my head will explode. – Dan Apr 06 '19 at 19:47

0 Answers0