How to make created button move with scrollbar?

  • Thread starter Thread starter Bon
  • Start date Start date
B

Bon

Hello all

How to make created buttons in worksheet move with worksheet scrollbar?

I have a workbook and there are 10 worksheets in it. Each worksheet I
will put two buttons (Next and Previous). The above buttons are used to
jump to next/previous worksheets. When the worksheet scrollbar is moved
up/down, the two buttons will move up/down as well.

Please give me some advices.

Thanks
Bon
 
Create a commandbar with the previous and next buttons, rather
than using button controls.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Would it be possible to create buttons for doing it? What commands or
methods I should use?

Thanks
Bon
 
Hi Bon,

This is probably bad advice.....

MyButton is from the forms toolbar, on Sheet1, and Excel named it
"Button 1".
The code is in the Sheet1 Module, is triggered when you change the
selection and moves MyButton so that it is sitting just to the left of
the new selection.
Could get annoying though, and would have to be moved out of the way
with a right click/drag to see the cell(s) it covers.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyButton As Shape
Set MyButton = Sheet1.Shapes("Button 1")
With MyButton
.Top = Target.Top
.Left = Target.Left - .Width
End With
End Sub
 
The suggested code make buttons move with mouse click to cell(s). But,
how can I make buttons move with scrollbar. It is like some graphics on
the web. When the user scroll down the page, the buttons move down as
well.

What methods or events I should use? Please give me some advices.

Thanks
Bon
Ken Johnson 寫é“:
 
Hi Bon,
Excel doesn't have a scroll event. Selection change was the closest I
could get to what you are after.

Changing: .Left = Target.Left - .Width to ...

.Left = Cells(1,ActiveWindow.ScrollColumn).Left

will keep the button on the left side of the screen.

I think a customised toolbar would be a better solution.

Ken Johnson
 
Back
Top