I have a listbox in excel 2002 that is loaded with the names of all the
sheets in the workbook to allow the user tp navigate quickly from sheet to
sheet. Dragging the mouse over each entry in the listbox works well. The
code to activate each sheet is in the Click event for the listbox.

Problem: when I added code to the listbox click event to preserve the
selected cells area of the first listbox, the list box no longer reliably
responds to dragging the mouse over it.

The Code:

Private Sub lstFreeRooms_Click()

' show the sheet clicked on and
' preserve the current selection

Dim currentSelectionAddress As String

currentSelectionAddress = Selection.Address

' activate the sheet selected in the listbox

Dim sheetname As String

sheetname = Me.lstFreeRooms.Value
Sheets(sheetname).Activate

' restore the current selection
ActiveSheet.Range(currentSelectionAddress).Select

End Sub

-------------------------------------------------

I have tried many work arounds. The only one that is somewhat satisfactory
is to put the code to preserve the current selection into the mouse down and
mouse up events.

Thanks for any assistance.

Graham,

I'm not completely clear on what you mean by "selected cells area of the
first listbox." Did you mean of your origianal worksheet? If so, then the
line ActiveSheet.Range(currentSelectionAddress).Select
won't work I think, because your telling it to select a cell in the
activesheet, which is no longer the original sheet because you activated the
one from the listbox.

Also, I'm not sure what you mean by "the list box no longer reliably
responds to dragging the mouse over it." Can you be more specific?

Finally, did you know that right-clicking on the sheet navigation arrows in
the bottom left corner of the Excel screen brings up a list of the
worksheets in your workbook (although with over 15 sheets you have an extra
click).

hth,

Doug

"Graham" wrote in message
...
I have a listbox in excel 2002 that is loaded with the names of all the
sheets in the workbook to allow the user tp navigate quickly from sheet to
sheet. Dragging the mouse over each entry in the listbox works well. The
code to activate each sheet is in the Click event for the listbox.

Problem: when I added code to the listbox click event to preserve the
selected cells area of the first listbox, the list box no longer reliably
responds to dragging the mouse over it.

The Code:

Private Sub lstFreeRooms_Click()

' show the sheet clicked on and
' preserve the current selection

Dim currentSelectionAddress As String

currentSelectionAddress = Selection.Address

' activate the sheet selected in the listbox

Dim sheetname As String

sheetname = Me.lstFreeRooms.Value
Sheets(sheetname).Activate

' restore the current selection
ActiveSheet.Range(currentSelectionAddress).Select

End Sub

-------------------------------------------------

I have tried many work arounds. The only one that is somewhat satisfactory
is to put the code to preserve the current selection into the mouse down
and
mouse up events.

Thanks for any assistance.

Graham,

Likewise not sure what you mean about the listbox responding. If the title
of your question refers to scrolling a listbox in Excel with a wheel mouse
then there is a solution involving a cheap 3rd party activex control on my
site at this address.

http://www.enhanceddatasystems.com/E...istScrolls.htm

Robin Hammond
www.enhanceddatasystems.com


"Doug Glancy" wrote in message
...
Graham,

I'm not completely clear on what you mean by "selected cells area of the
first listbox." Did you mean of your origianal worksheet? If so, then
the
line ActiveSheet.Range(currentSelectionAddress).Select
won't work I think, because your telling it to select a cell in the
activesheet, which is no longer the original sheet because you activated
the
one from the listbox.

Also, I'm not sure what you mean by "the list box no longer reliably
responds to dragging the mouse over it." Can you be more specific?

Finally, did you know that right-clicking on the sheet navigation arrows
in
the bottom left corner of the Excel screen brings up a list of the
worksheets in your workbook (although with over 15 sheets you have an
extra
click).

hth,

Doug

"Graham" wrote in message
...
I have a listbox in excel 2002 that is loaded with the names of all the
sheets in the workbook to allow the user tp navigate quickly from sheet
to
sheet. Dragging the mouse over each entry in the listbox works well. The
code to activate each sheet is in the Click event for the listbox.

Problem: when I added code to the listbox click event to preserve the
selected cells area of the first listbox, the list box no longer
reliably
responds to dragging the mouse over it.

