VBA code help

[Joeybl]

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

[Aussiebear]

Welcome to VBAX Joeybl. A couple of initial points here. End With require a With, and Select Case requires a End Select. So you need to have a quick rethink about the layout of your code



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 '<----- Where is the "With" that should precede this? Normally we would use "With Sheet2", "With Range("A2")" etc then complete the task with an End With. Since you dont have anything selected, then the "End With" is useless.


Again with the next section of code



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("CU:DA").EntireColumn.Hidden = False 'Show Columns


You cant have two Case 1's in the select process. I am assuming that you are wanting to hide all columns in the range B:CK, until a selection is made (1 to 7) and then unhide certain columns based on the selection. Would it also be true that say If the User selected 1 initially and columns B:L are unhidden, then the User selected 2, are you expecting columns B:L to then be re-hidden and columns S:Y to be unhidden? You should also need to consider using Appplication.ScreenUpdating to stop the flashing of the screen as well.

[georgiboy]

Hi Joeybl, welcome to the forum.

On this forum, we like to use something called 'code tags' when supplying code. I have added code tags to your code above for you as you are new here.

In my signature at the bottom of this post, you will see a link that should help you with the process of adding code tags in the future.

[Paul_Hossler]

Also, as others have said, there seems to be some important lines missing.

Might be better to include a small workbook as an attachment with any shapes, etc that you're using