Update range in worksheets based on selections in userform listbox

[bujaman]

I am using a listbox in a user form that is being populated with two values from each worksheet in a workbook, the value in range Y1 (Product Name) and the value in range A1. The box is populating great, but I want users to be able to edit the value in A1 through the userform. I am using TextBox1 on the userform to allow users to enter a new value for Range A1 on each sheet, so when the user selects the product from the listbox, they can change the value in TextBox1, hit and "Update" button, then the value will be passed from TextBox1 to Range A1 of the selected sheets. I am having some trouble getting it to work properly. Here is the code so far:

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim ProdTitle
    With ListBox1
    
        .ColumnCount = 2                    ' Listbox with two columns of values

        
        For Each ws In Worksheets
        ProdTitle = ws.Range("Y1").Value
        If ws.Name <> "Data" Then
                    ' Populate ListBox1
                    .AddItem (ProdTitle)
                    .list(ListBox1.ListCount - 1, 1) = ws.Range("A1").Value
            End If
        Next ws
     ListBox1.ColumnWidths = "600;30"
    End With
    
End Sub



Private Sub CommandButton1_Click()
    Dim i As Integer
    
    If TextBox1.Value <> vbNullString Then
        With ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) = True Then
                    Sheets(.list(i, 0)).Range("A1").Value = TextBox1.Value   ' Update worksheet
                    .list(i, 1) = TextBox1.Value                             ' Update Listbox with new value
                End If
            Next i
        End With
    End If
ThisWorkbook.Save
    
End Sub

The CommandButton1_Click sub was originally used to check the worksheet name (the worksheet name used to populate the listbox, but due to the limited number of characters allowed in worksheet names, I need to reference Y1 instead), then pass the new value to range A1 in that worksheet. Now I need it to check the value of Y1 in each worksheet and if Y1 matches what is selected in the listbox, then update A1 with value from TextBox1. Thanks upfront for any help, I really appreciate it.

 

[AlphaFrog]

For each worksheet, this code puts the Y1 value in 1st column of the listbox, the A1 value in the 2nd column, and the worksheet name in the 3rd column. The third column of the listbox is not displayed. It's only used as a worksheet reference of the selected item(s) in the listbox.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet

    With ListBox1
    
        For Each ws In Worksheets
            If ws.Name <> "Data" Then
                ' Populate ListBox1
                .AddItem ws.Range("Y1").Value
                .List(.ListCount - 1, 1) = ws.Range("A1").Value
                [COLOR="Red"].List(.ListCount - 1, 2) = ws.Name[/COLOR]
            End If
        Next ws

        .ColumnCount = 2                    ' Listbox with two columns of values
        .ColumnWidths = "600;30"

    End With
    
End Sub


Private Sub CommandButton1_Click()
    Dim i As Integer
    
    If TextBox1.Value <> vbNullString Then
        With ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    [COLOR="Red"]Sheets(.List(i, 2)).[/COLOR]Range("A1").Value = TextBox1.Value   ' Update worksheet
                    .List(i, 1) = TextBox1.Value                             ' Update Listbox with new value
                End If
            Next i
        End With
    End If
    
    ThisWorkbook.Save
    
End Sub

 

[bujaman]

Thank you so much! That is exactly what I needed. I really appreciate it!