TextBox to display date or "NA"

A

AndyEd

Board Regular
Joined
May 13, 2020
Messages
124
Office Version
  1. 365
Platform
  1. Windows
I have a number of TextBoxes that either display a Date, remain blank, or display "NA" if a date is not required.

They are three distinct options - 1) Date required and present; 2) Date required and missing (Blank); 3) Date not required ("NA")

I've formatted the TextBox to highlight it if it is blank (requires an entry).

The issue I have is that I'm using Format(CDate(TextBox),"dd/mm/yy") to display the date and of course it is finding a error as soon as it encounters an "NA". If I don't format the code to display a date, it is displaying the numerical code instead. I've tried various "if" functions in the "Change" sub for the TextBox, but nothing seems to have the desired effect.

I'm not sure where I'm going wrong.
 
Sort by date Sort by votes
AndyEd said:
The values in the sheet are either 'Blank', "NA", or a date.
Click to expand...
So, when you pick an item in the combobox it will get a value from the sheet which could be 'Blank', "NA", or a date?
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Yes - the corresponding value 'Blank', "NA", or date is displayed in an appropriate textbox.
 
Upvote 0
Try this:
VBA Code: 
Private Sub cbx1_Change()

Dim v

v = Application.WorksheetFunction.VLookup(CLng(Me.cbx1), Worksheets("Tracker").Range("A:BW"), 6, 0)

If IsDate(v) Then
    tbx1.Value = Format(v, "dd-mm-yyyy")
Else
    tbx1.Value = v
End If

End Sub

If it doesn't work then I'll need a sample workbook.
You can upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive.
And then share the link here.
 
Upvote 0
File detailsCriteria 9.3
RefFirst Name(s)Last NameWhoRequestedDueReceivedComments
1First Name 1Last Name 1NANANANA
2First Name 2Last Name 2NANANANA
3First Name 3Last Name 3NANANANA
4First Name 4Last Name 4NANANANA
5First Name 5Last Name 5NANANANA
6First Name 6Last Name 6NANANANA
7First Name 7Last Name 7NANANANA
8First Name 8Last Name 8NANANANA
9First Name 9Last Name 9
10First Name 10Last Name 10
11First Name 11Last Name 11NANANANA
12First Name 12Last Name 12NANANANA
13First Name 13Last Name 13NANANANA
14First Name 14Last Name 14
15First Name 15Last Name 15NANANANA
16First Name 16Last Name 16NANANANA
17First Name 17Last Name 17NANANANA
18First Name 18Last Name 18NANANANA
19First Name 19Last Name 19NANANANA
20First Name 20Last Name 20
 
