Select the next item

[Tendresse]

I have a listbox (created from the Control toolbar) in Sheet1. The list has a
number of items and the user can select only one item at a time. How can i
programmatically move the user's selection to the next item in the list?
So if the user originally selected item number 2 and it's highlighted in
blue, how can i move the selection to item number 3 using VBA?
Excel 2003
cheers, tendresse

 

[OssieMac]

Hi Tendresse,

Do you mean that if the user selects item 2 then you want item 2 to become
item 3 in the list like the following

Example Original list.
AAA
BBB
CCC
DDD
EEE

If the user selects BBB then you want the list to look like this
AAA
CCC
BBB
DDD
EEE

If my assumption is correct then need to know how the list is populated in
the list box. Is it a range in the worksheet or what?

 

[Tendresse]

Hi OssieMac,
Sorry if i wasn't very clear in my previous post.
The listbox in sheet1 contains some items, for example:
aaa
bbb
ccc
When the user selects one of them (say aaa), a click event takes place and a
userform is displayed containing information about item aaa. This userform
has a commandbutton with caption "View Next". When clicked, i want to display
a new userform that displays information about the next item which, in this
case, is bbb.

What i'm trying to do is to save the user the hassle of closing the current
userform, go back to sheet one and select a new item from the list. By having
"View Next" and "View Previous" in the userform itself, the user can flick
between the items without having to go back and forth to the original listbox.

So in order to populate the new userform with the new information about item
bbb, i need this item (bbb) to be selected so that it's reflected in the
Listbox's linkedCell.
I hope this makes more sense.
cheers
tendresse

 

[ND Pard]

Store the value selected via the list box's ControlSource.

Then use the Match function to determine the relative position of the
selected value in the list range.

Then you can use the Index function to return the next relative value to use
to view the next item.

Good Luck.

 

[OssieMac]

Hi again Tendresse,

I understand now. Try the following for both Next and Previous . Note the
comments in the code re what you want to do when you get to the first or last
value.

Private Sub CommandButton1_Click()
'Sets list box to to next

Dim varTemp As Variant

varTemp = Sheets("Sheet1").ListBox1.ListIndex

'Note: ListBox Index starts at zero
If varTemp = Sheets("Sheet1").ListBox1.ListCount - 1 Then
MsgBox "At end of list"
'Exit Sub
'or the following to roll around to first value
Sheets("Sheet1").ListBox1.ListIndex = 0
Else
Sheets("Sheet1").ListBox1.ListIndex = varTemp + 1
End If

End Sub

Private Sub CommandButton2_Click()
'Sets list box to to previous

Dim varTemp As Variant

varTemp = Sheets("Sheet1").ListBox1.ListIndex

'Note: ListBox Index starts at zero
If varTemp = 0 Then
MsgBox "At start of list"
'Exit Sub
'or the following to roll around to last value
Sheets("Sheet1").ListBox1.ListIndex = _
Sheets("Sheet1").ListBox1.ListCount - 1
Else
Sheets("Sheet1").ListBox1.ListIndex = varTemp - 1
End If

End Sub

 

[OssieMac]

Late afterthought Tendresse,

Your comment: "What i'm trying to do is to save the user the hassle of
closing the current userform, go back to sheet one and select a new item from
the list"

If you set theForms property ShowModal to False then you can go between the
form and the worksheet without closing the form.

To set forms properties, right click anywhere on the blank part of the form
and select properties.

 

[Tendresse]

OssieMac and ND Pard,
Many thanks for your replys. You have been of tremendous help. If i can be a
little bit greedy and ask for more assistance please. I'm not sure if you saw
my previous post with the title "ListFillRange". It's also about this
ListBox. If you can shed any light on this one for me, this would be great:

In Sheet1, i have some checkboxes for users to select from. Then there is a
commandbutton with caption 'Search'. The code behind this button searches for
the criteria selected by the users in the checkboxes and puts the results in
Sheet2 in 'MyRange'. The Listbox in sheet1 is then supposed to have its
ListFillRange property equal to MyRange.
All this goes well .. except that the items in the list don't get displayed
inside the listbox straight away. I found out that i first have to give the
screen a little 'tingle'. If i simply scroll down the screen and up again,
the list is there. Or if i minimize the window then maximize it, i can then
see the search results listed in the list box. It's as if the screen is not
refreshing its pixels unless it's 'tickled'!! I also noticed if i had another
application active on the screen in front of my sreadsheet, then i close this
other application, i can still visually see part of it within the listbox. Do
you know what i mean? Do you think this is a hardware problem related to the
capacity of the screen to refresh? or is it a programmatic problem with the
listbox? is there anyway i can go around this problem in my code. Is there
such a thing as: Screen.refresh?
PS i tried the spreadsheet in 3 other computers (fairly new ones) and the
same thing happens.
Thanks again for all your help.
Tendresse

 

[OssieMac]

Hi again Tendresse,

I can't emulate the problem but some things you might try. I have no idea
whether any will help.

Turn ScreenUpdating off at the start of the code.
Application.ScreenUpdating = False

and turn it back on at the end of the code.
Application.ScreenUpdating = True


Select another sheet and then back to the required sheet.
Sheets("Sheet2").Select
Sheets("Sheet1").Select

Select another sheet and then back to the required sheet then select a cell
on the required sheet.
Sheets("Sheet2").Select
Sheets("Sheet1").Select
Range("A1").Select


Other things to check is Ctrl/Alt/Del and see how many Excel processes are
running. You can get multiple ones if you don't set object variables to
nothing after you are finished with them.

Example

With Sheets("Sheet2")
Set MyRange = .Range("A2:A1000")
End With

'when finished with MyRange then
Set MyRange = Nothing

 

[Tendresse]

OssieMac,
You are a life saver. Thanks heaps .. the ScreenUpdating statement did the
trick.

Now you got my attention to something else: 'Setting Object Variables to
Nothing".
I'm still feeling my way around this issue. What are exactly Object
Variables? Are all variables 'Object Variables'? if not, how do i know which
ones have to be set to nothing and which ones don't?
For example, i use the Dim statement a lot. Does everything that comes after
'Dim' considered an Object Variable that has to be set to nothing?
Thanks again ..
tendresse