-
-
1 Attachment(s)
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
-
Use this code
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"
-
-
Ok I got it now.That works
I modified another sheet and it now works as well
Thanks for all those who helped
Paul
