Hey team i am looking for a way to have a floating bar on a specific worksheet. I want to use a bar that can control the sheet going up, down and to the side. I have tried a few ways and i just can't to get it right.
I think the horizontal and vertical scroll bars on a worksheet meet the navigational needs very well. However, here's how you can create a custom toolbar and make it visible for a particular sheet.
a. Write the macros you wish to run from the new toolbar and test them by running them directly.
b. Right-click on the menu bar and chose 'Customize...'
c. In the dialog box click 'New' - you'll be prompted for a name for the new toolbar - type 'RoyBar' or whatever...
d. Got to the 'Commands' tab and from 'Macros' in the list-box drag the smiley-face button that appears on to the new toolbar as many times as the number of macros you need to run from RoyBar.
Remember NOT to dismiss the dialog box at this stage
e. Edit each button face / button text by right-clicking and selecting the suitable choice from the shortcut menu. This will determine if the button will show a graphic or text.
f. Right-click each button to 'Assign macro' to each of them. Assign the macros written as mentioned in 'a' - one to each button.
g. Go back to the 'Toolbars' tab of the still visible dialog box and click 'Attach'
h. In the left section of the new dialog box select 'RoyBar' and click 'Copy' to attach it to the workbook. Click OK to dismiss this dialog box.
i. Click 'Close' to dismiss the 'Customize' dialog box.
j. Go to the Visual Basic Editor (Alt+F11) and dbl-click the ThisWorkbook module under Excel Objects in the Project Explorer window to make the module active.
k. In this module paste the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Don't worry, this won't do anything catastrophic - it'll only ensure that the toolbar is available only when this workbook is open.
l. Let's say, you want this bar to appear only when a sheet called 'RoySheet' is active. In the Project Explorer window of the Visual Basic Editor, dbl-click the icon named 'RoySheet' and make it active. In this module paste the following code:
Private Sub Worksheet_Activate()
CommandBars("RoyBar").Visible = True
Private Sub Worksheet_Deactivate()
CommandBars("RoyBar").Visible = False
This will ensure that the toolbar is visible only when RoySheet is the active sheet.