Menubar

Purchase · £2.99

Introduction

Introducing Menubar

Menubar - a Bar that sits at the Top or Side of a Worksheet with Buttons to use as a switch to toggle an option or setting for Excel (Version 1.1 can also follow the Window when Scrolling)

Menubar is designed to let you add Buttons and Divider Shapes to a Bar Shape to produce a Placeholder Bar that sits at the Top or Side of a Worksheet. The Buttons use a Macro called Toggle to allow a Depressed look and to run any Macros after a user presses them using the Mouse. It also allows you to run a Macro from a Button Press and then Popup the Button again, restoring its Status

The Menubar itself comprises of 1 Shape and the Dividers are a set of Grouped Shapes containing 2 Shapes made up of thin lines of dark/light colours. You can position these whereever you like using the Shapes in the Selection Pane (use the arrow to position them or drag with the Mouse)

The Buttons themselves have all been ported from my Awesome Buttons Project. You can use a Button that has 2 Grouped Shapes, a Normal and Depressed Group containing 2 other Shapes, an Icon Shape and a Button Shape - both linked to the Toggle Macro that look great when Clicked using the Mouse. You can use a Popup Button, where you can Depress it using the Mouse and do something whilst you hold it down, before releasing it and letting it return to its default state. The Menubar looks great at 150% but it equally looks good at 100% or 80%

Version 1.1 Demonstrates using the Popup Buttons on a Moving Menubar that uses my Alternative Method Macros to allow single Shapes and Buttons to be Nested Grouped Shapes. When you Scroll using the Cursor Keys or Move and select a Cell with the Mouse, the Menubar follows you to the new Cell position. The Alternative Method Macros allow single Shapes/Buttons to be used and then Grouped anyway that you like. This Menubar was specifically designed for use in my Jellyfish Project

Version 1.3 now includes the Menubar I use in my Jellyfish Software, demonstrating lots of Popup Buttons and Macros

If you like this Project you may also enjoy


Awesome Buttons ·  Flat UI ·  Slider Toggle Buttons ·  Sliders ·  Small Toggle Buttons ·  Toggle Buttons ·  xlui ·  RadiObutton ·  CheckboX ·  Burger Buttons ·  Popup Burger Buttons

Prerequisites

  • Excel 2013 (Version 5) & Excel 2016 (32bit or 64bit, Version 8)
  • Basic to moderate Excel Skills and some VBA skills to copy a Grouped Shape and then link a Subroutine to a Shape and a Graphic object
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • Copy the Grouped Shapes/Icon for the Buttons and/or Dividers to add to the Bar Shape
  • Store the Toggled values in the Sheet as Defined Names
  • Use a Popup Button to Depress, do something whilst you hold it down, before releasing it and letting it return to its default state
  • Run a Macro from a Button Press and then Popup the Button again, restoring its Status
  • Use SVG Icons and change their Colour
  • Alternative Method Macros to allow single Shapes/Buttons to be used and then Grouped anyway you like
  • When you Scroll using the Cursor Keys or Move and select a Cell with the Mouse, the Menubar follows you to the new Cell position
  • Protect the Shape and Graphic Drawing Objects via VBA Code to prevent Selection












Installation

Menubar.xlsm is ready to use (version specific naming of the file may differ with each new release). Open the Menubar.xlsm File and test out the Buttons on all of the Example Worksheets to see how they toggle their respective setting values and how they sit alongside the various Shapes that make up the Menubar. In order to select any of the Shapes or Graphic Objects you must first Unprotect the Sheet. Please read all of the info on the individual Sheets and in this online documentation before you begin to create your own Menubars and Toggle/Popup Buttons

Usage

Menubar.xlsm


Using the Menubar in your own Project
To use the Menubar in your own Project, you can either Copy in the Sheet containing the Menubar and the Code Modules behind the Menubar into your own Project or you can Copy in the Code in the Code Modules and then Copy parts of the Menubar out and setup the Defined Names yourself

