Resetting ListBox1.ListIndex to -1 after user selection

H

Hans

Hi all,

I have a simple question:

I display a listbox and let a user select one line of the list which
is then stored in a cell on a worksheet. The problem is that next time
the list is displayed the last selected line is selected and cannot be
picked again. Is there a way to set the .ListIndex to -1 so nothing is
selected when the list is displayed?

The application is this:

I have a grid of cells for a calender month. If a user clicks on a
cell the Worksheet SelectionChange procedure is executed. There I chek
via Intersec whether the cell clicked is in the grid. If so I display
the form with the possible entries in a listbox. The user picks one
line, the valued is stored in the cell by the ListBox1_Clicked
procedure and the form is hidden (UserForm1.Hide)

If the user clicks another cell the same procedure is executed but he
can pick any line but the one he picked previously.

Anyhelp is greatly appreciated.

Thanks in advance
Hans
 
J

Joel

Can't you simply set the index to -1 at the end of the worksheet change
macro? the other choice is to add a control buttom to initiate the macro
rather then use the clicking of the list box or use the click function of the
list box to start the macro.
 
H

Hans

Can't you simply set the index to -1 at the end of the worksheet change
macro?  the other choice is to add a control buttom to initiate the macro
rather then use the clicking of the list box or use the click function ofthe
list box to start the macro.
Click to expand...

Thanks Joel for your quick reply.

I put the ListBox1.ListIndex = -1 command almost everywhere. :)

At the end of the Worksheet_SelectionChange procedure it says: Runtime
error 424 Object required. And at the end of the Listbox1.Click
procedure it just doesn't do anything.
 
H

Hans

Doesn't
ListBox1.ListIndex= -1
just before the hide statement work?
It does here in XL2003
Click to expand...

No, unfortunately it doesn't do anything visible.

I can reset the ListIndex if I put a button on the form that re-
initializes the userform. But that doesn't seem like the intend
solution. :)
 
J

Joel

I think you may need

activesheet.ListBox1.ListIndex = -1

Listbox on a worksheet is a member of the worksheet. If the code is some
place other than a worksheet change macro you may need to explicitly need to
specify the worksheet name. I'm surprized that the code isn't causing an
error in other places.
 
H

Hans

ActiveSheet.ListBox1.ListIndex = -1 produces this error: Run-time
Error 438 Object doesn't support this property or method

All examples in the help file refer to the userform itself and
anything on it. Outside the form I seem to get these error messages
which would mean the ListIndex property cannot be referenced from
outside the form?
 
M

Mishell

Put the ListBox1.ListIndex = -1 command in the MouseUp Event :

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)

ListBox1.ListIndex = -1

End Sub

Mishell
 
H

Hans

Put the ListBox1.ListIndex = -1 command in the MouseUp Event :

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)

 ListBox1.ListIndex = -1

End Sub

Mishell
Click to expand...

Thanks Mishell,

The result is the same as anywhere in the scope of the Userform: The
selection just doesn't go away.

I placed a button on the userform with the ListBox1.ListIndex = -1
command in it's code and that seems to be the only way it works.

It's a solution although one not to my satisfaction. :)
 
A

Anthony

Is the listbox on the worksheet or is it on a user form?

If on a work sheet, set the input cell reference and all you would do then
is clear the reference.

e.g A1 = input cell

User selects an itemfrom list

A1= the selected line number

To clear selected line clear A1

Regards

Anthony
 
H

Hans

could you attach a version of your spreadsheet at codecage.com?
Click to expand...

Sorry, didn't succeed in uploading the file. I think I need to
register first and it keeps teasing me with tens of error messages. So
I gave up.

But thanks for the offer. :)
 
H

Hans

Is the listbox on the worksheet or is it on a user form?

If on a work sheet, set the input cell reference and all you would do then
is clear the reference.

e.g A1 = input cell

User selects an itemfrom list

A1= the selected line number

To clear selected line clear A1

Regards

Anthony
Click to expand...
Thanks Anthony,

No, the listbox is on a userform.
 
A

Anthony

Private Sub CommandButton1_Click()

Me.ListBox1.ListIndex = -1

End Sub

I have sent you a workbook.

This is code attached to a button on the userform.

Regards

Anthony

Is the listbox on the worksheet or is it on a user form?

If on a work sheet, set the input cell reference and all you would do then
is clear the reference.

e.g A1 = input cell

User selects an itemfrom list

A1= the selected line number

To clear selected line clear A1

Regards

Anthony
Click to expand...
Thanks Anthony,

No, the listbox is on a userform.
 
H

Hans

Hello Hans,

If your ListBox is a Forms type, you have to set the ListIndex property
to 0. Here is macro to reset the first ListBox on the ActiveSheet. This
would be placed in a standard VBA module.

Sub ResetList()

Dim LB As Excel.ListBox

Set LB = ActiveSheet.ListBoxes(1)
LB.ListIndex = 0

End Sub

--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
Click to expand...

Hello Leith,

Thanks you for your proposal. Here is what I tried:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set r1 = Range("b7:h7")
Set r2 = Range("b9:h9")
Set r3 = Range("b11:h11")
Set r4 = Range("b13:h13")
Set r5 = Range("b15:h15")
Set r6 = Range("b17:h17")

Set r11 = Range("b26:h26")
Set r12 = Range("b28:h28")
Set r13 = Range("b30:h30")
Set r14 = Range("b32:h32")
Set r15 = Range("b34:h34")
Set r16 = Range("b36:h36")