Upvote 0
File detailsCriteriaCriteria ACriteria BCriteria 1Criteria 2Criteria 3Criteria 4Criteria 5Criteria 6Criteria 7Criteria 8.1Criteria 8.2Criteria 8.3Criteria 9.1Criteria 9.2Criteria 9.3
RefFirst Name(s)Last NameFile typeFile statusDate of somethingCriteria 1Criteria 2Criteria 3Criteria 4Criteria 5Criteria 6Criteria 7Criteria 8Criteria 9HappenedDateOption1Option2Person APerson A report receivedPerson BPerson B report receivedRequestedReceivedCompletedRequestedDueReceivedRequestedDueReceivedRequestedDueReceivedWhoRequestedDueReceivedRequestedDueReceivedWhoRequestedDueReceivedAvailableRequestedDueReceivedWhoRequestedDueReceivedWhoRequestedDueReceivedWhoRequestedDueReceivedWhoRequestedDueReceivedWhoRequestedDueReceivedWhoRequestedDueReceivedComments
1First Name 1Last Name 1OpenNewNANoNoNoNoNoNoNoNoNoYes08/06/2023NoYesPerson 1NANANANANANANANANANANANANANANANANANANANANANANAYesNANANANANANANANANANANANANANANANANANANANANANANANA
2First Name 2Last Name 2ClosedOldNANoNoNoNoNoNoNoNoNoYes08/12/2022NoYesPerson 1Person 104/01/2023NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
3First Name 3Last Name 3PendingNew16/05/2023NoNoNoNoNoYesNoYesNoNoNANANANANANANANANANANANANANANANANANANANANANANANANANAPerson ANoNANANAThis one12/06/202324/07/2023NANANANANANANANANANANANANANANANANANANANA
4First Name 4Last Name 4OpenNew11/04/2023YesNoNoNoNoYesNoNoNoYes29/03/2023YesYesPerson 302/06/2023Person 224/04/2023NANANA25/04/202325/05/2023NANANANANANANANANANANANANAPerson B25/04/202331/05/202314/06/2023NANANANANANANANANANANANANANANANANANANANANANANANA
5First Name 5Last Name 5ClosedNewNANoNoNoNoNoNoNoNoNoYes06/06/2023NoYesPerson 1NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
6First Name 6Last Name 6PendingNew13/01/2023YesNoNoYesNoYesNoNoNoNoNANANANANANANANANANA25/01/201228/02/202330/01/2023NANANANANANAThis one03/05/202331/05/2023NANANAPerson C25/01/202301/03/2023Yes19/06/202319/07/2023NANANANANANANANANANANANANANANANANANANANA
7First Name 7Last Name 7OpenNew21/10/2022NoNoNoNoNoNoNoNoNoYes19/10/2022YesYesPerson 120/10/2022NANANA28/10/202228/11/2022NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
8First Name 8Last Name 8ClosedNewNANoNoNoNoNoNoNoNoNoYes19/10/2022YesNoPerson 2NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
9First Name 9Last Name 9PendingNew18/04/2023NoNoNoYesNoYesNoYesYesYes12/04/2023NoYesPerson 225/04/2023Person 124/04/2023NANANANANANANANANANANANAThat one07/06/202307/06/2023NANANAPerson D07/06/202312/07/2023NANANANANANANANAThis one08/06/202306/07/2023
10First Name 10Last Name 10OpenOld06/01/2023YesNoNoYesNoNoNoYesYesNoNANANANANANANANANANA20/01/202320/02/202323/01/2023NANANANANANA20/01/202328/02/202328/03/2023NANANANANANANANANANANAThat one20/01/202328/02/202308/02/2023NANANANANANANANAFire
11First Name 11Last Name 11ClosedOld10/01/2023YesNoNoNoNoYesNoYesNoNoNANANANANANANANANANA27/01/202315/03/202316/03/2023NANANANANANANANANANANANANAPerson E27/01/202327/05/2023NANANANAThat one27/01/202327/05/2023This one27/01/202327/05/202314/06/2023NANANANANANANANANANANANA
12First Name 12Last Name 12PendingOldNANoNoNoNoNoNoNoNoNoYes09/05/2023YesYesPerson 2Person 308/06/2023NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
13First Name 13Last Name 13OpenOld11/04/2023YesNoNoNoNoYesNoYesNoYes05/04/2023NoYesPerson 3Person 224/04/2023NANANA20/04/202320/05/202325/04/2023NANANANANANANANANANANANANAPerson A20/04/202330/05/2023NANANANAThe other20/04/202330/05/2023NANANANANANANANANANANANA
14First Name 14Last Name 14ClosedNew
15First Name 15Last Name 15PendingNewNANoNoNoNoNoNoNoNoNoYes24/10/2022YesNoPerson 3NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
16First Name 16Last Name 16OpenNew17/02/2023YesNoNoNoYesYesNoNoNoYes25/01/2023YesYesPerson 3Person 214/04/202319/06/202319/07/2023NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
17First Name 17Last Name 17ClosedNew31/05/2023YesNoNoNoYesYesNoNoYesYes25/05/2023NoNoPerson 3NANANANANA04/06/202317/07/2023NANANANANANANANANANA04/06/202317/07/202305/06/2023Person F04/06/202317/07/2023NANANANANANANANANANANANAThat one04/06/202317/07/2023This one04/06/202317/07/2023NANANANA
18First Name 18Last Name 18PendingNewNANoNoNoNoNoNoNoNoNoYes27/09/2022YesYesPerson 2Person 210/10/2022NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
19First Name 19Last Name 19OpenOldNANoNoNoNoNoNoNoNoNoYes02/05/2023NoYesPerson 2Person 325/05/2023NANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANANA
20First Name 20Last Name 20ClosedNew21/07/2022YesNoNoYesYesYesNoNoYesYes21/07/2022YesYesPerson 105/05/2023Person 312/08/2022NANANA28/07/202229/08/2022NANANANANANAThe other28/07/202229/08/202220/10/202201/06/202301/07/2023Person G28/07/202229/08/202214/10/2022NANANANANANANANANANANANANANANANA
 
Upvote 0
VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()

    With Me
        .Top = Application.Top
        .Left = Application.Left
        .Height = Application.Height
        .Width = Application.Width
    End With
    
    With Me.cbxSelectPerson
        '.List = Application.Transpose(Sheets("Tracker").ListObjects(1).DataBodyRange)
        .RowSource = "OFFSET(tracker!$A$2,1,0,COUNTA(tracker!$A:$A)-1,3)"
        .ColumnCount = 3
        .ColumnWidths = "80pt;150pt;100pt"
        .SetFocus
    End With
    
End Sub

