Buttons should float in the Worksheet

Hi Just Shan,
why don't you create a floating toolbar?
I got a file as yours with buttons (24!) and I fixed it with a floating bar that is always visible wherever you are in the sheet, in all sheets

To start:
******************
Sub CreateToolBar()
On Error Resume Next
Application.CommandBars("General Menu").Visible = True
If Err = 0 Then Exit Sub
Set mybar = CommandBars.Add(Name:="General Menu", Position:=msoBarFloating, _
temporary:=True)
Set newmenu = Application.CommandBars("General Menu").Controls.Add(Type:=msoControlPopup, temporary:=True)
newmenu.Caption = " Click here for Options! "
Set menu0 = newmenu.CommandBar.Controls _
.Add(Type:=msoControlButton, ID:=1)
menu0.Caption = "Save As..."
menu0.OnAction = "SaveAs"
menu0.FaceId = 3
*******************
Sub SaveAs()
Dim f As Variant
f = Application.GetSaveAsFilename(InitialFileName:="XXXX.xls", FileFilter:="Excel Workbook (*.xls),*.xls")
If f = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=f
End Sub
*******************

Bernard

Where would you put the code Createtoolbars and Saveas ? in a standard module or in thisworkbook ?

I have a module including the whole code and the Sub's for the toolbar, and the toolbar is called from the "ThisWorkbook" code page "Workbook_open()

****************
Private Sub Workbook_Open()
Worksheets("Checker").activate
CreateToolBar
FormEngFran.Show
End Sub
****************

and to get the toolbar available only in this particular file, if active:
in ThisWorkbook:
****************
Private Sub Workbook_Activate()
Application.CommandBars("General Menu").Visible = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("General Menu").Delete
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("General Menu").Visible = False
End Sub
****************

Thanks Vicber.

Best Regards,

S h a n

That's really neat

Is there anyway to position the menu in the top left corner?

Sure:

Position:=msoBarLeft
Position:=msoBarRight

others are, I think, but not sure, Up, Down

Bernard

Thanks, I'll put it to good use on Monday

Just Shan said:

Dear Experts,

I have buttons (macros) on top of my worksheets. The sheet might contain more than 1000 rows.

Now, what I want is that the buttons should remain constant on top (floating) horizontally and vertically even when the sheet is scrolled down or right etc.

An example would be Windows | Freeze Panes. But I need an alternative.

Any suggestions for the captioned would be appreciated.

Best Regards,

S h a n

Click to expand...

You could also have the Macros assigned to a control on the Worksheet Menu which is always on display.That would be much easier and wouldn't get in the way.

The captioned are good alternatives.

But is it not possible to make Forms Buttons / Commandbuttons Float ?