Buttons should float in the Worksheet

[Excel-ent]

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

 

[vicber]

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

 

[Excel-ent]

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

 

[vicber]

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
****************

 

[Excel-ent]

Thanks Vicber.

Best Regards,

S h a n

 

[AngAgius]

That's really neat

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

 

[vicber]

Sure:

Position:=msoBarLeft
Position:=msoBarRight

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

Bernard

 

[AngAgius]

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

 

[Jaafar Tribak]

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

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.

 

[Excel-ent]

The captioned are good alternatives.

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