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
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
Best option might be a floating command bar.
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
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]
Last edited by frank_m; 09-09-2011 at 12:31 PM. Reason: edited the code comments
I use the macro of Frank_m but i get an error (438).
I upload what i did to get it work.
Ger
It drives an ActiveX button, not a userform, so you need to add one to your worksheet.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
You will find that a lot, needing to save the changes and then re open it
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link