Convert ListBox into UserForm

[grady121]

I've been given a code that allows me to enter a value into column 9 from a list of names in Column 1 and works well via a basic Listbox. Shown below.

But what I would like to do is convert this into a more pleasing Userform layout, containing TextBoxes to show first the names in "TextBox1" as they appear in the list, and another "Textbox2" for me to enter the new Data. It would also have an OK button to insert the Data and move onto the next Name in the list.

Dim ListRow, ListColumn, NewDataColumn As Integer
Dim MyNewData As String

ListRow = 1: ListColumn = 1: NewDataColumn = 9

While ThisWorkbook.Sheets(1).Cells(ListRow, ListColumn) <> ""
    Sheets(1).Cells(ListRow, NewDataColumn) = InputBox("The contents of the current cell is " & Sheets(1).Cells(ListRow, ListColumn), "Enter data for each entry")
    ListRow = ListRow + 1
Wend

Not sure how complicated this will be but any help for a Newbie appreciated.

 

[Norie]

Do you mean a userform with a listbox and 2 textboxes?

The user selects an item in the list, and that's put in the 1st textbox.

Then they enter a new values, hit the OK button and the new value is transferred back to the list.

 

[grady121]

Hi Norie, thanks for the reply.

What I was hoping for was Userform1 to have two TextBoxes and a CommandButton1 (with a Caption name of "OK") placed on it. I wanted "TextBox1" to capture the Name in Column one, and the second, "TextBox2", is to allow me to fill in the required data associated with the Name shown in TextBox1.

So after filing in the data in "TextBox2", I hit the CommandButton1 it fills in the data into Column 9 and move onto the next name.....

I appreciate it's simpler to use the existing InputBox code, but it just looks too large for data that will only ever be two characters long.

Hope that makes sense.
Thanks again.

 

[Norie]

I did actually mean using a userform and I still think what I suggested would work.

If what you actually want to do is 'assign' a value to a name that is.

Just have a listbox for the names and a textbox for the value.

User selects name, enters value, hits OK and the value is 'assigned to the name.

Is that closer to what you want?

 

[grady121]

Originally I was just trying to find a way to Exit the existing routine if the Cancel button was clicked, just in case an error occurred in what can be a long process list. If on clicking Cancel - ideally to show a Yes/No MsgBox options to either start the Loop again, or just Exit completely.

If you have any advice on how that might work it may be easier to still use the InputBox option.

 

[grady121]

I have now added a MsgBox to the code to catch if nothing has been entered in the InputBox, but having dificulty in getting the correct response.

Depending on which "Case" is listed first, it either continues through the loop, or Exits the sub, regardless of which Yes/No button is clicked on the resulting MsgBox.

     Dim ListRow, ListColumn, NewDataColumn As Integer
    Dim MyNewData As String

    ListRow = 10: ListColumn = 1: NewDataColumn = 9
    While Sheets(1).Cells(ListRow, ListColumn) <> ""
    MyNewData = InputBox("Enter the Data for:- " & vbNewLine & vbNewLine & Sheets(1).Cells(ListRow, ListColumn), "Add Data", Sheets(1).Cells(ListRow, NewDataColumn))  'Get input
        If MyNewData <> "" Then Sheets(1).Cells(ListRow, NewDataColumn) = MyNewData 'If input is not empty, use the input
    ListRow = ListRow + 1
    If MyNewData = "" Then
    MsgBox "No Data entered for:- " & Sheets(1).Cells(ListRow - 1, ListColumn) & vbNewLine & vbNewLine _
    & "Do you want to continue?" & vbNewLine & vbNewLine _
    & "Click Yes to continue, or click No to Exit", vbYesNo, "No Data Added"
    Select Case True
        Case vbYes
        
        Case vbNo
            Exit Sub
    End Select
    End If
    Wend

Is there anything obvious I'm missing, or won't this approach work?