Joeybl
04-26-2024, 12:37 AM
Hello!
Im quite new to using VBA in excel and while trying to make a macro to one of my workbook, ive encountered an error i cant figure out how to get past.
I've taken inspiration from Excelforfreelancers inventory management system, and modified the code to fit my needs, but it returns with an compile error: Invalid or unqualified reference, highlighting the issue to be "Sub Menu_select"
I confess i dont 100% understand the scope of this function, as every macro i use is more or less just recorded with the exception of a few, so this has left me a little confused.
Any help/explanation of what is going wrong here is greatly appreciated.
I have 8 shapes with different names(cities) which i want to function as a "button" to jump to a specific range in my workbook, whilst hiding everything else and freezing the rows from B6 and up, coloring the selected button as indication of selection.
Sub Menu_Select()
Dim MenuItem As Long
MenuItem = Replace(Application.Caller, Left(Application.Caller, 8), "")
'Color Menu Shapes
For MenuItem = 1 To 8
.GroupItems("MenuItem").Fill.ForeColor.RGB = RGB(35, 108, 146) 'Standard Menu Item Color
Next MenuItem
.GroupItems("MenuItem").Fill.ForeColor.RGB = RGB(147, 202, 229) 'Selected Menu Color
End With
.Range("B:CK").EntireColumn.Hidden = True 'Hide All columns
.Range("B6").EntireRow.Select
ActiveWindow.FreezePanes = True
Select Case MenuNumb
Case Is = 1 'Provided Equipment
.Range("B:L").EntireColumn.Hidden = False 'Show Columns
Case Is = 2 'Randers
.Range("S:Y").EntireColumn.Hidden = False 'Show Columns
Case Is = 3 'Djursland
.Range("AD:AJ").EntireColumn.Hidden = False 'Show Columns
Case Is = 4 'Risskov
.Range("AU:BA").EntireColumn.Hidden = False 'Show Columns
Case Is = 5 'Horsens
.Range("BE:BK").EntireColumn.Hidden = False 'Show Columns
Case Is = 6 'Skanderborg/samsų
.Range("BQ:BW").EntireColumn.Hidden = False 'Show Columns
Case Is = 7 'Aarhus
.Range("CE:CK").EntireColumn.Hidden = False 'Show Columns
Case Is = 1 'Viby
.Range("CUA").EntireColumn.Hidden = False 'Show Columns
ThisWorkbook.RefreshAll 'Refresh all Pivot Table Data
ActiveWindow.FreezePanes = False
End Sub
Im quite new to using VBA in excel and while trying to make a macro to one of my workbook, ive encountered an error i cant figure out how to get past.
I've taken inspiration from Excelforfreelancers inventory management system, and modified the code to fit my needs, but it returns with an compile error: Invalid or unqualified reference, highlighting the issue to be "Sub Menu_select"
I confess i dont 100% understand the scope of this function, as every macro i use is more or less just recorded with the exception of a few, so this has left me a little confused.
Any help/explanation of what is going wrong here is greatly appreciated.
I have 8 shapes with different names(cities) which i want to function as a "button" to jump to a specific range in my workbook, whilst hiding everything else and freezing the rows from B6 and up, coloring the selected button as indication of selection.
Sub Menu_Select()
Dim MenuItem As Long
MenuItem = Replace(Application.Caller, Left(Application.Caller, 8), "")
'Color Menu Shapes
For MenuItem = 1 To 8
.GroupItems("MenuItem").Fill.ForeColor.RGB = RGB(35, 108, 146) 'Standard Menu Item Color
Next MenuItem
.GroupItems("MenuItem").Fill.ForeColor.RGB = RGB(147, 202, 229) 'Selected Menu Color
End With
.Range("B:CK").EntireColumn.Hidden = True 'Hide All columns
.Range("B6").EntireRow.Select
ActiveWindow.FreezePanes = True
Select Case MenuNumb
Case Is = 1 'Provided Equipment
.Range("B:L").EntireColumn.Hidden = False 'Show Columns
Case Is = 2 'Randers
.Range("S:Y").EntireColumn.Hidden = False 'Show Columns
Case Is = 3 'Djursland
.Range("AD:AJ").EntireColumn.Hidden = False 'Show Columns
Case Is = 4 'Risskov
.Range("AU:BA").EntireColumn.Hidden = False 'Show Columns
Case Is = 5 'Horsens
.Range("BE:BK").EntireColumn.Hidden = False 'Show Columns
Case Is = 6 'Skanderborg/samsų
.Range("BQ:BW").EntireColumn.Hidden = False 'Show Columns
Case Is = 7 'Aarhus
.Range("CE:CK").EntireColumn.Hidden = False 'Show Columns
Case Is = 1 'Viby
.Range("CUA").EntireColumn.Hidden = False 'Show Columns
ThisWorkbook.RefreshAll 'Refresh all Pivot Table Data
ActiveWindow.FreezePanes = False
End Sub