yes, i snatched some coding off the internet that does exactly that.
unfortunately i don't think i have it here at work........
aha! i found it!
'=============================
'revised 6/28/07 - added the commandbutton movement
'as below - original code did not dim variables
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'by Orlando Magalhães Filho in .programming
Dim x_offset As Long
Dim y_offset As Long
Dim xpos As Long
Dim ypos As Long
'revised 7/16/07
'added a random control controlling this action,
'doing it every time there was a worksheet_change
'was too slow & cumbersome.
If Range("k702") = 4 Then
Application.ScreenUpdating = False
x_offset = 10
y_offset = 10
ActiveSheet.Shapes("CommandButton1").Select
With Cells(ActiveWindow.Panes(1).ScrollRow, _
ActiveWindow.Panes(1).ScrollColumn)
' xpos = .Right + x_offset THERE IS NO .RIGHT PROPERTY
xpos = .Left + x_offset
ypos = .Top + y_offset
End With
With Selection
.Left = xpos
.Top = ypos
End With
Target.Select
Application.ScreenUpdating = True
End If
End Sub
'============================
i made a few modifications - since i didn't want this worksheet change
to fire completely every single time there was a change, i placed
=RAND()*5
in k701 and
=ROUND(K701,0)
in k702
the macro checks that cell (k702), & if it doesn't equal 4 it just
exits - much quicker, and yet it does update the command button to my
current location fairly often. also, adjust the x, y offsets to suit
- for me i set these to 2.
hope it helps!
susan
