UserForm Advice Request 2

[MikeCouling]

I'm just getting to grips with VBA and am trying to set up a User Form (I have been using John Walkenbach's Excel VBA for Dummies - highly recommend it for newbies)
It's a questionnaire. I have put the possible answers for one question in a ListBox. The last option in the ListBox is "Other"; if the user selects that, I want to unlock a text box so they can write their own answer. I have tried the following :-
Private Sub ListBox1_Click()
If ListBox1.Selected(13) Then
TextBox19.Locked = False
TextBox19.BackColor = &HFFFFFF
End If
End Sub

but the text box remains locked and greyed. I guess I need to associate an event with the selection of "Other" but am otherwise stuck. Can anyone help me out? Thanks.

Mike

 

[ktwclark]

Hi Mike

Try this...

Private Sub ListBox1_AfterUpdate()
'Use AfterUpdate event to detect changes in listbox after the selection has been made
'The Click event detects change in listbox during the selection process
'ListBox1.ListIndex is position in ListBox list of the selected item, positions in list start at 0
'ListBox1.ListCount is number of items in Listbox list, items start numbering at 1
'If 3 items in listbox then ListCount will be 3
'If last item in listbox is selected the ListIndex will be 2

'If the current selected item in the listbox list is at the last position in the list the
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then
'Enable the text box
TextBox1.Enabled = True
'Unlock text box, ie make it editable
TextBox1.Locked = False
Else
'Reset text box to empty string
TextBox1.Value = ""
'Lock text box
TextBox1.Locked = True
'Disable the text box, ie grey it out
TextBox1.Enabled = False
End If
End Sub

 

[MikeCouling]

Thanks for your time & expertise ktwclark, that's certainly got me a lot further.

However, and this may be a step too far, I would ideally like the same thing to happen when the ListBox has its MultiSelect property set to 1 or 2. i.e. Is there a way of identifying that "Other" has been selected when it might be one of several items in the List that have also been selected?

 

[PHV]

Replace this:
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then
with this:
If ListBox1.Selected(ListCount - 1) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeCouling]

Thanks for the input PHV - I thought you'd cracked it! However it still only works if MultiSelect on the ListBox is set to 0.
A chance for me to have a go with the debugger!

 

[PHV]

Sorry for the typo:
If ListBox1.Selected([!]ListBox1.[/!]ListCount - 1) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeCouling]

Thanks PH but I had spotted that (after a rude message admittedly). Still not getting it to work though with MultiSelect = 2 though.

 

[combo]

I'd stay with the 'Change' event. If necessary I'd add a flag to tect that the list was changed by the user:

Private Sub ListBox1_Change()
    If ListBox1.Selected(ListBox1.ListCount - 1) = True Then
        If TextBox1.Enabled = False Then
            TextBox1.Enabled = True
            TextBox1.SetFocus
        End If
    Else
        TextBox1.Text = ""
        TextBox1.Enabled = False
    End If
End Sub

combo

 

[MikeCouling]

Thanks a lot combo - the Change event did the trick.

Thanks for everybody's help with this - I'm setting this up for a charity so you've all done your good deed for the day!!