VBA userform to write down timestamp to range on sheet-CodePudding

In my excel file I have a worksheet for every person. This worksheet is copied according a template sheet after entering data.

Now for the next part I would like to add data to a specific range on the sheet of that person.

Let's start with a simple date stamp to Range E4:E53 for the specified sheet. I'm using a combobox so you can select someone from the list and this is where i'm struggling;

After selecting someone from the list, my code does not write down the data.

As shown in the picture, the Worksheet is set to nothing. How do I set the worksheet according to the selected person from the combobox?

Public Sub CommandButton1_Click()

    Dim lRow As String
    Dim Rng As Range
    Dim Rng2 As Range
    Dim ws As Worksheet

    Set ws = ComboBox1.List(I, 0)
    
    Set Rng = Range("C4, C53")
    Set Rng2 = Range("E4, Q53")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        
        With ws
            Rng.Cells.lRow.Value = Format(Time, "DD:MM:YYYY:HH:MM")
            
        End With
End sub

CodePudding user response：

I assume that your list contains names of worksheets for each person, like {"Monica", "Adam"...}, right?

The problem in your case is that you try to use string value from ComboBox1 to define worksheet which is an object in worksheets collection.

You should get string value (name) of worksheet and then use it to set your ws object.

Here is simple code snippet, hope it is what you wanted to achieve :)

Private Sub ComboBox1_click()

Dim ws As Worksheet

'Define worksheet from worksheets collection
Set ws = worksheets(ComboBox1.Value)

ws.Range("A5").Value = "Hello!"

End Sub


Private Sub UserForm_Initialize()

Dim ws As Worksheet

'Make list of all worksheets
For Each ws In worksheets
    ComboBox1.AddItem ws.Name
Next ws

End Sub