Solved: commandbutton that "follows" cursor

[Ger]

Hi,

is it possible to make a commandbutton follow the cursor?

I've a sheet with columns A to NU. Rows 1 to 3 are "frozen".
Over cell a1 i've a commandbutton. Is it possible that this command button follows me if i scroll to the right/left?


Ger

[mikerickson]

Best option might be a floating command bar.

[Apps]

Hi,

If you are using 2000 or 2003 then maybe a new commandbar set to the Left of the screen (we use something similar on a template at work that I did)?


Put the following code into the module of the Worksheet that you need it on;
[VBA]
Private Sub Worksheet_Activate()

'create new toolbar on sheet activation
On Error Resume Next
CommandBars("MyNewToolbar").Delete 'delete if present due to unforseen reason
On Error GoTo 0
'add new toolbar positioned to Left of worksheet
With CommandBars.Add(Name:="MyNewToolbar", Position:=0, MenuBar:=False, Temporary:=True)

.Protection = msoBarNoCustomize

With .Controls.Add(Type:=msoControlButton) 'add button
.OnAction = "TheNameOfTheMacroToRun" 'name of macro to run on click
.FaceId = 1020 'picture to show
.Caption = "Click this button" 'mouseover tooltip message
End With

.Visible = True
End With

End Sub

Private Sub Worksheet_Deactivate()
'delete toolbar on sheet deactivation
On Error Resume Next
CommandBars("MyNewToolbar").Delete
End Sub
[/VBA]

However if you use 2010 then I know it puts it at the top of the screen in a different section to use.

Google FaceIDs as well if you don't like the image that is there - there's a lot to choose from

[frank_m]

This won't work for scrolling,
but if by "follow the cursor" you mean follow a selection change, then perhaps something like this:
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Shp As Object

Set Shp = ActiveSheet.CommandButton1

With Shp
.Left = ActiveCell.Left
.Top = ActiveWindow.Top
.Width = Range("A1").Width ' or a desired fixed width
.Height = Range("A1").Height ' or a desired fixed height
End With

Shp.Object.TakeFocusOnClick = False'Optional. keeps focus on the selected cell instead of the button

End Sub
[/vba]

[Ger]

I use the macro of Frank_m but i get an error (438).
I upload what i did to get it work.

Ger

[Bob Phillips]

It drives an ActiveX button, not a userform, so you need to add one to your worksheet.

[Ger]

Thx to all,

I added an ActiveXbutton. First the button wouldn't move. I saved the worksheet and closed it. After opening it worked.


Ger

[Aussiebear]

You will find that a lot, needing to save the changes and then re open it