How to make created button move with scrollbar?

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


"Bon" <bonnie.tangyn@gmail.com> wrote in message
news:1134380535.713393.205950@g43g2000cwa.googlegroups.com...
> 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
>

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 =E5=AF=AB=E9=81=93=EF=BC=9A

> 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 =3D Sheet1.Shapes("Button 1")
> With MyButton
> .Top =3D Target.Top
> .Left =3D Target.Left - .Width
> End With
> End Sub

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

Thanks Ken

Bon
Ken Johnson =E5=AF=AB=E9=81=93=EF=BC=9A

> Hi Bon,
> Excel doesn't have a scroll event. Selection change was the closest I
> could get to what you are after.
>
> Changing: .Left =3D Target.Left - .Width to ...
>
> .Left =3D 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.
>=20
> Ken Johnson