Creation6000ya
New Member
- Joined
- Nov 2, 2019
- Messages
- 3
Good afternoon Mr. Excel,
I have a workbook that runs through code using a UserForm with several TextBoxes and a couple of ComboBoxes. The below code shows what I have so far but I am trying to figure out how to Loop through the code a specific number of times based on a value the user will enter on the UserForm. The code that is indented is what I currently have and it works well for what it is. The small section of code that is justified to the left is what I have added to try and accomplish this loop, but it currently is not working and when I try and run it, it is giving me an error saying "Object Required" and highlighting "(NumberOfApptsTextBox)" in the line that reads "Set apptsToSet = (NumberOfApptsTextBox) . I thought the NumberOfApptsTextBox was the object!
I have a workbook that runs through code using a UserForm with several TextBoxes and a couple of ComboBoxes. The below code shows what I have so far but I am trying to figure out how to Loop through the code a specific number of times based on a value the user will enter on the UserForm. The code that is indented is what I currently have and it works well for what it is. The small section of code that is justified to the left is what I have added to try and accomplish this loop, but it currently is not working and when I try and run it, it is giving me an error saying "Object Required" and highlighting "(NumberOfApptsTextBox)" in the line that reads "Set apptsToSet = (NumberOfApptsTextBox) . I thought the NumberOfApptsTextBox was the object!
Code:
Private Sub EnterButton_Click()
Dim thisSheet As Worksheet
Dim stopDate As Date
Dim thisAppointment As Date
Dim apptsToSet As Integer
Set thisSheet = thisWorkbook.Sheets("Future Appointments")
If SchoolNameComboBox = "" Or ApptDateTextBox = "" Or ApptTimeTextBox = "" Or SchedStatusComboBox = "" Or WeeklyIntervalTextBox = "" Then
MsgBox "All of These Fields are REQUIRED..." & _
vbNewLine & _
" - SCHOOL NAME" & vbNewLine & _
" - APPOINTMENT DATE" & vbNewLine & _
" - APPOINTMENT TIME" & vbNewLine & _
" - STATUS TO SET" & vbNewLine & _
" - AT ____ WEEK INTERVALS" & vbNewLine & vbNewLine & _
"At Least 1 of These 2 Fields are REQUIRED..." & vbNewLine & _
" - SET ____ APPOINTMENTS" & vbNewLine & _
" - STOP DATE" & vbNewLine & vbNewLine & _
"This Field is OPTIONAL..." & vbNewLine & _
" - DEFAULT NOTE", vbOKOnly, "INCOMPLETE INFORMATION"
Else
' Set apptsToSet = NumberOfApptsTextBox
' Set stopDate = CDate(Me.StopDateTextBox)
Do
Set apptsToSet = (NumberOfApptsTextBox)
NextAppDate = IIf(MaxDate = 0, "", MaxDate)
If NumberOfApptsTextBox <> "" Then
Call NumberOfApptsTextBox_Change
If NumberOfApptsTextBox = 0 Then
End Sub
End If
End If
' Set thisAppointment = CDate(Me.ApptDateTextBox)
If Not WeeklyIntervalTextBox = "" And thisAppointment <= stopDate Then
NextAppDate = DateAdd("w", CDbl(WeeklyIntervalTextBox * 7), ApptDateTextBox)
Else
ThisName = SchoolNameComboBox
With Worksheets("Future Appointments")
SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
SchoolDays = Intersect(.Range("A2").CurrentRegion, .Range("G:G")) 'Edit to fit
End With
For i = LBound(SchoolNames) + 1 To UBound(SchoolNames) '+1 to skip headers
If SchoolNames(i, 1) = ThisName Then MaxDate = Application.Max(MaxDate, SchoolDays(i, 1))
Next i
NextAppDate = IIf(MaxDate = 0, "", MaxDate)
End If
ApptDateTextBox.Text = NextAppDate
nextrow = thisSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
If NumberOfApptsTextBox <> 0 Then
thisSheet.Cells(nextrow, 1) = Me.SchoolNameComboBox
thisSheet.Cells(nextrow, 5) = Me.SchedStatusComboBox
thisSheet.Cells(nextrow, 7) = CDate(Me.ApptDateTextBox)
thisSheet.Cells(nextrow, 8) = CDate(Me.ApptTimeTextBox)
thisSheet.Cells(nextrow, 9) = Me.DefaultNoteTextBox
End If
Loop While NumberOfApptsTextBox <> 0
End If
Me.Hide
Unload Me
End Sub
