to come cell value in textbox as it is

[ajaar]

HI Friends,

We do restrict the textbox to accept only text or date...etc. my case is different, i wanted to come cell value in the textbox as it is. I have below code to bring the lookup value in the textbox, some time value will be date and time, some time it is text.
alofr is the name of text box. arrno is the value in the compobox,.

Appreciate your help

Private Sub arrno_Change
alofr.Value = Application.VLookup(Val(arrno.Value), Sheets("Data").Range("f3:ab25000"), 4, False)
End sub

 

[prasaddn]

Hi,

You can store the lookup value in one variable (to avoid multiple times lookup function) and check if its a date, if yes apply the formatting and if not show as it is.

see below code:

  If VBA.IsDate(Range("A1").Value) Then
  TextBox1.Value = VBA.Format(Range("A1").Value, "dd-mm-yyyy")
  Else
  TextBox1.Value = Range("A1").Value
  End If

regards,
Prasad DN

 

[ajaar]

Thanks Prasad. Appreciate your help.