Problem with formatting

[Hashiru]

Can any one help please.
I am having problem formating my code. I want the code to format each additional cell by clearing any format in that cell and place the desired form.

Desired format: 01/01/2011 "dd/mm/yyyy"

My code:

With Worksheets("Bank Payment").Cells(7, 1).Resize(nextrow, 1)
.Clear
.NumberFormat = "dd/MM/yyyy"
End With

nextrow is a variable that change each time an entry is made


I want to type 01/01/11 and the code format it to 01/01/2011

thanks

 

[GlennUK]

Can I ask why you don't format your input area beforehand?

Also, if you want to do this with VBA have you considered using the sheet event Worksheet_Change to alter the format of entered cells?

 

[Hashiru]

Thanks for your reply
My intention was to clear any previous format on the cell where the date is to be input and then input the date and lastly format it.
Column A is where I intend the date to be input starting from row 7. I am avoiding clearing the whole column because the user might want to do some calculations at the buttom.
I am actually designing a form to input data into excel worksheet and this code suppose to be behind the ok buttom specifically on the date textbox called datetext.
Thanks for your reply once more.

 

[GlennUK]

If you know which cell you are putting the data into, then why can't you format that cell straight after putting the data into it?

 

[Hashiru]

this is how far I have gone. thanks

Private Sub OKButton_Click()
'Find the next row of the data sheet
nextrow = Worksheets("Bank Payment").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Transfer data from ExpenseClassificationForm to worksheet
With Worksheets("Bank Payment")
.Cells(nextrow, 1).ClearFormats
.Cells(nextrow, 1).Value = Me.DateText
.Cells(nextrow, 1).NumberFormat = vbShortDate
End With

 

[Hashiru]

Hi
Thanks
Private Sub OKButton_Click()
'Find the next row of the data sheet
nextrow = Worksheets("Bank Payment").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Transfer data from ExpenseClassificationForm to worksheet
With Worksheets("Bank Payment")
.Cells(nextrow, 1).ClearFormats
.Cells(nextrow, 1).Value = Me.DateText
.Cells(nextrow, 1).NumberFormat = vbShortDate
End With

The macro recorder is giving me short date code as "d/m/yyyy" if replaced with the vbShortDate constant it give exactly the same result.
A green triangle on the cell where the date is input says
Text Date with 2-digit year
convert xx to 19xx
convert xx to 20xx
my system date is set to convert 2 digit year between 1930 and 2029.
Thanks

N/B: Can you show me how to insert screen shot on this message board. This is for you to see what's going on.

 

[GlennUK]

What are the contents of me.DateText? Have you considered converting whatever that is to a dateserial number for input to the cell?

 

[Hashiru]

Thanks

Me.DateText transfer the value in textbox to excel in the designated cell.

If I know how to show you a screen shot I shall show you exaclty what I am saying

Thanks

 

[GlennUK]

Change this:

.Cells(nextrow, 1).Value = Me.DateText

to

.Cells(nextrow, 1).Value = CDate(Me.DateText)

 

[Hashiru]

hi thanks for your help

I think I have came up with a solution. I guess you may want to take a look at it. See below:

Private Sub OKButton_Click()
'Find the next row of the data sheet
nextrow = Worksheets("Bank Payment").Cells(Rows.Count, 1).End(xlUp).Row + 1
'Transfer data from ExpenseClassificationForm to worksheet
With Worksheets("Bank Payment")
.Cells(nextrow, 1).ClearFormats
.Cells(nextrow, 1).Value = Me.DateText
.Cells(nextrow, 1) = Format((Me.DateText), "dd/mm/yyyy")

End With
Thanks once more for your contributions to this solution.