Floating button

[MNJ]

Hi :hi: ,

I just wanna ask if there's such stuff like floating buttons. Meaning, the buttons would be still within view even when you scroll down. If there really is such a thing, pls provide me with a macro.

Thanks!

[Charlize]

Maybe by using the top position of a button on the screen (or window). If the position is less than a certain number. Is there something like scroll_event to track the scrolling of a window ?

Charlize

[Charlize]

But why not use a custom floatbar (you can put it where you like). Still visible when you scroll.

Charlize

[Charlize]

This little thing I use in my code (partially in dutch for the labels). It creates a menu on auto_open (or workbook_open). First calls delete menu to prevent errors (if menu still exists) before creating the menu again. I use this only with one workbook open at the time (I think the other workbooks would have that bar to. If someone knows a way to disable the showing in the other workbooks, change the code and let me know.)

Sub Create_Menu()
Dim MyBar As CommandBar
Dim MyPopup As CommandBarPopup
Dim MyButton As CommandBarButton
Delete_Menu
Set MyBar = CommandBars.Add(Name:="Schuldbemiddeling", _
Position:=msoBarFloating, temporary:=True)
With MyBar
.Top = 50
.Left = 650
Set MyPopup = .Controls.Add(Type:=msoControlPopup)
With MyPopup
.Caption = "Werkbladen"
.BeginGroup = True
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Tabel Schuldeisers"
.Style = msoButtonCaption
''' msoButtonAutomatic, msoButtonIcon, msoButtonCaption, or msoButtonIconandCaption
.BeginGroup = True
.OnAction = "Tab_Schuldeisers"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Overzicht Schulden"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Over_Schulden"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Betalingen Schulden"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Bet_Schulden"
End With
End With
Set MyPopup = .Controls.Add(Type:=msoControlPopup)
With MyPopup
.Caption = "Afdruk/Nieuwe betaling"
.BeginGroup = False
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Nieuwe betaling"
.Style = msoButtonCaption
.BeginGroup = True
.OnAction = "Nw_Bet"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Afdrukken"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Afdrukken"
End With
End With
.Width = 130
.Visible = True
End With
End Sub

Sub Delete_Menu()
On Error Resume Next
CommandBars("My Menu").Delete
On Error GoTo 0
End Sub

[Bob Phillips]

You can also dock them in the toolbar region, just add



MyBar.Position = msoBarTop


instead of msoBarFloating

[Charlize]

Thanks for the tip.

[MNJ]

Hi,
Thanks for your various replies. I need them so the ' back to main button ' and 'back to top' button will be visible. It seems necessary cause my list is very long. Charlize could you kindly tell me how should i edit from your code if i use two command buttons for my respective purposes?

Thanks.

[MNJ]

My code for the back to main button is :



Private Sub CommandButton10_Click()
Worksheets("Main").Activate
Sheets("Sheet12").Visible = False
End Sub




And ' back to top' button is




Private Sub CommandButton9_Click()
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
End Sub




Thanks

[Charlize]

Put this code in a module. Run the menu with Alt+F8 and create_menu.
The code for the actual button must be placed in a public module (I think) in order to execute when you select an option on the floating menu bar.

Sub Create_Menu()
Dim MyBar As CommandBar
Dim MyButton As CommandBarButton
Delete_Menu
'name of your floating bar
Set MyBar = CommandBars.Add(Name:="Two Buttons", _
Position:=msoBarFloating, temporary:=True)
With MyBar
.Top = 50
.Left = 650
'the two buttons that you want
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Back to main"
.Style = msoButtonCaption
.BeginGroup = False
'here you refer to the code to execute when you select this option
'put code to execute in module (not a private one)
.OnAction = "Macro_to_go_to_main"
End With
Set MyButton = .Controls.Add(Type:=msoControlButton)
With MyButton
.Caption = "Back to top"
.Style = msoButtonCaption
.BeginGroup = False
.OnAction = "Macro_to_go_to_top"
End With
.Width = 150
.Visible = True
End With
End Sub

Sub Delete_Menu()
On Error Resume Next
'remove menu when active before activating again.
CommandBars("Two Buttons").Delete
On Error GoTo 0
End Sub

Charlize

[MNJ]

Thanks! I'll try it tml and get back to you. :)

[lucas]

Sub Macro_to_go_to_top()
ActiveSheet.Range("A1").Select
End Sub

works for back to top of active sheet.

[MNJ]

Hi all,

Thanks for your replies! :yes Everything seems simpler now and neater. Last thing, one of my macro is used to go back to main page.
When i accidentally click the go to main button when I'm at my main page, it disappears.

What I wanna ask u guys is : how do i exclude the main sheet from the ' go back to main' macro.

currently this is my code.



Sub macro_to_go_to_main()
ActiveSheet.Visible = False
Worksheets("main").Activate
End Sub





Thanks! :friends:

[lucas]

Can I ask why your using this:


ActiveSheet.Visible = False

[lucas]

Sub macro_to_go_to_main()
If ActiveSheet.Name <> "Main" Then
ActiveSheet.Visible = False
Worksheets("main").Activate
End If
End Sub

[MNJ]

This is because I need to hide my sheets tabs. They are too messy.

Thanks for the macro! =)

[lucas]

No problem.....be sure to mark your thread solved using the thread tools at the top of the page if you got your solution. You can always post followup questions here regarding the same issue.

[Charlize]

This is because I need to hide my sheets tabs. They are too messy.

Thanks for the macro! =)
For hiding your sheettabs (only tested this on excel 2003).

Sub Hide_Tabs()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With ActiveWindow
.DisplayWorkbookTabs = False
End With
Next sht
Sheets(1).Activate
End Sub

Sub Show_Tabs()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
With ActiveWindow
.DisplayWorkbookTabs = True
End With
Next sht
Sheets(1).Activate
End Sub
Charlize