Combobox date value as lookup value, populate to textbox on Userform

[pric9331]

Greetings,

I’m using excel 2013, version 15. I’ve designed a userform (frmDataInput) where the user selects a week-ending date from a combobox (WEBox). The values for the combobox are located in worksheet 8, column I.


What I’m trying to do is populate a text box (DateBox1) with the corresponding value in column C as selected by a given value in WEBox. So, if a user selects 1/3/2015 in WEBox, Datebox1, (also on the userform) should display 12/28/2014.

Below is the code I'm using to try to search "sheet8" using the selected WEBox value, and populate the DateBox1 with the value found (on the change event for WEBox)

Private Sub WEBox_Change()

    WEBox.Value = Format(WEBox.Value, "mm/dd/yyyy")
    

    Dim strFind As String
    Dim rFound As Range
    
    If WEBox.ListIndex > -1 Then
        strFind = WEBox
        On Error Resume Next
        With Sheet8.Columns(5)
            Set rFound = .Find(What:=".xls", After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            
        End With
        
        If rFound Is Nothing Then
            MsgBox strFind & "cannot be found"
                Exit Sub
        Else
            DateBox1 = rFound(1, 1)
        End If
        
    End If

Thank you in advance for any and all help!

 

[pric9331]

Hi All,

Thread [RESOLVED]

Thank you for viewing this thread. I found a workaround. I placed the lookup values for the combobox in Column A, and manually changed the date to display as text (instead of using the excel date format, I inserted a ' in front of the date and the textbox displayed the appropriate value based on the combobox selection.)


I found this thread to be helpful with my issue:

Populate textbox using combobox info