Userform Listbox

[hrayani]

Hello Friends,

I have a userform in which I have a list box (named as supplier_list)

The userform is loaded when cell C2 is double clicked... see below screen shot

1) Now the problem is that I am unable to select a supplier if it's already been selected before. Like if cell C2 already has Gul Ahmed in there then I am unable to select Gul Ahmed again from the list and eventually I have to close the list box pressing the X key at the top

2) Another problem is that when the next time user form is loaded then it does not show the selected item Gul Ahmed in the list.... See screen shot

3) Also If possible I would want the userform to unload if escape key is pressed

Any help would be appreciated

Regards,

Humayun

 

[GWteB]

Without access to the code behind your UserForm, it becomes difficult to assess your issue.
The desired behavior of the Esc key can be enforced as follows:
1. place a command button on your userform;
2. change the Cancel property from False to True;

3. create a Click event procedure ...

Private Sub CommandButton1_Click()

    Unload Me

End Sub

4. include this in the UserForm_Initialize event procedure ...

Private Sub UserForm_Initialize()
    
    Me.CommandButton1.Left = Me.Width

End Sub

 

[hrayani]

Hi GWteB,

Thanks for the reply,

Issue not 3 solved

And issue not 2 also solved with below line added in the userfom initialize event.
Me.supplier_list.Value = Range("C2").Value

Now only issue # 1 remains there that I am unable to select a supplier if it's already been selected before. Like if cell C2 already has Gul Ahmed in there then I am unable to select Gul Ahmed again from the list and eventually I have to close the list box pressing the X key at the top.

Also one more thing I noticed that as soon as I press the down arrow key then the userform is closed. Where as it should move the selection down in the list and stay there until I press enter to select... It just selects the supplier from the list and closes.... Any Idea ???

Without access to the code behind your UserForm, it becomes difficult to assess your issue

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub supplier_list_Click()
supplier.Hide
End Sub
Private Sub UserForm_Initialize()

Me.supplier_list.Value = Range("C2").Value

With Me
    .StartUpPosition = 0
   .Top = Range("C2").Top + 173.5
    .Left = Range("B3").Left + 61.5
End With
End Sub

 

[hrayani]

I guess I have figured out what went wrong with the code.. It is actually the list box click even which is causing the list to close as soon as the drop down arrow key is pressed. When I remove this then the list remains there when the drop down arrow key is pressed but how do I select the supplier from the list.

I want a single click or enter key to select the supplier and close the userform

Any idea ??

 

[GWteB]

In the UserForm_Initialize event procedure I added a line that set focus to the ListBox.
The ListBox_Change event procedure is used to update cell C2.
The ListBox_DoubleClick event procedure is used to both make a selection and close the UserForm.

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub supplier_list_Change()
    Range("C2").Value = Me.supplier_list.Value
End Sub

Private Sub supplier_list_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Unload Me
End Sub

Private Sub UserForm_Initialize()

    With Me
        .StartUpPosition = 0
        .Top = Range("C2").Top + 173.5
        .Left = Range("B3").Left + 61.5
    End With

    Me.CommandButton1.Left = Me.Width

    Me.supplier_list.Value = Range("C2").Value
    Me.supplier_list.SetFocus

End Sub

 

[hrayani]

Hi GWteB,

Thanks for the reply.... But why can not we select it with single click... ??

 

[GWteB]

You are welcome.

But why can not we select it with single click... ??

We cannot due to the fact a single change of the listbox or any key press will fire the Click event.
Within the Initialize procedure we "synchronize" the selection of the listbox with this line of code ...

    Me.supplier_list.Value = Range("C2").Value

... which fires the Change event, which fires the Click event.
Because of that, your userform will never be shown on screen when there is an Unload Me within the Click event procedure. Feel free to try.

 

[hrayani]

You are welcome.



We cannot due to the fact a single change of the listbox or any key press will fire the Click event.
Within the Initialize procedure we "synchronize" the selection of the listbox with this line of code ...

    Me.supplier_list.Value = Range("C2").Value

... which fires the Change event, which fires the Click event.
Because of that, your userform will never be shown on screen when there is an Unload Me within the Click event procedure. Feel free to try.

Hi GWteB,

Sorry for coming back late...

Yes you are right what you said about single click selection... I tried that...

Many thanks for the help

Regards,

Humayun

 

[GWteB]

Sorry for coming back late...

Late is a relative concept (as far a dilophosaurus is concerned we both are late ? )
Glad to help and thanks for the feedback.

 

[hrayani]

Hi GWteB,

I have just noticed some weird behaviour in my file..

1) well the file has many sheets in it. But sometimes worksheet activation does not get triggered for a specific sheet having no userforms etc
2) If the VBA editor is open then it does not get triggered at all for that specific sheet.. having no userforms etc
3) if the VBA editor is open and if another file of mine is open then I am unable to type any thing in those cells in the other open file (normal xlsx file with no macros) whose references are given in the userform initialize even and userform list change events.

All errors are gone as soon as the userforms are removed from the file...

Can you pls shed some light on this as why this is happening ??

Regards,

Humayun