Results 1 to 4 of 4

Thread: VBA code help

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Apr 2024

    VBA code help


    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
        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
    Last edited by georgiboy; 04-26-2024 at 01:23 AM. Reason: Added code tags

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts