Floating Command Button - How to???
 

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

Floating Command Button - How to???
 

Place your command button in the top row and then select B1. From the
Menu bar select Window then Freeze Panes. :)


---
Message posted from http://www.ExcelForum.com/

Floating Command Button - How to???
 

In addition to freezing the pane, you could also try adding a "split" to the
window or adding a toolbar.

Tim

"TinyTim" wrote in message
...
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

Floating Command Button - How to???
 

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



"TinyTim" wrote in message
...
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

Floating Command Button - How to???
 

You could freeze panes, or you could experiment with a commandbar object.
CommandBars are available in VBA help.



"TinyTim" wrote in message
...
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

Floating Command Button - How to???
 

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.

--
HTH,
Dianne

In ,
TinyTim typed:
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

Floating Command Button - How to???
 

Thanks all for your help. I'm focusing in on the commandbar object.

tt

On Wed, 31 Dec 2003 10:51:29 -0600, "Dianne"
wrote:

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.