If Intersect(Target, r1) Is Nothing And _
Intersect(Target, r2) Is Nothing And _
Intersect(Target, r3) Is Nothing And _
Intersect(Target, r4) Is Nothing And _
Intersect(Target, r5) Is Nothing And _
Intersect(Target, r6) Is Nothing And _
Intersect(Target, r11) Is Nothing And _
Intersect(Target, r12) Is Nothing And _
Intersect(Target, r13) Is Nothing And _
Intersect(Target, r14) Is Nothing And _
Intersect(Target, r15) Is Nothing And _
Intersect(Target, r16) Is Nothing Then
Exit Sub
End If

If Cells(Target.Row - 1, Target.Column) = "" Or _
Cells(Target.Row - 1, Target.Column) = "-" Then Exit Sub

UserForm1.Show
Cells(1, 9).Select
ResetList

End Sub

Sub ResetList()

Dim LB As Excel.ListBox
Set LB = ActiveSheet.ListBoxes(1)
LB.ListIndex = 0

End Sub

Which produces this:

Run-time error 1004 Unable to get the ListBoxes property of the
worksheet class
 
H

Hans

Private Sub CommandButton1_Click()

Me.ListBox1.ListIndex = -1

End Sub

I have sent you a workbook.

This is code attached to a button on the userform.

Regards

Anthony









Thanks Anthony,

No, the listbox is on a userform.
Click to expand...

Hello Anthony,

You are right. That works if you put it in the code of a command
button on the userform. It is a solution but not really as it should
work I believe. :)
 
M

Mishell

It should work in the normal Run Mode, although not in the Step by Step
Debug Mode.

Mishell

Put the ListBox1.ListIndex = -1 command in the MouseUp Event :

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)

ListBox1.ListIndex = -1

End Sub

Mishell
Click to expand...

Thanks Mishell,

The result is the same as anywhere in the scope of the Userform: The
selection just doesn't go away.

I placed a button on the userform with the ListBox1.ListIndex = -1
command in it's code and that seems to be the only way it works.

It's a solution although one not to my satisfaction. :)
 
H

Hans

It should work in the normal Run Mode, although not in the Step by Step
Debug Mode.

Mishell
Click to expand...

Oh no, I'm not debugging it. It's in run mode. It seems that it
doesn't work anywhere within the scope of the userform except on a
command button. Strange, isn't it?
 
A

Anthony

Just move the code into the userform activate option

Regards

Anthony
Hello Hans,

If your ListBox is a Forms type, you have to set the ListIndex property
to 0. Here is macro to reset the first ListBox on the ActiveSheet. This
would be placed in a standard VBA module.

Sub ResetList()

Dim LB As Excel.ListBox

Set LB = ActiveSheet.ListBoxes(1)
LB.ListIndex = 0

End Sub

--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
Click to expand...

Hello Leith,

Thanks you for your proposal. Here is what I tried:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set r1 = Range("b7:h7")
Set r2 = Range("b9:h9")
Set r3 = Range("b11:h11")
Set r4 = Range("b13:h13")
Set r5 = Range("b15:h15")
Set r6 = Range("b17:h17")

Set r11 = Range("b26:h26")
Set r12 = Range("b28:h28")
Set r13 = Range("b30:h30")
Set r14 = Range("b32:h32")
Set r15 = Range("b34:h34")
Set r16 = Range("b36:h36")

If Intersect(Target, r1) Is Nothing And _
Intersect(Target, r2) Is Nothing And _
Intersect(Target, r3) Is Nothing And _
Intersect(Target, r4) Is Nothing And _
Intersect(Target, r5) Is Nothing And _
Intersect(Target, r6) Is Nothing And _
Intersect(Target, r11) Is Nothing And _
Intersect(Target, r12) Is Nothing And _
Intersect(Target, r13) Is Nothing And _
Intersect(Target, r14) Is Nothing And _
Intersect(Target, r15) Is Nothing And _
Intersect(Target, r16) Is Nothing Then
Exit Sub
End If

If Cells(Target.Row - 1, Target.Column) = "" Or _
Cells(Target.Row - 1, Target.Column) = "-" Then Exit Sub

UserForm1.Show
Cells(1, 9).Select
ResetList

End Sub

Sub ResetList()

Dim LB As Excel.ListBox
Set LB = ActiveSheet.ListBoxes(1)
LB.ListIndex = 0

End Sub

Which produces this:

Run-time error 1004 Unable to get the ListBoxes property of the
worksheet class
 
A

Anthony

Just move code into the userform activate. I have sen tyou workbook

Regards


Anthony
Private Sub CommandButton1_Click()

Me.ListBox1.ListIndex = -1

End Sub

I have sent you a workbook.

This is code attached to a button on the userform.

Regards

Anthony









Thanks Anthony,

No, the listbox is on a userform.
Click to expand...

Hello Anthony,

You are right. That works if you put it in the code of a command
button on the userform. It is a solution but not really as it should
work I believe. :)
 
H

Hans

Just move code into the userform activate. I have sen tyou workbook

Regards
Click to expand...

Hello Anthony,

Bingo. That is the solution!

For the records:

Listbox1.ListIndex = -1 in the UserForm_Activate procedure resets the
user selection everytime the listbox is displayed.

I want to expressly thank all of you for participating in the hunt for
the correct solution. Every contribution is greatly appreciated.

Very best regards

Hans
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Show a value for Listbox1.Selected(i) 2
ListBox and ListIndex 2
nothing selected in listbox, but listindex <> -1 2
trouble with a listindex value... 2
listbox listindex problem 2
combo box and .listindex 3
remove selection from listbox. 4
UserForm past data 7

Top