Private Sub cbxSelectPerson_Change()

    With Me

        .tbxFirstNames.Value = StrConv(Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 2, 0), vbProperCase)
        
        .tbxLastName.Value = UCase(Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 3, 0))
        
        .tbxFiletype.Value = StrConv(Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 4, 0), vbProperCase)
        
        .tbxFileStatus.Value = StrConv(Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 5, 0), vbProperCase)
        
        .tbxDateOfSomething.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 6, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxHappened.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 16, 0)
        
        .tbxDate.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 17, 0)
        
        .tbxOption1.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 18, 0)
        
        .tbxOption2.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 19, 0)

        .tbxPersonA.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 20, 0)

        .tbxPersonAReportReceived.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 21, 0)

        .tbxPersonB.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 22, 0)

        .tbxPersonBReportReceived.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 23, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria1.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 7, 0)
        
        .tbxCriteria2.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 8, 0)
        
        .tbxCriteria3.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 9, 0)
        
        .tbxCriteria4.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 10, 0)
      
        .tbxCriteria5.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 11, 0)
        
        .tbxCriteria6.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 12, 0)
        
        .tbxCriteria7.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 13, 0)
        
        .tbxCriteria8.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 14, 0)
        
        .tbxCriteria9.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 15, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX
      
        .tbxCriteriaBRequested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 24, 0)
      
        .tbxCriteriaBReceived.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 25, 0)
        
        .tbxCriteriaBCompleted.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 26, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX
      
        .tbxCriteria1Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 27, 0)
      
        .tbxCriteria1Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 28, 0)
        
        .tbxCriteria1Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 29, 0)
      
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX
        
        .tbxCriteria2Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 30, 0)
      
        .tbxCriteria2Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 31, 0)
        
        .tbxCriteria2Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 32, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria3Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 33, 0)
      
        .tbxCriteria3Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 34, 0)
      
        .tbxCriteria3Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 35, 0)
      
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria5Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 40, 0)
      
        .tbxCriteria5Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 41, 0)
      
        .tbxCriteria5Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 42, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria4Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 36, 0)
      
        .tbxCriteria4Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 37, 0)
      
        .tbxCriteria4Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 38, 0)
      
        .tbxCriteria4Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 39, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria6Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 43, 0)
      
        .tbxCriteria6Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 44, 0)
      
        .tbxCriteria6Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 45, 0)
      
        .tbxCriteria6Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 46, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria7Available.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 47, 0)
      
        .tbxCriteria7Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 48, 0)
      
        .tbxCriteria7Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 49, 0)
      
        .tbxCriteria7Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 50, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria81Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 51, 0)
      
        .tbxCriteria81Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 52, 0)
      
        .tbxCriteria81Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 53, 0)
      
        .tbxCriteria81Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 54, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria82Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 55, 0)
      
        .tbxCriteria82Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 56, 0)
      
        .tbxCriteria82Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 57, 0)
      
        .tbxCriteria82Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 58, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria83Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 59, 0)
      
        .tbxCriteria83Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 60, 0)
      
        .tbxCriteria83Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 61, 0)
      
        .tbxCriteria83Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 62, 0)
        
        ' XXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria91Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 63, 0)
      
        .tbxCriteria91Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 64, 0)
      
        .tbxCriteria91Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 65, 0)
      
        .tbxCriteria91Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 66, 0)
      
      ' XXXXXXXXXXXXXXXXXXXXXXXXXXX
 
        .tbxCriteria92Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 67, 0)
      
        .tbxCriteria92Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 68, 0)
      
        .tbxCriteria92Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 69, 0)
      
        .tbxCriteria92Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 70, 0)
    
        ' XXXXXXXXXXXXXXXXXXXXXXXXX

        .tbxCriteria93Who.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 71, 0)
      
        .tbxCriteria93Requested.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 72, 0)
      
        .tbxCriteria93Due.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 73, 0)
      
        .tbxCriteria93Received.Value = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 74, 0)
      
    End With
    
End Sub
 
Upvote 0
OK, thanks for the showing us the code & data.
My code in post #13 is for the combobox change event not the textbox. Did you try that?
So the textbox is populated & formatted when you pick an item in the combobox.
 
Upvote 0
Sorry, you did use combobox change event in Sub cbxSelectPerson_Change().
But I meant you need to do it on each textbox that receive date value. I don't know which which ones, but let's say it's tbxCriteriaBReceived.
So try it on this textbox first, like this:

VBA Code: 
Private Sub cbxSelectPerson_Change()

Dim v

v = Application.WorksheetFunction.VLookup(CLng(Me.cbxSelectPerson), _
        Worksheets("Tracker").Range("A:BW"), 25, 0)
   
    With Me

            If IsDate(v) Then
                 .tbxCriteriaBReceived.Value = Format(v, "dd-mm-yyyy")
            Else
                 .tbxCriteriaBReceived.Value = v
            End If

    End With

End Sub

if it works then maybe we can apply it on other textboxes by using a Function so we can shorten the code.
How many textboxes are there?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Exiting a Textbox Problem
2
Replies
10
Views
695
Pacman52
P
P
  • Solved
Loading sheet row data to a userform issue.
Replies
7
Views
277
DanteAmor
DanteAmor
B
  • Solved
changing date format
Replies
5
Views
186
NoSparks
N
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
410
Peter_SSs
Peter_SSs
D
  • Solved
Date Issues with User Forms
Replies
4
Views
206
dmt32
D

Forum statistics

Threads
1,224,574
Messages
6,179,633
Members
452,933
Latest member
patv

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