Want listbox to move when scrolling down spreadsheet

[kdunnSBLI]

I have a calendar control on my spreadsheet which moves whenever I scroll
down and I want my listboxes to behave the same way, but they're not. The
Format Control property for the listboxes is set to "Move but don't size
with cells", so I don't know what else to do.
I'm using Excel 2002. Any help with this would be greatly appreciated.
Thanks

Kevin

[Dave Peterson]

Do you have any code that moves that calendar control when you scroll/change
selection?

If you do, maybe you can use very similar code to keep track of the listboxes.

An easier alternative...

Move the listboxes to Row 1 (and make row 1 taller).

Then with row 1 still visible, select A2 and click on Window|freeze panes.



kdunnSBLI wrote:
>
> I have a calendar control on my spreadsheet which moves whenever I scroll
> down and I want my listboxes to behave the same way, but they're not. The
> Format Control property for the listboxes is set to "Move but don't size
> with cells", so I don't know what else to do.
> I'm using Excel 2002. Any help with this would be greatly appreciated.
> Thanks
>
> Kevin

--

Dave Peterson

[kdunnSBLI]

No, I didn't add any code to make the Calendar move. I tried freezing the
pane like you suggested but that didn't work. Thanks anyway. I still think
that there is a way to have a control move automatically whenever you scroll
thru a spreadsheet. I'll keep looking and asking. Thanks Dave.

Kevin


"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42BA00E4.531A6B89@netscapeXSPAM.com...
> Do you have any code that moves that calendar control when you
scroll/change
> selection?
>
> If you do, maybe you can use very similar code to keep track of the
listboxes.
>
> An easier alternative...
>
> Move the listboxes to Row 1 (and make row 1 taller).
>
> Then with row 1 still visible, select A2 and click on Window|freeze panes.
>
>
>
> kdunnSBLI wrote:
> >
> > I have a calendar control on my spreadsheet which moves whenever I
scroll
> > down and I want my listboxes to behave the same way, but they're not.
The
> > Format Control property for the listboxes is set to "Move but don't
size
> > with cells", so I don't know what else to do.
> > I'm using Excel 2002. Any help with this would be greatly appreciated.
> > Thanks
> >
> > Kevin
>
> --
>
> Dave Peterson

[Dave Peterson]

You can do it with something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("List box 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

(change the name of your listbox to match)

Rightclick on the worksheet that should have this behavior. Select view code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user moves the
screen using the scroll bars, then it won't show up until they select a range.

If you have multiple listboxes, you'll have to decide what column to use for
each.




kdunnSBLI wrote:
>
> No, I didn't add any code to make the Calendar move. I tried freezing the
> pane like you suggested but that didn't work. Thanks anyway. I still think
> that there is a way to have a control move automatically whenever you scroll
> thru a spreadsheet. I'll keep looking and asking. Thanks Dave.
>
> Kevin
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:42BA00E4.531A6B89@netscapeXSPAM.com...
> > Do you have any code that moves that calendar control when you
> scroll/change
> > selection?
> >
> > If you do, maybe you can use very similar code to keep track of the
> listboxes.
> >
> > An easier alternative...
> >
> > Move the listboxes to Row 1 (and make row 1 taller).
> >
> > Then with row 1 still visible, select A2 and click on Window|freeze panes.
> >
> >
> >
> > kdunnSBLI wrote:
> > >
> > > I have a calendar control on my spreadsheet which moves whenever I
> scroll
> > > down and I want my listboxes to behave the same way, but they're not.
> The
> > > Format Control property for the listboxes is set to "Move but don't
> size
> > > with cells", so I don't know what else to do.
> > > I'm using Excel 2002. Any help with this would be greatly appreciated.
> > > Thanks
> > >
> > > Kevin
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson