Floating Command Button - How to???

  • Thread starter Thread starter TinyTim
  • Start date Start date
T

TinyTim

Is there a way to create a "floating" command button so where ever the
user scrolls on the worksheet the command button remains in a fixed
position. Examples anyone?

Thanks,

tt
 
Place your command button in the top row and then select B1. From the
Menu bar select Window then Freeze Panes. :)
 
In addition to freezing the pane, you could also try adding a "split" to the
window or adding a toolbar.

Tim
 
You can try something like this



put the code in the "ThisWorkbook" object.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
Sheet1.CommandButton1.Left = Selection.Left
Sheet1.CommandButton1.Top = Selection.Top
End Sub
 
You could freeze panes, or you could experiment with a commandbar object.
CommandBars are available in VBA help.
 
The best I could come up with is to put the code into the worksheet's
SelectionChange event:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim btn As OLEObject
Dim rng As Range

Set rng = Windows(1).Panes(1).VisibleRange.Cells(1, 1)
Set btn = ActiveSheet.OLEObjects("CommandButton1")

btn.Top = rng.Top + 20
btn.Left = rng.Left + 20

Set rng = Nothing
Set btn = Nothing
End Sub

This won't reposition on scrolling, but when the user clicks into the
newly visible area, the button will reappear. Kinda clunky. Also, this
doesn't work if you've frozen panes.

Your best bet might be, as Rob says, a commandbar object.
 
Back
Top