Add an item to a listbox and select it

[Emperor]

Hi all,

I think this can't be hard but I can't seem to fix it...

If ListBox5.Value = "Manually" Then
    omschrijving = InputBox(Prompt:="Fill the value pls", _
          Title:="Geef een omschrijving van de behandeling", Default:="Value")
    'add new item on top
    Me.ListBox5.AddItem omschrijving, 0
    
    'Select inserted value...

'What goes here?!?

End if

Thanks in advance!

Mathijs.

 

[Andrew Poulsom]

Try:

Me.ListBox5.ListIndex = 0

 

[Emperor]

Hello Andrew,

Thanks for your reply...
The weird thing is, when I do this the by you sugested way, I get another inputwindow (after the first) and the new item doest get selected.

Have you got any other ideas?

Mathijs.

 

[Andrew Poulsom]

If your code is in the ListBox's Click event procedure it will fire again when you programatically change the selection. You need to temporarily disable the event. This shows one way of doing it:

http://www.cpearson.com/Excel/SuppressChangeInForms.htm

 

[Emperor]

I have added the tags for disabeling the event, still no changes...

This is what I have done;
1. added "Public EnableEvents As Boolean" at the top
2. added "Me.EnableEvents = True" in Private Sub UserForm_Initialize()
3. added tags arround the change event:

If ListBox5.Value = "Manually" Then
    omschrijving = InputBox(Prompt:="Fill the value pls", _
          Title:="Geef een omschrijving van de behandeling", Default:="Value")
    'add new item on top
    Me.ListBox5.AddItem omschrijving, 0
    
    'Select inserted value...
    Me.EnableEvents = False
    Me.ListBox5.ListIndex = 0
    Me.EnableEvents = True
End if

Is this correct? Or am I interpretering the code wrong?

Greetings Mathijs.

 

[Andrew Poulsom]

Did you remember to put this at the beginning of your procedure?

If Me.EnableEvents = False Then Exit Sub

 

[Emperor]

No I did not, and no (sadly enough) this doesn't help.
My original code, without translation etc;

Private Sub ListBox5_Click()
If Me.EnableEvents = False Then Exit Sub

Dim omschrijving
If ListBox5.Value = "Handmatig invoeren" Then
    Me.EnableEvents = False
    omschrijving = InputBox(Prompt:="Geef een omschrijving van de behandeling", _
          Title:="Geef een omschrijving van de behandeling", Default:="Verf")
    'Nieuw ingevoerde behandeling bovenaanzetten
    Me.ListBox5.AddItem omschrijving, 0
    
    'Nieuw ingevoerde waarde selecteren
    Me.ListBox5.ListIndex = 0
    
    Me.EnableEvents = True
        
End If
End sub

I don't get what I'm doing wrong!

Mathijs.


Edit: Could it be I'm still at Excel 2003?

 

[Andrew Poulsom]

Try using a CommandButton instead of the ListBox's Click event procedure. In tests I couldn't programatically change the selection while the ListBox had the focus.

Private Sub CommandButton1_Click()
    Dim omschrijving
    With ListBox5
        If .Value = "Handmatig invoeren" Then
            omschrijving = InputBox(Prompt:="Geef een omschrijving van de behandeling", _
                Title:="Geef een omschrijving van de behandeling", Default:="Verf")
'           Nieuw ingevoerde behandeling bovenaanzetten
            .AddItem omschrijving, 0
'           Nieuw ingevoerde waarde selecteren
            .ListIndex = 0
        End If
    End With
End Sub

 

[Emperor]

Andrew,

Thanks for your help, this is, I think, the only possibility.
I tried changing the focus on another listbox in the start of the event but this also doesn't help, i'll change the form so a button is integrated.

Mathijs.