PDA

View Full Version : UserForm to Return the MAX date in a TextBox resulting from VLOOKUP in ComboBox



infinity
10-19-2019, 07:04 PM
Hi VBaxers,

I have a UserForm that has a ComboBox and Several TextBoxes. One TextBox returns the date from a VLOOKUP of the entry in the ComboBox on the UserForm. This actually works great as far as I have gotten but I am having a few problems with expanding it.


Private Sub SchoolNameComboBox_Change()
Dim nextApptDate
nextApptDate = WorksheetFunction.VLookup(SchoolNameComboBox.Value, Worksheets("Future Appointments").Range("A2:J2500"), 6, False)
ApptDateTextBox.Value = nextApptDate
If Not IsDate(Me.ApptDateTextBox.Text) Then
Me.ApptDateTextBox.Text = Format(Me.ApptDateTextBox.Text, "m/d/yyyy")
End If
End Sub


Problem # 1 - Currently this returns the first result from the VLOOKUP (which is a date) but because there may be multiple entry's for the selected value in the ComboBox I need it to return the date that is most in the future.

Problem # 2 - If the VLOOKUP finds a match I want it to return the MAX date but I also have another TextBox on the UserForm that the user will enter a number, this number will represent a number of weeks to advance the date returned i.e. if the VLOOKUP returns 11/1/2019 and there is a value in the other TextBox of 4 then I want the returned value to be 11/1/2019 + 28 days (4 weeks) which would be a result of 11/28/2019.

Problem # 3 - If the user selects from the ComboBox and but the VLOOKUP cannot find a match I want the value in the ApptDateTextBox to be blank, currently it is giving an error.

Thank you so much for any help... you guys have taught me sooooo much over the years and I cannot articulate my appreciation enough!

SamT
10-20-2019, 01:29 AM
Problem # 1 - Currently this returns the first result from the VLOOKUP (which is a date) but because there may be multiple entry's for the selected value in the ComboBox I need it to return the date that is most in the future.VLookup won't do multiple matches


Problem # 2 - If the VLOOKUP finds a match I want it to return the MAX date You can use Find...Findnext, but IMO, arrays are better in your situation
Dim SchoolNames As Variant
Dim SchoolDays As Variant
Dim i As long
Dim MaxDate As Date
Dim ThisName As String

ThisName = SchoolNameComboBox

With Worksheets("Future Appointments")
SchoolNames = .Range(Intersect(.Range("A2").CurrentRegion, .Range("A:A")))
SchoolDays = .Range(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) = ThisName Then MaxDate = Max(MaxDate, SchoolDays(i))
Next i

'If MaxDate = 0 then No Match found



Problem # 2 - I also have another TextBox on the UserForm that the user will enter a number, this number will represent a number of weeks to advance the date returned
In Microsoft Office, there are differences between Days, Weeks and Months. So 7 days might really be 7 workdays, 4 weeks might not be one month. Google "Excel VBA DateAdd" for more info.

infinity
10-20-2019, 09:46 AM
If SchoolNames(i) = ThisName Then MaxDate = Max(MaxDate, SchoolDays(i))

In Microsoft Office, there are differences between Days, Weeks and Months. So 7 days might really be 7 workdays, 4 weeks might not be one month. Google "Excel VBA DateAdd" for more info.

Hi SamT,

Thank you for your reply, much appreciated! I tried this code but got an error that said "Compile Error Sub of Function not defined" and the "Max" was highlighted in the quoted code above. Not sure what that means... Also, I understand the issues with days you mentioned. What I want to do is whatever date is returned, if there is a number in the other TextBox, take that number *7 and add that to the date returned. What this should do is give what the next appointment date would be after finding the future date already entered on the worksheet then adding the interval entered in weeks. I hope that makes sense.

p45cal
10-20-2019, 10:35 AM
Max
needs to be:
Application.Max

infinity
10-20-2019, 11:08 AM
Thank you p45cal, I have changed that but now I am getting a Run-time error '1004' that says "Application-defined or object-defined error" the portion of code is highlighted...


SchoolNames = .Range(Intersect(.Range("A2").CurrentRegion, .Range("A:A")))