To Copy the Sheet and Code Modules into your own Project
Make a backup of your Project and the Menubar.xlsm file. Open your Project (it must be Macro-enabled ie. .XLSM or .XLSB etc.) and the Menubar.xlsm file. In order to select any of the Shapes or Graphic Objects you must first Unprotect the Sheets - do this from the Review Tab on the Ribbon. Right-click the Example1 Sheet in the Menubar.xlsm file and select 'Move or Copy'. Locate your Project in the Drop-down at the top of the Move or Copy Dialog. Click Create a Copy (bottom LHS of the Move or Copy Dialog) and click OK. This will Copy in the Example1 Sheet into your own Project. On the View Tab of the Ribbon, in the Macros Group, Click Macros and select the Menubar.xlsm!Toggle Macro and then click Edit to go into the Code. YOu should see both Workbooks in the Project Explorer Window (LHS). Drag both Code Modules, 'mdMenubar' and 'mdMenubarPopupButtons' into your own Project (top LHS). This means that you have now copied in all of the Code required to run drive the Buttons on the Menubar. Close the Menubar.xlsm file without Saving. Save your Project. Now don't click anything yet as it will reopen the Menubar.xlsm file as you now need to link up all of the Buttons to their respective Macros, Toggle or PopupToggle. Unprotect the Example1 Sheet if it is protected. You will need to link up 2 sets of Grouped Icons and Buttons to each Macro. So right-click on any Shape and use the Selection Pane on the Editing Group of the Home Tab of the Ribbon to make Selections easier. Select each Button Icon or Button in turn and assign the Macro. For example select the NormalText Shape using the Selection Pane, hover next to the Button, right-click and choose assign macro. Pick the Toggle Macro for the Text Buttons. Repeat this for the NormalButton, DepressedText and DepressedButton Shapes - you may need to click the little eye-droppers in the Group or individual Shapes to make the Buttons visible and editable (once you have finished and ran the Code it will toggle these settings automatically). After you have added the Example1 Sheet, your Project may look something like this as you assign your first Toggle Macro:



