Resetting ListBox1.ListIndex to -1 after user selection

[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

 

[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.

 

[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.

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.

 

[p45cal]

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

 

[Hans]

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

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.

 

[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.

 

[p45cal]

could you attach a version of your spreadsheet at codecage.com

 

[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?

 

[Hans]

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

I'll try to. Just need to walk the dog first because she's getting
nervous.

 

[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

 

[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

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.

 

[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

 

[Hans]

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

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.

 

[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

Thanks Anthony,

No, the listbox is on a userform.

 

[Leith Ross]

Hello Hans,

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

Sub ResetList()

Dim LB As Excel.ListBox

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

End Su

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[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

Thanks Anthony,

No, the listbox is on a userform.

 

[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/)

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

 

[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.

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.

 

[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

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.

 

[Hans]

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

Mishell

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?