Transfering Data From Userform To Sheet Under Relevant Column And Row

[paul_pearson]

Ok I will try again

Paul

[paul_pearson]

I filled in the tag property,changed the sheets names but still get error

What have I missed.Combobox2 entry decides on which sheet to enter data....is this the wrong approached?

Thanks

Paul

[Excel Fox]

Use this code

Option Explicit

Private Sub UserForm_Initialize()


    Dim i As Long
    Dim vList As Variant
    Dim vSht As Variant
    
    For i = CLng(Date - 2) To CLng(Date + 7)
        vList = vList & Format(i, "dd/mm/yyyy") & ","
    Next i
    vList = Left(vList, Len(vList) - 1)
    ComboBox1.List = Split(Mid(vList, 1), ",")
    
    vList = ("C1,C2,C3,C4,C5")
    ComboBox2.List = Split(Mid(vList, 1), ",")
    
    vList = vbNullString
    vList = "-,"
    For i = CLng(Date - 2) To CLng(Date + 7)
        vList = vList & Format(i, "dd/mm/yyyy") & ","
    Next
    vList = Left(vList, Len(vList) - 1)
    ComboBox3.List = Split(Mid(vList, 1), ",")
    
    vList = vbNullString
    vList = "-,"
    For i = CLng(Date - 2) To CLng(Date + 7)
        vList = vList & Format(i, "dd/mm/yyyy") & ","
    Next
    vList = Left(vList, Len(vList) - 1)
    ComboBox4.List = Split(Mid(vList, 1), ",")
    
    vList = ("AA,BB,CC,DD,EE")
    ComboBox5.List = Split(Mid(vList, 1), ",")
    For Each vSht In Array("C1", "C2", "C3", "C4", "C5")
        With Worksheets(vSht)
            .Unprotect Password:="1357"
            .Protect UserInterfaceOnly:=True, Password:="1357"
        End With
    Next


End Sub


Private Sub CommandButton1_Click()
   
    Dim lngCol As Long
    Dim ctl As Object
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    With Sheets(ComboBox2.Value)
       With .Cells(.Rows.Count, 2).End(xlUp).Offset(1)
            .Resize(, 6) = Array(ComboBox1.Value, ComboBox3.Value, ComboBox4.Value, ComboBox5.Value, TextBox1.Text, TextBox2.Text)
        End With
    End With
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
    Next
    
End Sub

By the way, please correct the sheet name from "C1 " to "C1"

[regc]

Thanks Excel fox

[paul_pearson]

Ok I got it now.That works
I modified another sheet and it now works as well
Thanks for all those who helped
Paul