Modify User Form to do event to each day in a range of dates

S

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hi everyone, I have a user form that is part of a scheduling ws. The user form alows the user to
choose an employee from a combobox
choose a date from a combobox (had to go with combox, cause the filed doesnt have this object :( )
choose if its PTO or Off day (radio button)

It then runs though the script placing the approprate info on the scheduler and in a calendar etc..

My hopes is get help from you to modify the below code (sorry so long, thought it would be helpful) to allow me to use 2 comboboxes (for the dates) to do the same chain of events for each day in the range. i.e. If the first combobox sais 3-1-2012 and the second 3-5-2012, it will do al the events for each day in the range (in this case 5 times)

Thanks in advance for any direction. :)

Code: 
Private Sub CommandButton3_Click() 'input
Dim ws As Worksheet
Dim wb As Workbook
Dim FindValue As String, Box As String, putName As String, FindName As String, FindNumber As String, Found As Range
Dim RowNum As Integer, ColNum As Integer
Dim r As Integer
If Me.OptionButton3 = False And Me.OptionButton4 = False And Me.OptionButton5 = False And Me.OptionButton6 = False And Me.OptionButton7 = False And Me.OptionButton8 = False And Me.OptionButton9 = False And Me.OptionButton10 = False And Me.OptionButton11 = False And Me.OptionButton12 = False And Me.OptionButton13 = False And Me.OptionButton14 = False And Me.OptionButton15 = False And Me.OptionButton16 = False And Me.OptionButton17 = False And Me.OptionButton18 = False And Me.OptionButton19 = False And Me.OptionButton20 = False And Me.OptionButton21 = False And Me.OptionButton22 = False And Me.OptionButton23 = False And Me.OptionButton24 = False And Me.OptionButton25 = False And Me.OptionButton26 = False And Me.OptionButton27 = False And Me.OptionButton28 = False And Me.OptionButton29 = False And Me.OptionButton30 = False Then
MsgBox "Please Choose an Employee..."
Exit Sub
End If
If Me.OptionButton1 = False And Me.OptionButton2 = False Then
MsgBox "Please Choose PTO or OFF..."
Exit Sub
End If
If Me.ComboBox1.Value = "" Then
MsgBox "Please Choose an Date..."
Exit Sub
End If
 
Application.ScreenUpdating = False
Sheets("MyStoreInfo").Visible = True
Sheets("MyStoreInfo").Rows("11:157").Hidden = False
FindValue = Me.ComboBox1.Value
If Me.OptionButton1 = True Then
Box = "O"
End If
If Me.OptionButton2 = True Then
Box = "P"
End If
    'On Error GoTo finish
    Range("P11:AD197").Select
   Selection.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    'On Error GoTo 0
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
If Me.OptionButton3 = True Then
    putName = Me.TextBox1.Value & " " & Me.TextBox2.Value
End If
If Me.OptionButton4 = True Then
    putName = Me.TextBox3.Value & " " & Me.TextBox4.Value
End If
If Me.OptionButton5 = True Then
    putName = Me.TextBox5.Value & " " & Me.TextBox6.Value
End If
If Me.OptionButton6 = True Then
    putName = Me.TextBox7.Value & " " & Me.TextBox8.Value
End If
If Me.OptionButton7 = True Then
    putName = Me.TextBox9.Value & " " & Me.TextBox10.Value
End If
If Me.OptionButton8 = True Then
    putName = Me.TextBox11.Value & " " & Me.TextBox12.Value
End If
If Me.OptionButton9 = True Then
    putName = Me.TextBox13.Value & " " & Me.TextBox14.Value
End If
If Me.OptionButton10 = True Then
    putName = Me.TextBox15.Value & " " & Me.TextBox16.Value
End If
If Me.OptionButton11 = True Then
    putName = Me.TextBox17.Value & " " & Me.TextBox18.Value
End If
If Me.OptionButton12 = True Then
    putName = Me.TextBox19.Value & " " & Me.TextBox20.Value
End If
If Me.OptionButton13 = True Then
    putName = Me.TextBox21.Value & " " & Me.TextBox22.Value
End If
If Me.OptionButton14 = True Then
    putName = Me.TextBox23.Value & " " & Me.TextBox24.Value
End If
If Me.OptionButton15 = True Then
    putName = Me.TextBox25.Value & " " & Me.TextBox26.Value
End If
If Me.OptionButton16 = True Then
    putName = Me.TextBox27.Value & " " & Me.TextBox28.Value
End If
If Me.OptionButton17 = True Then
    putName = Me.TextBox29.Value & " " & Me.TextBox30.Value
