UserForm VBA to get Listbox to populate

[Kellis]

Hi all

I am currently building what I hope is a basic user form and VBA is not my strong point.
I am currently matching the form entries to the spreadsheet which is working so far however I have a listbox where the range is set and it appears on the form however when the user scrolls to the entry required and does not hit enter it will always return the first entry (or last entry). Can I get the listbox to choose on the scroll rather than hit enter or would I need to add a message box to advise the user they need to highlight and enter.

Here is my code so far

Private Sub cmdOK_Click()
''Okay save record

Dim RowCount As Long


RowCount = Worksheets("Form").Range("A1").CurrentRegion.Rows.Count


    With Worksheets("Form").Range("A1")
        .Offset(RowCount, 0).Value = Me.txtDate.Value
        .Offset(RowCount, 1).Value = Me.txtSIMSJN
        .Offset(RowCount, 2).Value = Me.lstDistrict.Value
        .Offset(RowCount, 3).Value = Me.txtHVPrim.Value
        .Offset(RowCount, 4).Value = Me.txtHVFeeder.Value
        .Offset(RowCount, 5).Value = Me.txtFdrName.Value

'In Eng Frame

        .Offset(RowCount, 6).Value = Me.txtEngName.Value
        .Offset(RowCount, 7).Value = Me.txtEngETA.Value
       
    If Me.ChkEngmob.Value = True Then
        .Offset(RowCount, 8).Value = "Yes"
       
  Else
        .Offset(RowCount, 8).Value = "No"
       
        End If
       
'In RRO frame
           
        .Offset(RowCount, 9).Value = Me.txtRROName.Value
        .Offset(RowCount, 10).Value = Me.txtRROeta.Value
       
    If Me.ChkRROMobile.Value = True Then
        .Offset(RowCount, 11).Value = "Yes"
       
  Else
        .Offset(RowCount, 11).Value = "No"
       
    End If
       
    End With
   
    Dim ctl As Control

For Each ctl In Me.Controls

    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "Combobox" Or TypeName(ctl) = "Listbox" Then
    ctl.Value = ""
   
    ElseIf TypeName(ctl) = "CheckBox" Then
    ctl.Value = False
   
    End If
   
    Next ctl
End Sub

Hope this is understandable.

 

[Luke M]

User should select the desired item. I'd suggest putting something like this near beginning of macro (before you start making changes to sheet).

If Me.lstDistrict.Value = Null Then
        MsgBox "Please make a selection from list"
        Exit Sub
    End If

 

[Kellis]

Thanks perfect, I went with

If Me.lstDistrict.Value = "Choose District" Then
        MsgBox "Please pick District"
        Exit Sub

this work so now I need to clear the Chosen district on the command 'Okay', Soooo should I add this, it seems to work but does it look right?

For Each ctl In Me.Controls

    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "Combobox" Then
    ctl.Value = ""
   
    ElseIf TypeName(ctl) = "CheckBox" Then
    ctl.Value = False
   
    ElseIf TypeName(ctl) = "ListBox" Then
    ctl.Value = "Choose District"
   
    End If

Choose District is the first line in my district range, I want it to default back to this to ensure the user does not just stick with the first actual district in the list.

I'm loving this, it is amazing what satisfaction you get trying to work this out, your help is invaluable to us learners. Please Luke M, do not go far. lol

 

[Kellis]

Hi
I have changed my Listbox to a combo box and remembered to change all my code names but now the clear box on okay is not working any ideas?

 

[Kellis]

Scrap that I've it. Thanks

 

[Luke M]

Glad you got it figured out, and kudos to you for sticking with it!