Dynamically updating userform that lists worksheets and allows to edit one cell in each worksheet

[bujaman]

OK, I have a question that I am stuck on. I would like to allow users to display a userform that lists all worksheets except for three (Sheet 1, Sheet 2, Sheet 3) in the workbook and has a textbox next to the worksheet name that displays the value of cell A1 in that particular worksheet. The user can then change the value in this textbox and when they hit OK at the bottom, it updates the value in that cell on the respective worksheet. Each user may have a different number of worksheets, so scrolling needs to be enabled on the userform. I have a userform displaying the names of all the worksheets, but am struggling with the rest. Here is what I have so far:

Private Sub UserForm_Activate()
Dim shtSheet As Worksheet
Dim strSheetNames As String
For Each shtSheet In ActiveWorkbook.Worksheets
    strSheetNames = strSheetNames & shtSheet.Name & Chr$(13)
Next shtSheet
Me.Label1 = strSheetNames
End Sub

Any help would be awesome! Thanks!

 

[AlphaFrog]

If your Userform has these three items

• TextBox1
• CommandButton1
• ListBox1

This code will populate ListBox1 with the sheet names and their respective A1 values.

When the user selects one of the sheets in the list, Textbox1 will update with the selected A1 value. The user can then change the value in Textbox1 . When the user clicks CommandButton1, the sheet value is updated.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    
    ListBox1.ColumnCount = 2    ' Listbox with two columns of values
    
    For Each ws In Worksheets
        If ws.Name <> "Sheet1" And _
           ws.Name <> "Sheet2" And _
           ws.Name <> "Sheet3" Then
                ' Populate ListBox1
                ListBox1.AddItem (ws.Name)
                ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Range("A1").Value
        End If
    Next ws
    
End Sub


Private Sub CommandButton1_Click()
    If TextBox1.Value <> vbNullString Then
        Sheets(ListBox1.Value).Range("A1").Value = TextBox1.Value   ' Update worksheet
        ListBox1.List(ListBox1.ListIndex, 1) = TextBox1.Value       ' Update Listbox with new value
    End If
End Sub


Private Sub ListBox1_Click()
    TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 1)           ' Update TextBox1 with selected A1 value
End Sub

 

[bujaman]

Thanks! That is working great. I have another question with the same userform. Is it possible to allow users to select multiple items in the listbox and change the values of all the selected items at the same time? Just curious. Thanks again for the help!

 

[AlphaFrog]

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    
    With ListBox1
    
        .ColumnCount = 2                    ' Listbox with two columns of values
        .MultiSelect = fmMultiSelectMulti   ' Allow muultiselect
        
        For Each ws In Worksheets
            If ws.Name <> "Sheet1" And _
               ws.Name <> "Sheet2" And _
               ws.Name <> "Sheet3" Then
                    ' Populate ListBox1
                    .AddItem (ws.Name)
                    .List(ListBox1.ListCount - 1, 1) = ws.Range("A1").Value
            End If
        Next ws
    
    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
    
End Sub


Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 1)           ' Update TextBox1 with selected A1 value
End Sub

 

[bujaman]

That works perfect! Thanks!