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

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" wrote:

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

On Aug 22, 12:57*pm, Joel wrote:
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.



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.

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127662

On Aug 22, 2:38*pm, p45cal wrote:
Doesn't
ListBox1.ListIndex= -1
just before the hide statement work?
It does here in XL2003

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=127662

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. :-)

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.

"Hans" wrote:

On Aug 22, 12:57 pm, Joel wrote:
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.



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.

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127662

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.

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?

On Aug 22, 5:22*pm, p45cal wrote:
could you attach a version of your spreadsheet at codecage.com?

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=127662

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

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" wrote in message
...
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

On Aug 22, 5:39*pm, "Mishell" wrote:
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. :-)

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" wrote in message
...
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

On Aug 22, 5:22*pm, p45cal wrote:
could you attach a version of your spreadsheet at codecage.com?

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile:http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=127662

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. :-)

On Aug 22, 6:48*pm, "Anthony" wrote:
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.

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/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127662

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

"Hans" wrote in message
...
On Aug 22, 6:48 pm, "Anthony" wrote:
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.

On Aug 22, 7:00*pm, Leith Ross
wrote:
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/)
------------------------------------------------------------------------
Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=127662

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

On Aug 22, 7:04*pm, "Anthony" wrote:
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

"Hans" wrote in message

...
On Aug 22, 6:48 pm, "Anthony" wrote:



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.

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. :-)

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

Mishell

"Hans" wrote in message
...
On Aug 22, 5:39 pm, "Mishell" wrote:
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. :-)

On Aug 22, 8:01*pm, "Mishell" wrote:
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?

Just move the code into the userform activate option

Regards

Anthony
"Hans" wrote in message
...
On Aug 22, 7:00 pm, Leith Ross
wrote:
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/)
------------------------------------------------------------------------
Leith Ross's
Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=127662

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

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

Regards


Anthony
"Hans" wrote in message
...
On Aug 22, 7:04 pm, "Anthony" wrote:
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

"Hans" wrote in message

...
On Aug 22, 6:48 pm, "Anthony" wrote:



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.

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. :-)

On Aug 22, 9:12*pm, "Anthony" wrote:
Just move code into the userform activate. I have sen tyou workbook

Regards

Anthony"Hans" wrote in message


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

Your welcome.

Regards

Anthony

"Hans" wrote in message
...
On Aug 22, 9:12 pm, "Anthony" wrote:
Just move code into the userform activate. I have sen tyou workbook

Regards

Anthony"Hans" wrote in message


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