VBA Message Box

[Vin90]

I would like to make the combination of UserForm and MessageBox but stuck with the code, can anyone help to rewrite the code correctly?

[U][COLOR=#ff0000]Private Sub CommandButton1_Click()[/COLOR][/U]


If MsgBox("Are you sure?", vbYesNo) = vbYes Then


Dim lRow As Long
Dim ws As Worksheet
Set ws = Worsheets("Sheet1")
lRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
With ws
    .Cells(lRow, 1).Value = Me.TextBox1.Value
    .Cells(lRow, 2).Value = Me.TextBox2.Value
End With
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""


End If
End Sub

Private Sub Commandbutton2_click()
Unload Me
End Sub

The idea is clicking CommandButton1 will prompt the message box, click Yes on the MessageBox the values on the Textboxes will be transferred into worksheet and textboxes will be cleared, click No will only close the MessageBox.

I got an error on the red highlighted area, and assume the code is properly written.

 

[sericom]

Set ws = Worsheets("Sheet1")
Worksheets is misspelt

lRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
x1Up should be xLup

 

[Vin90]

OMG, such a silly mistake.. Thank you

 

[Vin90]

I need another extra help, in autofill with formula function.

Google search lands me on this code

Sub FillDown()
Dim strFormulas(1 To 3) As Variant
With ThisWorkbook.Sheets("Data")
    strFormulas(1) = "=YEAR(A2)"
    strFormulas(2) = "=MONTH(A2)"
    strFormulas(3) = "=ISOWEEKNUM(A2)"
    
    .Range("B2:D2").Formula = strFormulas
[COLOR=#ff0000][U]    .Range("B2:D" & lRow).FillDown[/U][/COLOR]
End With
End Sub

I want to have a macro that can help me to quickly fill in the cells B2:D2 and the consecutive rows with weeknumber, month and year with a reference to a date on cell A2 onward.

Is there anything wrong with this code?

In addition, is it possible to combine this code to a command button in a userform? so everytime a new data is saved, they can quickly fill the gap information.

My current userform code is like this:

Private Sub UserForm_Initialize()
    'Populate control.
    With cboShift
        .AddItem "Night"
        .AddItem "Morning"
        .AddItem "Evening"
    End With
    With cboPrd
        .AddItem "K2"
        .AddItem "TC"
        .AddItem "TP"
    End With
    TextBox1.Value = Format(Date, "mm/dd/yyyy")
    Label1100.Font.Size = 15
    Label1100.Font.Bold = True
    Label1200.Font.Size = 14
    Label1200.Font.Bold = True
    Label1300.Font.Size = 14
    Label1300.Font.Bold = True
    Label1301.Font.Size = 10
    Label1301.Font.Bold = True
    Label1302.Font.Size = 10
    Label1302.Font.Bold = True
    Label1400.Font.Size = 14
    Label1400.Font.Bold = True
End Sub
Private Sub cmdAdd_Click()
    
If MsgBox("Do you want to input the data?", vbYesNo) = vbYes Then
    
[COLOR=#00ff00]    'Copy input values to sheet.[/COLOR]
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.TextBox1.Value
        .Cells(lRow, 3).Value = Me.cboShift.Value
        .Cells(lRow, 4).Value = Me.cboPrd.Value
        .Cells(lRow, 5).Value = Me.TextBox2.Value
        End With
[COLOR=#00ff00]    'Clear input controls.[/COLOR]
    Me.cboShift.Value = ""
    Me.cboPrd.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
Else
End If
End Sub

Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
End Sub