p45cal
10-20-2019, 12:24 PM
reduce it to
SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
(and similar with the following line where also "F"F" should be "F:F").

Also the following needs the red additions:
If SchoolNames(i, 1) = ThisName Then MaxDate = Application.Max(MaxDate, SchoolDays(i, 1))

infinity
10-20-2019, 02:48 PM
p45cal,

I made the changes and now I am no loner getting the errors but it also is not pulling in any date in the ApptDateTextBox. I am posting all of the code I have so far. I have this entered in the UserForms SchoolNameComboBox_Change Event, is that correct?


Private Sub SchoolNameComboBox_Change()
Dim SchoolNames As Variant
Dim SchoolDays As Variant
Dim i As Long
Dim MaxDate As Date
Dim ThisName As String
ThisName = SchoolNameComboBox
With Worksheets("Future Appointments")
SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
SchoolDays = Intersect(.Range("F2").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
'If MaxDate = 0 then No Match found
End Sub


Thank you for your help!

p45cal
10-20-2019, 04:14 PM
Finish it with the likes of:

ApptDateTextBox.text = iif(MaxDate=0,"",MaxDate)

(Untested)

SamT
10-20-2019, 04:35 PM
I made the changes and now I am no loner getting the errors but it also is not pulling in any date in the ApptDateTextBox.Arrgh! :crying:

When you see what that code is doing, you will see the answer.


What this should do is give what the next appointment date would be after finding the future date already entered on the worksheet then adding the interval entered in weeks.


Dim NextAppDate As Date

If TextBoxOther = "" Them
NextAppDate = maxDate
Else
NextAppDate = DateAdd(MaxDate, ww, Clng(TextBoxOther)
End If


If you see what that code does, then you can find the hint, the typo, and the error in it by yourself. One each.

infinity
10-20-2019, 04:38 PM
Finish it with the likes of:
ApptDateTextBox.text = iif(MaxDate=0,"",MaxDate)

(Untested)


THAT DID IT!!! Thank you very much! However I have no idea what the "IIF" is, that is the first time I have seen that. Either way, thank you for your help!

infinity
10-20-2019, 06:44 PM
Arrgh! :crying:

When you see what that code is doing, you will see the answer.




Dim NextAppDate As Date

If TextBoxOther = "" Them
NextAppDate = maxDate
Else
NextAppDate = DateAdd(MaxDate, ww, Clng(TextBoxOther)
End If


I tried this but entering a number in the other TextBox does not advance the date in ApptDateTextBox. What I want it to do is take the number that is entered in WeeklyIntervalTextBox and multiply it by 7 then add it to the date that was returned. By doing this, as an example, if 11/1/2019 is returned in ApptDateTextBox and "4" is entered in WeeklyIntervalTextBox then I want to take the "4" and multiply it by 7 (for days in the week, which would be 28) then add that 28 to the date returned in ApptDateTextBox which would give a final result of 11/29/2019. Also out of curiosity, what is the "ww" and "Clng"? Thank you for your help.


NextAppDate = DateAdd(MaxDate, ww, Clng(weeklyintervaltextbox)

SamT
10-20-2019, 10:37 PM
ww= in DateAdd indicates to add weeks
Clng converts the number as text, (the TextBox value,) into a numerical value


I tried this but entering a number in the other TextBox does not advance the date in ApptDateTextBox.Of course not, there is nothing in that bit of code that references AppTextBox.


"4" is entered in WeeklyIntervalTextBox then I want to take the "4" and multiply it by 7 (for days in the week, which would be 28)
Do you want to add days or weeks to the appointment date? There is a difference.

DateAdd(MaxDate, ww, Clng(WeeklyIntervalTextBox))Adds weeks



DateAdd(MaxDate, d, Clng(WeeklyIntervalTextBox) * 7)Adds days

snb
10-21-2019, 01:07 AM
Count the brackets:


NextAppDate = DateAdd(MaxDate, ww, Clng(TextBoxOther)

p45cal
10-21-2019, 02:45 AM
Do check out the help on DateAdd (https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateadd-function ) and note especially the order in which the arguments should appear and the data type of each argument.
You may find it easier to just add 7 times what's in the other TextBox to MaxDate.
25319

What is the name of this other TextBox?

infinity
10-21-2019, 07:35 AM
p45cal, I will certainly check out the help doc you sent me when I get home tonight. Thank you! I have found so many things on the web, I got confused with it. I am pretty new to InputBox and while I get Arrays in Excel, I am extremely new to arrays in VBA. But just like everything else that Vbaxers have taught me over the last 14 years, this too I will get! You especially have been very helpful over the years so thank you.


What is the name of this other TextBox?

The other TextBox is Named WeeklyIntervalTextBox.


You may find it easier to just add 7 times what's in the other TextBox to MaxDate.

That is exactly what I want to do because each appointment is going to be on the same day of the week so the number of weeks x the value entered in WeeklyIntervalTextBox would give me the next appointment, on the same day of the week but that number of weeks into the future.

infinity
10-21-2019, 07:42 AM
ww= in DateAdd indicates to add weeks
Clng converts the number as text, (the TextBox value,) into a numerical value

Thank you for that clarification.


Of course not, there is nothing in that bit of code that references AppTextBox.

I am guessing here but... that might help??? The date that is returned from the Array from the earlier post goes into ApptDateTextBox, the value for weeks to advance that date is entered in WeeklyIntervalTextBox. So when the lookup returns the futuremost date for the school (from earlier posts) that date is returned in ApptDateTextBox, however if there is a value entered in WeeklyIntervalTextBox, then I want that date to be advanced by (WeeklyIntervalTextBox * 7)



Do you want to add days or weeks to the appointment date? There is a difference.

I definitely want to add days rather than weeks, it is just easier for the user to enter the number of weeks and allow the code to take it from there.


DateAdd(MaxDate, d, Clng(WeeklyIntervalTextBox) * 7)

I will give this a shot once I get home from work today, thank you very much for your help.

p45cal
10-21-2019, 10:45 AM
Add

Dim Weeks As Double
at the top then at the bottom:

On Error Resume Next
Weeks = CLng(WeeklyIntervalTextBox.Text)
On Error GoTo 0
ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate + Weeks * 7)
(If you put a negative number in you'll get earlier dates.)

SamT
10-21-2019, 12:25 PM
Thank P45Cal for catching these syntax errors.

DateAdd("d", Clng(WeeklyIntervalTextBox) * 7, MaxDate)
And

DateAdd("ww", Clng(WeeklyIntervalTextBox), MaxDate)
Will give the same result.

I always expect the student to do their homework and find all the errrors I leeve in.

infinity
10-21-2019, 09:39 PM
OK, so this is what I have done and it still is not working... I have to apologize, I really am not trying to waste anyone's time but I simply do not understand what I am doing wrong, like I said earlier, I am pretty new to InputBox and very new to Arrays in VBA... what am I doing wrong???


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:=""
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
ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate)
If WeeklyIntervalTextBox = "" Then
NextAppDate = MaxDate
Else
NextAppDate = DateAdd(MaxDate, ww, CLng(WeeklyIntervalTextBox))
End If
On Error Resume Next
Weeks = CLng(WeeklyIntervalTextBox.Text)
On Error GoTo 0
ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate + Weeks * 7)
End Sub


SamT - I really do appreciate your help and I am very good at finding errrors that others leeve in grammar and spelling but with coding, I do not do this for a living and am completely self taught with A LOT of help from all of you at VBAExpress, I couldn't do the things with Excel that I do without everyones help so thank you.

p45cal
10-22-2019, 12:06 AM
I refer you to the first sentence of msg#14.

snb
10-22-2019, 01:08 AM
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

SamT
10-22-2019, 02:35 AM
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

snb
10-22-2019, 02:52 AM
@SamT

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

SamT
10-22-2019, 04:05 AM
I knew that.
All times are local. In my Time zone, I posted that at 04:35 AM

infinity
10-22-2019, 06:29 PM
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.


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.


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. :banghead:

Either way, thank you for the help you have provided.

SamT
10-22-2019, 09:56 PM
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.

infinity
10-23-2019, 08:06 PM
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.


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.


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.


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...