make buttons move when scrolling

  • Thread starter Thread starter bramnizzle
  • Start date Start date
B

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?
 
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
 
I suppose that would be the easiest...however, a few of my sheets
have the buttons listed vertically.
 
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
 
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
 
Back
Top