make buttons move when scrolling

[bramnizzle]

All of my sheets have buttons with assigned macros. Some of my sheets
are very long and I don't want to have to scroll all the way back up
to the top to use them. Is there a way to make the buttons move as
you scroll down the sheet?

 

[Zone]

The easiest way is probably to put the buttons on row 1 and then freeze row
1. If you still want them to move when you scroll, post back. James

 

[bramnizzle]

I suppose that would be the easiest...however, a few of my sheets
have the buttons listed vertically.

 

[Susan]

yes, i had found some code on the internet that allows your button to
always move with you as you scroll..... unfortunately i don't have it
here @ work with me, it's @ home. i can bring it tomorrow & post it
if nobody else answers you.
sorry for the delay.
susan

 

[JLGWhiz]

You might find what Susan was referring to on this site:

http://blogs.msdn.com/frice/archive/2005/11/14/492560.aspx

 

[Susan]

nope, different idea.......
(i randomized it, that's what's in the cells in the spreadsheet. you
could easily take that out).... watch the word wrapping.
********************************

in the worksheet:

cell K701 = =RAND()*5
cell K702 = =ROUND(K701,0)


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'by Orlando Magalhães Filho in .programming

'revised 6/28/07 - added the commandbutton movement
'as below - original code did not dim variables

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.
'this does it frequently enough to
'update it often.

If Range("k702") = 4 Then

Application.ScreenUpdating = False

x_offset = 305 'was 10 2 makes it top left corner 305 makes it
start @ column J
y_offset = 2 'was 10 2 makes it top left corner

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
*************************
hope it helps!
susan