The Code:

Private Sub lstFreeRooms_Click()

' show the sheet clicked on and
' preserve the current selection

Dim currentSelectionAddress As String

currentSelectionAddress = Selection.Address

' activate the sheet selected in the listbox

Dim sheetname As String

sheetname = Me.lstFreeRooms.Value
Sheets(sheetname).Activate

' restore the current selection
ActiveSheet.Range(currentSelectionAddress).Select

End Sub

-------------------------------------------------

I have tried many work arounds. The only one that is somewhat
satisfactory
is to put the code to preserve the current selection into the mouse down
and
mouse up events.

Thanks for any assistance.

I now have a fuller understanding of the problem and have come up wit a
clean solution.

For those interested:

The problem was that behind each listbox click event there was code that
(needlessly) refilled the listbox itself. Eliminating this code solved the
problem.

The problem resulted from the fact that the Workbook_SheetSelectionChange
event can be caused to fire not only by a user making a selection on the
current sheet, but also by the process of a new sheet activating where that
sheet has a different selection to the previously active sheet.
My small application helps my college to book rooms. It is customised for
the rooms at my college but could be modified with not too much trouble. It
is free for all to use and modify.
http://wn.com.au/alexj/Excel/RoomBooking.xls



Thank you Doug & Robin for your assistance.



Graham


"Graham" wrote in message
...
I have a listbox in excel 2002 that is loaded with the names of all the
sheets in the workbook to allow the user tp navigate quickly from sheet to
sheet. Dragging the mouse over each entry in the listbox works well. The
code to activate each sheet is in the Click event for the listbox.

Problem: when I added code to the listbox click event to preserve the
selected cells area of the first listbox, the list box no longer reliably
responds to dragging the mouse over it.

The Code:

Private Sub lstFreeRooms_Click()

' show the sheet clicked on and
' preserve the current selection

Dim currentSelectionAddress As String

currentSelectionAddress = Selection.Address

' activate the sheet selected in the listbox

Dim sheetname As String

sheetname = Me.lstFreeRooms.Value
Sheets(sheetname).Activate

' restore the current selection
ActiveSheet.Range(currentSelectionAddress).Select

End Sub

-------------------------------------------------

I have tried many work arounds. The only one that is somewhat satisfactory
is to put the code to preserve the current selection into the mouse down
and
mouse up events.

Thanks for any assistance.

Your file on the net is buggy, you might want to
check through before putting it up.

Heath

"Graham" wrote in message ...
I now have a fuller understanding of the problem and have come up wit a
clean solution.

For those interested:

The problem was that behind each listbox click event there was code that
(needlessly) refilled the listbox itself. Eliminating this code solved the
problem.

The problem resulted from the fact that the Workbook_SheetSelectionChange
event can be caused to fire not only by a user making a selection on the
current sheet, but also by the process of a new sheet activating where that
sheet has a different selection to the previously active sheet.
My small application helps my college to book rooms. It is customised for
the rooms at my college but could be modified with not too much trouble. It
is free for all to use and modify.
http://wn.com.au/alexj/Excel/RoomBooking.xls



Thank you Doug & Robin for your assistance.



Graham


"Graham" wrote in message
...
I have a listbox in excel 2002 that is loaded with the names of all the
sheets in the workbook to allow the user tp navigate quickly from sheet to
sheet. Dragging the mouse over each entry in the listbox works well. The
code to activate each sheet is in the Click event for the listbox.

Problem: when I added code to the listbox click event to preserve the
selected cells area of the first listbox, the list box no longer reliably
responds to dragging the mouse over it.

The Code:

Private Sub lstFreeRooms_Click()

' show the sheet clicked on and
' preserve the current selection

Dim currentSelectionAddress As String

currentSelectionAddress = Selection.Address

' activate the sheet selected in the listbox

Dim sheetname As String

sheetname = Me.lstFreeRooms.Value
Sheets(sheetname).Activate

' restore the current selection
ActiveSheet.Range(currentSelectionAddress).Select

End Sub

-------------------------------------------------

I have tried many work arounds. The only one that is somewhat satisfactory
is to put the code to preserve the current selection into the mouse down
and
mouse up events.

Thanks for any assistance.