Test out the first Button. If everything works, then proceed through all of the rest of the Buttons. For the Icon Buttons the procedure is exactly the same. For the Popup Buttons you will need to assign the PopupToggle Macro to the NormalIcons, NormalButton, DepressedIcon and DepressedButton Shapes (this also applies to the MyMacro1_Normal5, MyMacro1_Pressed5 Shapes to run the Macro1_ Macro

To Copy the Sheet and Code Modules into your own Project
To setup Menubar in your Project without Copying in the Example1 Sheet, you need to Copy the Code Modules as per the instructions above. In order to select any of the Shapes or Graphic Objects you must first Unprotect the Sheets - do this from the Review Tab on the Ribbon. Now you can Copy out parts of the Menubar. Copy the Menubar Shape and the Dividers first. Select them all in the Selection Pane and then Copy and Paste into your Project - use CTRL+Left Mouse Button to multi-select each Group or Shape in the Selection Pane. Here is a Screen Shot of the Menubar and Dividers being selected:



Copy and then Paste the Shapes above into your Project first. It should look something like this - my Zoom is set to 100%, my Gridlines are turned on and the Sheet background is white, so it may look a little different to yours:



Okay let's add a Button. Select the Normal Group and the Depressed Group Shapes. Switch back to your Project and select the Menubar Shape. Copy and Paste the 2 Buttons onto the Menubar. While they are still selected move the cursor 4 points right and 4 points down to roughly align them. Select the NormalText and NormalButton and assign the Toggle Macro as per the image below. Repeat for the DepressedText and the DepressedButton Shapes:



To ensure that the Code does not Error. Select the NormalText Shape and edit the Alt. Text. It should say 'Button'. So the last thing you need to do is to add this Defined Name to a Cell. Click in an empty Cell. In the Name Box or using Formulas->Name Manager, add the word Button to the Cell. Type FALSE into the Cell. Repeat this for the Defined Names, Button1, Button2 and Button6 as these exist in the Code that you Copied. Now Click the Button to run the Code and update the Defined Name. It should look like this:



You should edit the Code and edit or remove the parts that reference Defined Names that you want/don't want. Also you may need to wrap parts of the Code up to encapsulate specific Defined Names and Buttons otherwise, for example, the Code will check and run bits of the Code that reference the Status of the Defined Name. For example, on clickiing the Heading Button you will notice the background colour changes and the Zoom is set to 150%

Changing the Alt. Text of the Buttons
I have done the hard work for you already, however if you have an error when creating a new Toggle Button if you use your own Shapes then remember to edit the Alt. Text to be Button, Button1, Button2 etc.

SVG Icons
The Icon Buttons use an SVG Icon. To change the Icon, you first need to Unprotect the Worksheet. Then right-click the Graphic Object or any Shape and use the Selection Pane on the Editing Group of the Home Tab of the Ribbon to select the Icon in a Toggle Group. Now choose, change Graphic, from Icons on the Change Group of the Graphics Format Tab on the Ribbon and pick another Icon. Here I have changed the Edit/Pencil Icon to a Bug Icon





Tips
Resizing the Buttons to create smaller ones:
Unprotect the Worksheet and select one of the Grouped Buton Shapes. Hold down Shift and then drag the object to resize it. Please note: you may have to tweak the positioning of the Icon and Button and/or use the Align Shape on the Arrange Group of either the Graphic Format or Shape Format Tab on the Ribbon

Activating the Cell to force a redraw of the Menubar:
Okay so you scrolled right in the Code but the bar did not follow? Well use something like this: ActiveSheet.Cells(28, 8).Activate to force the Menubar to display

Woah, my Menubar disappeared and then shrank!:
This will only happen when Ungrouping your Shapes to edit the Menubar, you then forget to set the Properties to 'Move, but don't Size with Cells' after you have Grouped all of the Shapes

Menubar

The Default Menubar that comes with the file

The Menubar with the Headings Text Button Depressed toggling the Headings On

Example2 Sheet - this Menubar is a tiny Vertical Menubar with Popup Buttons

Example3 Sheet - this Menubar is a tiny Horizontal Menubar with Popup Buttons that Moves as you Scroll in the Worksheet and activate Cells. These Nested Groups of Buttons are Moved as a single Shape called Menubar1. This Menubar was specifically designed for use in my Jellyfish Project

The Menubar used with my Jellyfish Forecasting Project - I plan to just have a single Back Button and 3 Scroll to Buttons for Planner Adjustments, Promo Adjustments and MI Adjustments so the final Menubar will be a little different, but it gives you a great feel of what you can do with Menubar in your own Projects

The Menubar used with my Jellyfish Forecasting Software has now been added into this Project

FAQ

Q. Woah, my Menubar disappeared and then shrank!
A. This will only happen when Ungrouping your Shapes to edit the Menubar, you then forget to set the Properties to 'Move, but don't Size with Cells' after you have Grouped all of the Shapes

Menubar Buttons

This is a video of the Menubar and the Example Sheets in Version 1.1

Support

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes

  • Responding to questions or problems regarding the Software and its features
  • Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include

  • Customization and installation services
  • Support for third party software or ANY kind of development whatsoever

Before seeking support

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • Ensure that you access to the VBOM is allowed and that Macros can run in Excel
  • Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Future Builds

* To make the Menubar dragable

Bug Fixes

* There are currently no bugs identified

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first

28.07.2021 - (Version 1.3)

Added the Menubar I use in my Jellyfish Software, demonstrating a moving Menubar with lots of Popup Buttons and Macros for navigation for a Table

03.07.2021 - (Version 1.2)

Added an API function that allows you to maintain a tiny pause that helps when integrating the Popup Buttons into your own Projects. The use of this and an additional DoEvents in the Code makes for a very pleasent UX experience. Changed the existing Public API's to Private API's 

 ' modify this depending on your specific requirements in the 'AltPopupToggle' Subroutine
 ' in order to update the Depressed Button to see the change it may be neccessary to use a slight Pause with another DoEvents
 ' it gives the impression of a much nicer UX ;)
 Pause 200
 DoEvents

01.07.2021 - (Version 1.1)

Demonstrates using the Popup Buttons on a Moving Menubar that uses my Alternative Method Macros to allow single Shapes and Buttons to be Nested Grouped Shapes. When you Scroll using the Cursor Keys or Move and select a Cell with the Mouse, the Menubar follows you to the new Cell position

30.06.2021 - (Version 1)

General release