Consulting

Results 1 to 20 of 27

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    95
    Location

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

    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!
    Last edited by Aussiebear; 04-21-2023 at 06:33 PM. Reason: Reduced the whitespace

Posting Permissions

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