floating Bar

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
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.

Any solutions

Thanks, Roy
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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)
Application.CommandBars("RoyBar").Delete
End Sub
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
End Sub
Private Sub Worksheet_Deactivate()
CommandBars("RoyBar").Visible = False
End Sub
This will ensure that the toolbar is visible only when RoySheet is the active sheet.
 
Upvote 0

Forum statistics

Threads
1,223,490
Messages
6,172,584
Members
452,468
Latest member
godlennutrition

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top