Ok so I know this is a rather common topic on forums and I have already been sitting here for literally hours trying to figure this out. I have checked forums and help centers but I cannot figure it out. I am trying to input data into my created userform and have it input on the spreadsheet. For some reason when I click my command button that is the "OK" button, the code works to a certain extent but not all the way. I have coded it to require certain information and that part works but when it comes time to put code in the worksheet it just does not want to cooperate. please help. I know my code could be reduced but please forgive my amateur coding. I am using Excel 2010 on Windows 7
Shot at 2012-06-19
Shot at 2012-06-19
Code:
Public doc As String
Public day As String
Public month As String
Public year As String
Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Jan"
month = 1
Case "Feb"
month = 2
Case "Mar"
month = 3
Case "Apr"
month = 4
Case "May"
month = 5
Case "June"
month = 6
Case "July"
month = 7
Case "Aug"
month = 8
Case "Sept"
month = 9
Case "Oct"
month = 10
Case "Nov"
month = 11
Case "Dec"
month = 12
End Select
End Sub
Private Sub ComboBox2_Change()
day = Me.ComboBox2.Value
End Sub
Private Sub ComboBox3_Change()
year = Me.ComboBox3.Value
End Sub
Private Sub CommandButton1_Click()
Dim RowCount As Long
Dim toc As String
Dim ctl As Control
If Me.TextBox1.Value = "" Then
MsgBox "Please Enter the New Sweetheart's First Name.", vbExclamation, "Error"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox10.Value = "" Then
MsgBox "Please Enter the Soul-winner's First Name.", vbExclamation, "Error"
Me.TextBox10.SetFocus
Exit Sub
End If
If Me.TextBox11.Value = "" Then
MsgBox "Please Enter the Soul-winner's Last Name.", vbExclamation, "Error"
Me.TextBox11.SetFocus
Exit Sub
End If
If Me.ComboBox1.Value = "" Then
MsgBox "Please Select the Month.", vbExclamation, "Error"
Me.ComboBox1.SetFocus
Exit Sub
End If
If Me.ComboBox2.Value = "" Then
MsgBox "Please Select the Day.", vbExclamation, "Error"
Me.ComboBox2.SetFocus
Exit Sub
End If
If Me.ComboBox3.Value = "" Then
MsgBox "Please Select the Year.", vbExclamation, "Error"
Me.ComboBox3.SetFocus
Exit Sub
End If
If OptionButton1.Value = True Then
Exit Sub
Else
If OptionButton2.Value = True Then
Exit Sub
Else
If OptionButton3.Value = True Then
Exit Sub
Else
If OptionButton4.Value = True Then
Exit Sub
Else
If OptionButton5.Value = True Then
Exit Sub
Else
If OptionButton6.Value = True Then
Exit Sub
Else
MsgBox "Please Select the Type of Contact.", vbExclamation, "Error"
Exit Sub
End If
End If
End If
End If
End If
End If
Sheets("Info").Range("A4").Select
Do Until ActiveCell = ""
If ActiveCell = "" Then
ActiveCell.Offset(0, 1).Select
If ActiveCell = "" Then
ActiveCell.Offset(0, -1).Select
Else
ActiveCell.Offset(1, -1).Select
End If
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
With Worksheets("Info").Range("A3")
.Offset(RowCount, 0).Value = Me.TextBox1.Value
.Offset(RowCount, 1).Value = Me.TextBox2.Value
.Offset(RowCount, 2).Value = Me.TextBox3.Value
.Offset(RowCount, 3).Value = Me.TextBox4.Value
.Offset(RowCount, 4).Value = Me.TextBox5.Value
.Offset(RowCount, 5).Value = Me.TextBox6.Value
.Offset(RowCount, 6).Value = Me.TextBox7.Value
.Offset(RowCount, 7).Value = Me.TextBox8.Value
.Offset(RowCount, 8).Value = Me.TextBox9.Value
.Offset(RowCount, 9).Value = Me.TextBox10.Value
.Offset(RowCount, 10).Value = Me.TextBox11.Value
.Offset(RowCount, 11).Value = Me.TextBox12.Value
.Offset(RowCount, 12).Value = Me.TextBox13.Value
doc = (month & "/" & day & "/" & year)
.Offset(RowCount, 13).Value = doc
If OptionButton1.Value = True Then
toc = "Home Visit"
Exit Sub
Else
If OptionButton2.Value = True Then
toc = "Visited Church"
Exit Sub
Else
If OptionButton3.Value = True Then
toc = "Phone Call"
Exit Sub
Else
If OptionButton4.Value = True Then
toc = "Letter"
Exit Sub
Else
If OptionButton5.Value = True Then
toc = "Email"
Exit Sub
Else
If OptionButton6.Value = True Then
toc = "Run-In"
Exit Sub
End If
End If
End If
End If
End If
End If
.Offset(RowCount, 14).Value = toc
.Offset(RowCount, 15).Value = Me.TextBox14.Value
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "OptionButton" Then
ctl.Value = False
End If
Next ctl
End Sub
Private Sub CommandButton2_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "OptionButton" Then
ctl.Value = False
End If
Next ctl
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
