VBA - Help with form date formatting

[anthony85]

Hi,

I adapted a user form (with permission ) which has been working really well until I discovered the issue with UK and US date formatting.

In brief, the form has many text boxes which is used to enter data into a spreadsheet. What I noticed on the 01/12/16 is that excel would switch the date around to 12/01/16 and enter that into the spreadsheet. I got around that by editing the VBA to format the text boxes into UK style dates before adding to the spreadsheet.

After the data is added, I have a lookup function on the form where it re-calls the rows data by a search function and populates the data into the relevant text boxes. At this point excel is looking at the dates in the spreadsheet and switching them to US format. I've tried adding formatting into the VBA again at this point but it doesn't work, I guess because it's already pulled the data in.

I'm wondering if I could please ask for someone's help to see if there is a fix in the short bit of VBA shown below rather than reworking the whole thing.

To explain some of the elements to the code. Cnum = 39 columns in total, text boxes are labelled reg1, reg2 and so on.

Thanks

Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'declare the variables
Dim cVRM As String
Dim I As Integer
Dim findvalue
'error block
On Error GoTo errHandler:
'get the select value from the listbox
For I = 0 To lstLookup.ListCount - 1
If lstLookup.Selected(I) = True Then
cVRM = lstLookup.List(I, 0)
End If
Next I
'find the VRM
Set findvalue = Sheet2.Range("E:E").Find(What:=cVRM, LookIn:=xlValues).Offset(0, -2)
'add the database values to the userform
cNum = 39
For X = 1 To cNum
Me.Controls("Reg" & X).Value = findvalue
Set findvalue = findvalue.Offset(0, 1)
Next
'disable adding
Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

 

[AlphaFrog]

Try this...

[color=darkblue]Set[/color] findvalue = Sheet2.Range("E:E").Find(What:=DateValue(cVRM), _                                         LookIn:=xlFormulas, _
                                         LookAt:=xlWhole, _
                                         SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=False).Offset(0, -2)

 

[anthony85]

Hi,

I tried that but it didn't work. It returned the error Handler message. Just to mentioned though the VRM reference it is searching for isn't in date format. Its text. The dates are within its row of data.

Try this...

[COLOR=darkblue]Set[/COLOR] findvalue = Sheet2.Range("E:E").Find(What:=DateValue(cVRM), _                                         LookIn:=xlFormulas, _
                                         LookAt:=xlWhole, _
                                         SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=False).Offset(0, -2)

 

[AlphaFrog]

Just to mentioned though the VRM reference it is searching for isn't in date format. Its text. The dates are within its row of data.

Can you better explain what exactly does that mean? Perhaps a couple of examples.

 

[anthony85]

Hi,

I tried that but it didn't work. It returned the error Handler message. Just to mentioned though the VRM reference it is searching for isn't in date format. Its text. The dates are within its row of data.

Ok I'll try my best.

I'm using a form to store data about vehicles we have on site here. The first step I take is to open the form, add the VRM (vehicle reference) and then add data to many other text boxes on the form. Many of them are dates. I click submit which then gets added to a spreadsheet. Each vehicle has its own row with 39 columns in total. Each text box on the form is labelled Reg1, Reg 2 and so on to relate to the column 1, column 2 etc. I used formatting in the VBA to make sure the dates go across in the UK format by doing DD MMM YY

The next step is to bring up the form again and perform a search on the VRM. The results are shown in a small window. I click on the entry I want to be populate in the form and once I double click, the VBA I shown in this thread is used.

In the code, it searches for the VRM and then links the text boxes by their reg numbers to the columns and populates the data. It is at this point where excel is getting the date data and switching it to US format. Note it's only a problem if the date is below the 12th of the month.

I've tried adding vba formatting to the spreadsheet before the lookup code and also straight after but I can't get it to work.

Any suggestions would be really appreciated.

 

[dmt32]

Hi,

try changing this line:

Me.Controls("Reg" & X).Value = FindValue

to this:

Me.Controls("Reg" & X).Value = FindValue.Text

This returns just the Text of the cell only i.e. what you see & not the cells underlying value so your dates should retain their displayed cell format - hopefully!

Dave

 

[anthony85]

Hi,

try changing this line:

Me.Controls("Reg" & X).Value = FindValue

to this:

Me.Controls("Reg" & X).Value = FindValue.Text

This returns just the Text of the cell only i.e. what you see & not the cells underlying value so your dates should retain their displayed cell format - hopefully!

Dave

Hi Dave, that solved the problem. Thank you very much