Floating button

[MNJ]

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

[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