Excel changing date format after VB has run

[Richhall]

Hi

I have a sheet 1 with a combo box and a blank field. When something
is selected from the combo box a date gets populated in the blank
field. The date is custom formatted as mmm-yyyy.

On sheet 2 I have a table with the options in the drop down box and a
date next to them. When a selection is done, the code does a VLOOKUP
and populates the data in Sheet 1 in the blank field. Dates are also
mmm-yyyy in the Sheet 2.

The problem is, say the date in Sheet 2 is 01/12/2010 displayed as
Dec-2010. When it is put into sheet w, it flips it to 12/01/2010 so
says Jan-2010. Any idea why this is happening please?

Code Below - VersionRange is a named Range ($C$4:$F$56) in Sheet 2

Dim boxdate
Dim VerValue
Dim VerRange
Dim ws As Worksheet

Set ws = Worksheets("Sheet 2")
VerValue = Combo1.Text
VerRange = ws.Range("VersionRange")

If VerValue = "" Then
boxdate= ""
Else: boxdate = Application.WorksheetFunction.VLookup(VerValue,
VerRange, 2)
End If

Range("A19").Select
ActiveCell.FormulaR1C1 = boxdate


Thank you

Rich

 

[Richhall]

Also, I have tried changing the fields to text but then the VLOOKUP
wont work.

 

[Dave Peterson]

I'd try:

with Range("A19")
'an unambiguous date format for testing
.numberformat = "mmmm dd, yyyy"
.value = clng(boxdate)
end with

 

[Richhall]

Seems it was something to do with the VLOOKUP, dates are fine now as I
added:

Set VerRange = Worksheets("Sheet 2").Range("VersionRange")

Everything seemed to start working ok.

Thanks anyway. Eventually I just added the VLOOKUP into the cell
using an Admin sheet with the VerValue set in a cell there.

Cheers

Rich