Consulting

Results 1 to 4 of 4

Thread: VBA code help

  1. #1
    VBAX Newbie
    Joined
    Apr 2024
    Posts
    1
    Location

    VBA code help

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

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Administrator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,254
    Location
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2408, Build 17928.20080

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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
  •