Microsoft VBA Error "Sub or Function not defined"

Question

I'm trying to show and hide sheets in excel using the if else syntax but i get this error in the first line 'SUB OR FUNCTION NOT DEFINED'

the code is in** sheet 3** any help is appreciated!

Thank you/Faleminderit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Me.Range("C1"), Target) Is Nothing Then Exit Sub
If Worksheets(Sheet3).Range(C1).Value = "INTERIM" Then
    ShowHide (False)
    Worksheets(Sheet1).Visible = True
    Worksheets(Sheet3).Visible = True
ElseIf Worksheets(Sheet3).Range(C1) = "ESTIMATED_BUDGET" Then
    ShowHide (False)
    Worksheets(Sheet5).Visible = True
    Worksheets(Sheet3).Visible = True
ElseIf Worksheets(Sheet3).Range(C1) = "Final" Then
    ShowHide (False)
    Worksheets(Sheet6).Visible = True
    Worksheets(Sheet3).Visible = True
ElseIf Worksheets(Sheet3).Range(C1) = "ALL" Then
   ShowHide (True)
End If
End Sub

Sub Macro1()

End Sub

I tried different ways to do it and i get the same error

Where did you define what ShowHide is? Where did you define what Sheet3 is? Is it a string? Range(C1) should probably be Range("C1") since you didn't define what C1 is either. Maybe you mean Sheet3.Range("C1") ? — braX
– braX, Commented Feb 17, 2023 at 11:27

NotDanSLS · Accepted Answer · 2023-02-17 15:04:25Z

When testing here I created 2 sheets so I had Sheet1, Sheet2, and Sheet 3.

I had hadded your code to the cose of sheet 1 initially and straight away was getting errors. I think it is important to note that sheet names and cell references or ranges so need to be encapsualted with Quote marks ("Sheet3").

Once the Sheets and Cells where quoted I was able to continue. I'm not sure on the purpose of "ShowHide". I will assume you wanted to track if the sheets are visible or not, this will need to be declared.

I also recommend setting 'Option Explicit' at the top of the module, this helps with error handling and debugging when you have problems.

Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ShowHide As Boolean
            ShowHide = False
        
        If Intersect(Me.Range("C1"), Target) Is Nothing Then Exit Sub
       
        If Worksheets("Sheet3").Range("C1").Value = "INTERIM" Then
            ShowHide = False
            Worksheets("Sheet1").Visible = True
            Worksheets("Sheet3").Visible = True
        ElseIf Worksheets("Sheet3").Range("C1") = "ESTIMATED_BUDGET" Then
            ShowHide = False
            Worksheets("Sheet5").Visible = True
            Worksheets("Sheet3").Visible = True
        ElseIf Worksheets("Sheet3").Range("C1") = "Final" Then
            ShowHide = False
            Worksheets("Sheet6").Visible = True
            Worksheets("Sheet3").Visible = True
        ElseIf Worksheets("Sheet3").Range("C1") = "ALL" Then
            ShowHide = True
        End If
    End Sub

This worked as I expected.

Collectives™ on Stack Overflow

Microsoft VBA Error "Sub or Function not defined"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related