TextBox to display date or "NA"

[AndyEd]

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.

 

[Akuini]

Could you show us your code?
How do you enter the date in textbox? by typing?

 

[Micron]

Not sure if it matters, but are these on a sheet or userform?
If using vba, test the contents of the textbox first and if it's a date, then format it. If it's not, then I guess skip that step. You're right - conversion functions will raise an error if you attempt to pass a value to it that cannot be converted.

 

[AndyEd]

The contents of the TextBox is dictated by a ComboBox selection, which displays the cell content by way of a VLookup. I'm displaying via a UserForm, because I can't think of another option. I eventually want to use some type of Form to provide a data entry method and then a way of searching the table for missing entries and displaying only the relevant records.

 

[Akuini]

The contents of the TextBox is dictated by a ComboBox selection, which displays the cell content by way of a VLookup. I'm displaying via a UserForm

Can you show us the code on this part? and also where the code format the textbox as date

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.

Maybe something like this:

Private Sub CommandButton1_Click()
If IsDate(Range("A1")) Then
    TextBox1 = Format(Range("A1"), "dd-mm-yyyy")
Else
    TextBox1 = Range("A1")
End If
End Sub

Basically, format the value before you insert it into the textbox, not after.

 

[AndyEd]

The value of the respective cell is displayed via the following code.

Private Sub cbx1_Change()

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

End sub

I can then manipulate the TextBox later, via it's own Change event, or via the one above, e.g. changing BackColor etc. However I can't seem to be able to code for it to display either Blank, "NA" or the relevant date, in "dd/mm/yyyy" format.

I've tried the above, substituting the cell reference for TextBox.Value but still can't get it to work. I've tried to code that if if it displays "NA" to leave display as such, but then format the date if it is a date, but the moment I format it errors out on the first "NA" it encounters.

 

[Akuini]

However I can't seem to be able to code for it to display either Blank, "NA" or the relevant date, in "dd/mm/yyyy" format.

What is the code?

 

[AndyEd]

Sorry, that is it, I haven't got any code,

I've tried,

if .tbx1.value = "NA" then
.tbx1.value=.tbx.value
else
.tbx1.value=CDate(Application.WorksheetFunction.VLookup(CLng(Me.cbx1), Worksheets("Tracker").Range("A:BW"), 6, 0)
end if

but that doesn't work.

I've tried it in the cbx1 & tbx1 Change event - neither works.

Tried it in a Before Update event, again, didn't work.

 

[Akuini]

What's in the combobox list? just date? where is "NA" come from?

 

[AndyEd]

The combobox provides a list of references in column A3 through to the last row, for the user to select.

The textboxes then populate from corresponding columns in the reference sheet, based on the Vlookup column reference.

The values in the sheet are either 'Blank', "NA", or a date.