Resetting ListBox1.ListIndex to -1 after user selection

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

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

 

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

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.

 

[Hans]

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

Regards

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

 

[Anthony]

Your welcome.

Regards

Anthony

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

Regards

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