End If
If Me.OptionButton18 = True Then
    putName = Me.TextBox31.Value & " " & Me.TextBox32.Value
End If
If Me.OptionButton19 = True Then
    putName = Me.TextBox33.Value & " " & Me.TextBox34.Value
End If
If Me.OptionButton20 = True Then
    putName = Me.TextBox35.Value & " " & Me.TextBox36.Value
End If
If Me.OptionButton21 = True Then
    putName = Me.TextBox37.Value & " " & Me.TextBox38.Value
End If
If Me.OptionButton22 = True Then
    putName = Me.TextBox39.Value & " " & Me.TextBox40.Value
End If
If Me.OptionButton23 = True Then
    putName = Me.TextBox41.Value & " " & Me.TextBox42.Value
End If
If Me.OptionButton24 = True Then
    putName = Me.TextBox43.Value & " " & Me.TextBox44.Value
End If
If Me.OptionButton25 = True Then
    putName = Me.TextBox45.Value & " " & Me.TextBox46.Value
End If
If Me.OptionButton26 = True Then
    putName = Me.TextBox47.Value & " " & Me.TextBox48.Value
End If
If Me.OptionButton27 = True Then
    putName = Me.TextBox49.Value & " " & Me.TextBox50.Value
End If
If Me.OptionButton28 = True Then
    putName = Me.TextBox51.Value & " " & Me.TextBox52.Value
End If
If Me.OptionButton29 = True Then
    putName = Me.TextBox53.Value & " " & Me.TextBox54.Value
End If
If Me.OptionButton30 = True Then
    putName = Me.TextBox55.Value & " " & Me.TextBox56.Value
End If
    
    For r = 1 To 28
        If Cells(RowNum + r, ColNum) = "" Or Cells(RowNum + r, ColNum) = putName Then
            Cells(RowNum + r, ColNum) = putName
            Cells(RowNum + r, ColNum + 1) = Box
            
    'begin part that inputs the letter to Schedule Template
If Me.OptionButton3 = True Then
    FindName = Me.TextBox1.Value & " " & Me.TextBox2.Value
End If
If Me.OptionButton4 = True Then
    FindName = Me.TextBox3.Value & " " & Me.TextBox4.Value
End If
If Me.OptionButton5 = True Then
    FindName = Me.TextBox5.Value & " " & Me.TextBox6.Value
End If
If Me.OptionButton6 = True Then
    FindName = Me.TextBox7.Value & " " & Me.TextBox8.Value
End If
If Me.OptionButton7 = True Then
    FindName = Me.TextBox9.Value & " " & Me.TextBox10.Value
End If
If Me.OptionButton8 = True Then
    FindName = Me.TextBox11.Value & " " & Me.TextBox12.Value
End If
If Me.OptionButton9 = True Then
    FindName = Me.TextBox13.Value & " " & Me.TextBox14.Value
End If
If Me.OptionButton10 = True Then
    FindName = Me.TextBox15.Value & " " & Me.TextBox16.Value
End If
If Me.OptionButton11 = True Then
    FindName = Me.TextBox17.Value & " " & Me.TextBox18.Value
End If
If Me.OptionButton12 = True Then
    FindName = Me.TextBox19.Value & " " & Me.TextBox20.Value
End If
If Me.OptionButton13 = True Then
    FindName = Me.TextBox21.Value & " " & Me.TextBox22.Value
End If
If Me.OptionButton14 = True Then
    FindName = Me.TextBox23.Value & " " & Me.TextBox24.Value
End If
If Me.OptionButton15 = True Then
    FindName = Me.TextBox25.Value & " " & Me.TextBox26.Value
End If
If Me.OptionButton16 = True Then
    FindName = Me.TextBox27.Value & " " & Me.TextBox28.Value
End If
If Me.OptionButton17 = True Then
    FindName = Me.TextBox29.Value & " " & Me.TextBox30.Value
End If
If Me.OptionButton18 = True Then
    FindName = Me.TextBox31.Value & " " & Me.TextBox32.Value
End If
If Me.OptionButton19 = True Then
    FindName = Me.TextBox33.Value & " " & Me.TextBox34.Value
End If
If Me.OptionButton20 = True Then
    FindName = Me.TextBox35.Value & " " & Me.TextBox36.Value
End If
If Me.OptionButton21 = True Then
    FindName = Me.TextBox37.Value & " " & Me.TextBox38.Value
End If
If Me.OptionButton22 = True Then
    FindName = Me.TextBox39.Value & " " & Me.TextBox40.Value
End If
If Me.OptionButton23 = True Then
    FindName = Me.TextBox41.Value & " " & Me.TextBox42.Value
