need help with copying date from userform and changing the format of it before its copied to sheet

[kbishop94]

I have a macro that runs when the workbook is opened and looks at the date column and takes the date, copies it and pastes it as text in a hidden column so that it reads "Apr-2018" (or whatever the month/year is in the date.)
But I cannot get the same function to work when attempting to do it a command button is hit to copy the data over to the spreadsheet from the form.

Here is what I have so far:

Private Sub cmdAdd_Click()
Dim ws As Worksheet

[COLOR=#008000]'copy the data entered from the userform to the spreadsheet:[/COLOR]
With ws
 
.Cells(llRow, 2).Value = Me.DTPicker1.Value                                                               [COLOR=#008000]'this is copying the date from my datepicker to the date column (which is visible)[/COLOR]                       
.Cells(llRow, 5).Value = Me.DTPicker1(FormulaR1C1 = "=TEXT(RC[-1],""mmm-yyy"")") [COLOR=#008000]'this is what is not working. I need to take the same date from the datepicker and change it to the format "Apr-2018" and copy it to a hidden column which is column E (5) 
[/COLOR]

I know that this part: Me.DTPicker1(FormulaR1C1 = "=TEXT(RC[-1],""mmm-yyy"")") is not formatted correctly, but no matter how I try to 'arrange it' I still keep getting an error.

 

[Norie]

Try this.

.Cells(llRow, 5).Value = Format(Me.DTPicker1.Value,"mmm-yyy")

 

[kbishop94]

Try this.

.Cells(llRow, 5).Value = Format(Me.DTPicker1.Value,"mmm-yyy")

Thank you, Norie.

 

[kbishop94]

Try this.

.Cells(llRow, 5).Value = Format(Me.DTPicker1.Value,"mmm-yyy")

Just now had a chance to try this and it doesnt quite give me the return that i need...

It converts the date to: "Jul-18183" instead of "Jul-2018" ???

I tried several different methods, and still cant get it to give me the return I need.

What I am trying to replicate is the return I get if I was using a formula on the spreadheet. This formula gives me the correct return: =TEXT(B413, "mmm-yyyy") I cant get the VBA equivalent to do quite the same. (?)

 

[Norie]

This is the equivalent of =TEXT(B413, "mmm-yyyy").

Format(Me.DTPicker1.Value,"mmm-yyy")