Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 27 of 27

Thread: UserForm to Return the MAX date in a TextBox resulting from VLOOKUP in ComboBox

  1. #21
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,613
    The simpler the code the easier to detect errors

    Private Sub SchoolNameComboBox_Change()
      sn = Sheets("Future Appointments").Cells(1).CurrentRegion
    
      For j = 2 To UBound(sn)
        If sn(j, 1) = Schoolnamecombobox Then If sn(j, 6) > y Then y = sn(j, 6)
      Next
    
      ApptDateTextBox = IIf(IsEmpty(y), "", DateAdd("ww", Val(WeeklyIntervalTextBox), y))
    End Sub
    I'd also prefer simpler names of Userformcontrols:

    Private Sub C_00_Change()
      sn = Sheets("Future Appointments").Cells(1).CurrentRegion
    
      For j = 2 To UBound(sn)
        If sn(j, 1) = C_00 Then If sn(j, 6) > y Then y = sn(j, 6)
      Next
    
      T_00 = IIf(IsEmpty(y), "", DateAdd("ww", Val(T_01), y))
    End Sub
    Avoid any unnecessary feature like 'protection'.

    NB. Also a completely 'self taught' autodidact

  2. #22
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,964
    Location
    NextAppDate = DateAdd(MaxDate, ww, CLng(WeeklyIntervalTextBox))
    See P45cal's post # 14

    Replace everything from ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate) down with:
         NextAppDate = IIf(MaxDate = 0, "", MaxDate)
         If Not WeeklyIntervalTextBox = "" Then
               NextAppDate = DateAdd("ww", CDbl(WeeklyIntervalTextBox), NextAppDate)
         End If
    
         ApptDateTextBox.Text = NextAppDate
    End Sub
    Last edited by SamT; 10-22-2019 at 04:03 AM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #23
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,613
    @SamT

    The first argument in Dateadd is a string: "ww"
    See #21

  4. #24
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,964
    Location
    I knew that.
    All times are local. In my Time zone, I posted that at 04:35 AM
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #25
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    p45cal and SamT, thank you for your help but it still is not working. I am at the point where I am going to give up on this process and try and find another way to get the results I am trying to accomplish. I greatly appreciate all your help.

    Quote Originally Posted by SamT View Post
    See P45cal's post # 14
    I have poured over the doc that p45cal sent me, I guess I just don't understand what I am doing wrong.

    Quote Originally Posted by SamT View Post
    Replace everything from ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate) down with:
    I have also replaced everything as you suggested from ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate) down with:

     NextAppDate = IIf(MaxDate = 0, "", MaxDate)
         If Not WeeklyIntervalTextBox = "" Then
               NextAppDate = DateAdd("ww", CDbl(WeeklyIntervalTextBox), NextAppDate)
         End If
    
         ApptDateTextBox.Text = NextAppDate
    End Sub
    ... and it still is only returning the date of the currently scheduled most in the future, which was step one that you helped me with. This is what I have up to this point, but I am about to try and find a different way of doing this.

    Private Sub SchoolNameComboBox_Change()
    
    
            Dim SchoolNames  As Variant
            Dim SchoolDays As Variant
            Dim i As Long
            Dim MaxDate As Date
            Dim ThisName As String
            Dim NextAppDate As Date
            Dim Weeks As Double
            
                Sheets("Future Appointments").Select
                    ActiveSheet.Unprotect Password:="KingJesus1996"
                        ThisName = SchoolNameComboBox
                            With Worksheets("Future Appointments")
                                SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
                                SchoolDays = Intersect(.Range("A2").CurrentRegion, .Range("F:F"))  '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)
         If Not WeeklyIntervalTextBox = "" Then
               NextAppDate = DateAdd("ww", CDbl(WeeklyIntervalTextBox), NextAppDate)
         End If
    
    
         ApptDateTextBox.Text = NextAppDate
    
    
    End Sub
    Looking at the doc that p45cal sent me, I guess I am just over my head with this and cannot figure out if the order of arguments is correct or if the data type for each argument is correct.

    Either way, thank you for the help you have provided.
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  6. #26
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,964
    Location
    On the VBA menu >> Tools >> Options >> Editor tab, check all the boxes in the Code Settings frame. On the General tab, check Show Tool Tips, Notify before State Loss, Break on all Errors, and Compile on Demand.

    Click OK to exit that.

    In the VBA Editor, on the left side of the code page, there is a vertical bar, the same color as the menu bar. Left click that bar next to the Private Sub SchoolNameComboBox_Change. You just set a Breakpoint at that line. There should be a colored circle there, Click that circle to remove the Breakpoint

    Start the UserForm and change the value of SchoolNameComboBox.
    Now you should be looking at the Breakpointed line of code with that line highlighted, that highlight means that is the next line to be executed.

    Hover the mouse pointer over ThisName in the line ThisName = SchoolNameComboBox. A Tool Tip should pop up saying something to the effect of ="". Don't touch the mouse, press F8 three times, watching the highlight move down to the ThisName = SchoolNameComboBox line. At this time that line has not been executed. Press F8 one more time, Now the Tool Tip for ThisName should show the school you selected in SchoolNameComboBox.

    Now you have learned Breakpoints, F8, and Tooltips.

    Press F8 until For i = LBound(SchoolNames) + 1 To UBound(SchoolNames) is executed.

    Now hover the mouse over the i in the line For i = blah, blah. The Tooltip should be either 1 or 2 depending on some other settings. The first part of the line starting with If SchoolNames(i, 1) = ThisName Then should be highlighted. Hover over SchoolNames, then press F8. The Tooltip should read the first name in the list on the sheet. ThisName should always be the school name you selected in the Combobox.

    Depending on the condition of the If...Then, the highlighted line will be either MaxDate = Application.Max(MaxDate, SchoolDays(i, 1)) or Next i.

    In either case, set a Breakpoint at the Next i line. Press F5. this will make the code run until it hits a breakpoint, (Next i). IOW, it will run the loop once each time you press F5. Watch the Tooltip for SchoolNames and MaxDate. When SchoolNames = ThisName, MaxDate should change.

    You have now learned Breakpoints, F5, F8, Tooltips, stepping thru code, and watching variable values.

    Be sure to have a Breakpoint set at the line: NextAppDate = IIf(MaxDate = 0, "", MaxDate) so you know when the loop is done, else F5 will run thru the entire sub.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #27
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    Thank you SamT, p45cal, snb and everyone else that has taken time to look at this and assist me. I have been a little apprehensive to upload a sample workbook because in order to do that I would have to go through it to remove a massive amount of sensitive information yet still make it so this portion of the workbook would still function, and I knew that would take a while to do. I have done that now and I think the best thing is to maybe have someone look at it so you can see exactly what I am trying to accomplish. I am hoping it will make much more sense.

    Quote Originally Posted by SamT View Post
    On the VBA menu >> Tools >> Options >> Editor tab, check all the boxes in the Code Settings frame. On the General tab, check Show Tool Tips, Notify before State Loss, Break on all Errors, and Compile on Demand.
    I had most of these selected but selected all of the options you have here.

    Quote Originally Posted by SamT View Post
    Now you have learned Breakpoints, F8, and Tooltips.
    I am very familiar with breakpoints and stepping through code, I use that all the time but setting another breakpoint and using the F5 is new to me to go through loops.

    Quote Originally Posted by SamT View Post
    You have now learned Breakpoints, F5, F8, Tooltips, stepping thru code, and watching variable values.
    Watching the value of variables with tooltips is also new to me.


    There are multiple people that use their own version of this workbook that I developed and maintain for my company to schedule appointments at schools in their region, some of the people using it are not very knowledgeable about all things Excel. The purpose of this is to make it much easier to set multiple future appointments (usually scheduled once or twice once a month for a year in advance) at a user defined weekly interval with one entry on the UserForm instead of having to set each future appointment one at a time. Maybe I have some good ideas but they also may be overly difficult or too unrealistic, someone that knows better than I can let me know, however, I have done some pretty crazy things with VBA over the years even with my limited understanding of VBA (comparatively speaking). You will see several fields on the UserForm...


    • SchoolNameComboBox - This is where the school will be selected
    • -
    • ApptDateTextBox - This is where the future-most date will be returned once a school is selected (which currently works). This date should change if there is a number entered in WeeklyIntervalTextBox (see below)
    • -
    • ApptTimeTextBox - The user will enter a time for all of the appointments to be scheduled once the Enter button is pressed.
    • -
    • SchedStatusComboBox - The user will set a status for all of the appointments to be set here i.e. "Scheduled", "** Job Fair **, "** Meeting ** etc. This will be entered on all of the new appointments that are on the UserForm will set once the Enter button is pressed
    • -
    • DefaultNoteTextBox - The user will set a note for the appointments to be set here if they want to (optional field). This will be entered on all of the new appointments that are on the UserForm will set once the Enter button is pressed
    • -
    • NumberOfApptsTextBox - If there is a number entered here, AND there is a number entered in WeeklyIntervalTextBox then it will set that number of future appointments each at the interval set in WeeklyIntervalTextBox. If there is a number in NumberOfAppointmentsTextBox but no number in WeeklyIntervalTextBox, it will not set any appointments but maybe put up a Message Box saying they need to set an interval
    • -
    • WeeklyIntervalTextBox - If there is a number here, it will 1. advance the date that was already returned in ApptDateTextBox by that number of weeks so that is shows what the date of the NEXT appointment that is going to be set will be. 2. If there is a number in NumberOfApptsTextBox OR a date set in StopDateTextBox (see below) then it will create future appointments until one of the entry's is met.
    • -
    • StopDateTextBox - If there is a date set here, it will over-ride NumberOfAppointmentsTextBox and do a hard stop in setting appointments once the next appointment would exceed this date but will set all appointments until it reaches this date. In other words if the user enters 12 in NumberOfApptsTextBox and there is WeeklyIntervalTextBox of 6 (weeks) but there is a date of 3/30/2020 in StopDateTextBox it will set all appointments but will stop once the appointments would exceed 3/30/2020 because there are not 12 - 6 week intervals between today and 3/30/2020.


    Thank you everyone for all your help, it is GREATLY appreciated...
    Attached Files Attached Files
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •