listbox - How to reset the values

[Guest]

Hello Gurus - I have written a macro on the back of a listbox button that
allows the user to select a worksheet to update. However, once a selection is
made the list box retains this choice so the user cannot select the same
choice again. How do I reset the listbox so none of the options are selected?

Thank you in advance!

 

[NickHK]

Set its .ListIndex to -1

NickHK

 

[Guest]

Nick

I added this line of script to the end of my macro:

ListBox1.ListIndex = -1

and unfortunately it does not reset the listbox. Can you clarify your answer
a little please?

 

[Tom Ogilvy]

Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ListBox1.ListIndex = -1
End Sub

 

[Guest]

Great, perfect super, Thanks Tom! (and Nick HK)

Private Sub ListBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
ListBox1.ListIndex = -1
End Sub

 

[Guest]

I have used the following:
unload me ' unload userform
UserForm1.Show ' reload same userform, change "UserForm1" with your userform
name

Are there any reasons this is not good to use?

--
Regards

Rick
XP Pro
Office 2007

 

[DonJ_Austin]

I found this answer to reseting the value/position of a List Box, but I think
my requirement is somewhat different. BTW, I am trying to write a 'nimble'
application to run in both Excel 11 and Excel 12, so lewt me know if there
are distinctions in your recommendation.

I am building a record using several entry firelds and several List Boxes.
I am *not* triggering the code on the List Box selection, but have a
CommandButton the user clicks when the entire record is ready to save. After
copying the record to a pseudo-database, I want to clear the entry fields and
reset the List Boxes to their original blank value & position. I did put a
statement for each of the List Boxes in the CommandButton routine of the form:
ListBoxA.ListIndex = -1
but that did not work.

Thanks in advance for the help.

Cheers. dj

 

[Kenneth Hobson]

ListBoxA.ListIndex = -1
is kind of odd. Normally, one does:
ListBox1.ListIndex = -1

Maybe something else is going on. Post a sample xls at a shared site like
4shared.com and we will check it if you like.

 

[DonJ_Austin]

"ListBoxA" was arbitrarily used in the posting. I never leave controls with
the default name (i.e. ListBoxn) but give them a meaningful name. In this
case, the actual List Box names include "lb_Facility", lb_CaseManager", etc.
Maybe I am missing something, but I don't see how that has any impact on my
issue. Does it ?

Cheers. dj

 

[Kenneth Hobson]

I don't see how the selected item would not be reset to nothing then.

If I want to clear a listbox's list I do: ListBox1.Clear

If you are using a RowSource, you need to reset it rather than Clear.
e.g.
Private Sub CommandButton1_Click()
ListBox1.RowSource = ""
End Sub

 

[DonJ_Austin]

Not using RowSource: Extract follows:
************************************************************
Private Sub SaveNewRecord_Click()

Dim RowStart As Integer, RowIncr As Integer

RowStart = Sheets("workdata").Range("RefRecordBase")

RowStep = Sheets("workdata").Range("RecordCounter")

Sheets("workdata").Range("BuildRecord").Copy
Sheets("Referrals").Activate
Sheets("Referrals").Cells(RowStart + RowStep, 3).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' *** indicator of non-unique patient name
Sheets("workdata").Range("F19").ClearContents

' *** clear the cells linked from listboxes on referrals
Sheets("workdata").Activate
Sheets("workdata").Range("D22").ClearContents
Sheets("workdata").Range("E22").ClearContents
Sheets("workdata").Range("H22").ClearContents
Sheets("workdata").Range("J22").ClearContents
Sheets("workdata").Range("K22").ClearContents
Sheets("workdata").Range("M22").ClearContents
Sheets("workdata").Range("N22").ClearContents
Sheets("workdata").Range("O22").ClearContents
Sheets("workdata").Range("P22").ClearContents

' *** reset List Boxes to first position (space)
Sheets("Referrals").Activate
lb_New_RefBy.ListIndex = -1
' lb_New_RefName.ListIndex = -1
' lb_New_RefCaseType.ListIndex = -1
' lb_New_Dispo.ListIndex = -1
' lb_New_RFD.ListIndex = -1
' lb_New_AdmFrFacility.ListIndex = -1
' lb_New_TrilliumFacility.ListIndex = -1
' lb_New_AdmCaseType.ListIndex = -1
' lb_New_MDAssigned.ListIndex = -1

' *** clear the cells copied from entry fields referrals
Sheets("Referrals").Activate
Sheets("Referrals").Range("C6").ClearContents
Sheets("Referrals").Range("G6").ClearContents
Sheets("Referrals").Range("H6").ClearContents
Sheets("Referrals").Range("J6").ClearContents
Sheets("Referrals").Range("M6").ClearContents

Sheets("workdata").Range("RecordCounter").Value = _
Sheets("workdata").Range("RecordCounter").Value + 1


End Sub
************************************************************

Note: Names of List Boxes have been triple-checked. I have commented out
all but one of the statements that is intended to reset the Lict Box as you
had suggested.

Cheers. dj

 

[Kenneth Hobson]

As I understand it, you want the listbox items deleted? I see no
ListBox1.Clear as I explained.

If you are not using RowSource to fill the list, then you must be using
AddItem or List. Not sure why you are not using RowSource but then I don't
what all you are doing.

Sometimes, one needs repaint to refresh the userform: Userform1.Repaint

 

[DonJ_Austin]

I am populating the List Box items from cells (which I modify while the
fields in the record are being built) by using the ListFillRange property.

No, I do not want the List Box deleted. I just want it reset to appear with
the <blank> item showing, which is the first cell in the ListFillRange.

And, I am not using a UserForm, but have the various entry fields and List
Boxes on a regular worksheet which shows other data. When they user finishes
enter data in several fields and making slections in several List Boxes, she
clicks the "Save" CommandButton. It is in the Sub for that button that I
want to reset the List Boxes after copying & storing the built record.

Clearer ? However, the lb_New_RefBy.ListIndex = -1 does not reset
that List Box.

Thanks for the help.

Cheers. dj

 

[Kenneth Hobson]

-1 sets the index to no entry. 0 is the first indexed item so:,
Sheet1.ListBox1.Index = 0, would seem to be what you want.

 

[DonJ_Austin]

Yes ! Thank you. After struggling with some issue like this, the answer
always looks so obvious.

In the spirit of giving back to anyone else following this thread, the
..ListIndex = 0 does place the first item in the List Box, though it is
highlighted. Following the .ListIndex = 0 with .Value = "" deselects that
item and completely returns the List Box to the initial state.

Kenneth, somewhere I picked up the practice of always having an empty cell
as the first in the list of items. Do you recommend against that ?

Cheers. dj

 

[Kenneth Hobson]

It depends on your needs. If it works for you, I say go for it.

I have not found a need for such. I have been known to end a list with
something like NA. A similar concept can be used for the first item. A
combobox are may useful for that kind of scenario by using -1 for its index.

 

[DonJ_Austin]

Thanks.