End If
If Me.OptionButton24 = True Then
    FindName = Me.TextBox43.Value & " " & Me.TextBox44.Value
End If
If Me.OptionButton25 = True Then
    FindName = Me.TextBox45.Value & " " & Me.TextBox46.Value
End If
If Me.OptionButton26 = True Then
    FindName = Me.TextBox47.Value & " " & Me.TextBox48.Value
End If
If Me.OptionButton27 = True Then
    FindName = Me.TextBox49.Value & " " & Me.TextBox50.Value
End If
If Me.OptionButton28 = True Then
    FindName = Me.TextBox51.Value & " " & Me.TextBox52.Value
End If
If Me.OptionButton29 = True Then
    FindName = Me.TextBox53.Value & " " & Me.TextBox54.Value
End If
If Me.OptionButton30 = True Then
    FindName = Me.TextBox55.Value & " " & Me.TextBox56.Value
End If
FindNumber = Me.ComboBox1.Value
For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
ws.Visible = True
ws.Rows.Hidden = False
ws.Select
ws.Range("A:A").Select
     'On Error GoTo finish
        Set Found = ws.Range("A:A").Find(What:=FindNumber, After:=ws.Range("A3"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If Not Found Is Nothing Then
   
    Selection.FindNext(After:=ActiveCell).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Find(What:=FindName, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Select
        
    RowNum = ActiveCell.Row
    ColNum = ActiveCell.Column
    Cells(RowNum, ColNum + 88) = Box
    End If
 
Next
'end part that inputs the letter to Schedule Template
            For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
            ws.Visible = False
            Next
            Sheets("MyStoreInfo").Select
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
            
            ElseIf Cells(RowNum + r, ColNum + 1) = putName Then
            
            MsgBox ("You have already input that name here.")
            Range("Q9").Select
            Application.ScreenUpdating = True
            Exit Sub
    
            Else
            If r = 28 Then
                MsgBox ("No more room")
                Range("Q9").Select
                Application.ScreenUpdating = True
                Exit Sub
            End If
            
        End If
    Next
finish:
    Err.Clear
    For Each ws In Sheets(Array("Schedule Tool1", "Schedule Tool2", "Schedule Tool3", "Schedule Tool4", "Schedule Tool5"))
    ws.Visible = False
    Next
    Range("Q9").Select
    Application.ScreenUpdating = True
    MsgBox ("Date not found")
ExitTheSub:
MsgBox "Please Choose an Employee..."
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
sdoppke said:
Hi everyone, I have a user form that is part of a scheduling ws. The user form alows the user to
choose an employee from a combobox
choose a date from a combobox (had to go with combox, cause the filed doesnt have this object :( )
choose if its PTO or Off day (radio button)

It then runs though the script placing the approprate info on the scheduler and in a calendar etc..

My hopes is get help from you to modify the below code (sorry so long, thought it would be helpful) to allow me to use 2 comboboxes (for the dates) to do the same chain of events for each day in the range. i.e. If the first combobox sais 3-1-2012 and the second 3-5-2012, it will do al the events for each day in the range (in this case 5 times)

Thanks in advance for any direction. :)
Click to expand...

Hi everyone, any chance anyone would be able to provide some feeback/direction?

Thanks in advance.

sd
 
Upvote 0
sdoppke said:
Hi everyone, any chance anyone would be able to provide some feeback/direction?

Thanks in advance.

sd
Click to expand...

I was thinking I would need to add an additional drop down for the end date range and then somekind of "count" and "for each" coding to do the above steps for each date in the range, if that seems right to anyone (if not please course correct me :) ) would anyone have an idea how and where to add that into the code I have now?

Thanks very much for having a look.

sd
 
Upvote 0
sdoppke said:
I was thinking I would need to add an additional drop down for the end date range and then somekind of "count" and "for each" coding to do the above steps for each date in the range, if that seems right to anyone (if not please course correct me :) ) would anyone have an idea how and where to add that into the code I have now?

Thanks very much for having a look.

sd
Click to expand...


Still trying :) Has anyone had any experience with running a series of steps that are repeated over for a range of date, that maybe I could use to intigrate into my tool?

Thaks for any input.

sd
 
Upvote 0
sdoppke said:
Still trying :) Has anyone had any experience with running a series of steps that are repeated over for a range of date, that maybe I could use to intigrate into my tool?

Thaks for any input.

sd
Click to expand...


Sorry folks, i keep bringing this up, hoping a GURU will happen by and provide some direction. :)

sd
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Run Macro from command button in userform - display msgbox at the end of the macro
Replies
2
Views
2K
theprincipal78
T
S
  • Question Question
UserForm
2
Replies
10
Views
983
Norie
Norie

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top