Entering a Date in a form using spin buttons then getting it to add to the spreadsheet in date format

[freckles81]

Hello all
sorry for this, twice in one day!!

I have a form that I have put together that is allowing the user to select a date using spin buttons and it places the date in the text box, this works fine but when I ask it to place it in the spreadsheet it places it in as a time '00:00.00' and not a date. I know I need to add something in the VB to get it to use a date format but I don't know what i have missed.

I am sure this is really easy but I am just not having any fun with this today

holto is the text box name

this is the info on the spin button

Private Sub UserForm_Activate()
Me.SpinButton2.Min = Int(Now() - 360)
Me.SpinButton2.Max = Int(Now() + 360)
Me.SpinButton2.Value = Int(Now())
SpinButton2_Change
End Sub

Private Sub SpinButton2_Change()
Me.holto.Value = Format(Me.SpinButton2.Value, "dd-mmm-yy")
End Sub

and this is the info in the module

Sub addname()
Dim namejoin As String
Dim propername As String
Dim properfirst As String
Dim properlast As String
Dim dept As String
Dim existflag As String
Dim x As Long
Dim srange As String
Dim holto As Date

andtheresmore:
Load UserForm1
Application.ScreenUpdating = False
startagain:
UserForm1.Show
If UserForm1.firstname.Value = "" Then
MsgBox "User First Name Cannot Be Blank - Please Re Input", vbExclamation, "Agency Personnel - Add Name"
UserForm1.firstname.SetFocus
GoTo startagain:
End If
If UserForm1.lastname.Value = "" Then
MsgBox "User Surname Cannot Be Blank - Please Re Input", vbExclamation, "Agency Personnel - Add Name"
UserForm1.lastname.SetFocus
GoTo startagain:
End If
properfirst = StrConv(UserForm1.firstname.Value, 3)
properlast = StrConv(UserForm1.lastname.Value, 3)
namejoin = properfirst & " "
namejoin = namejoin & properlast
propername = namejoin
Sheets("name").Select
Range("A1").Select
existflag = "N"
Do Until Selection.Value = ""
ActiveCell.Offset(1, 0).Select
If ActiveCell = propername Then
existflag = "Y"
End If
Loop
If existflag = "Y" Then
MsgBox propername & " Already Exists in the Database, Please See Your Administrator", vbCritical, "Agency Personnel - Add Name"
GoTo okthatsitthen
End If
dept = UserForm1.dept.Value
Call unlockit
ActiveCell = propername
ActiveCell.Offset(0, 1).Value = dept
ActiveCell.Offset(0, 2).Value = properfirst
ActiveCell.Offset(0, 3).Value = properlast
ActiveCell.Offset(0, 4).Value = holto

Sheets(dept).Select
Range("A2").Select
Do Until Selection.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
Call unlockit
ActiveCell = propername
Call lockit
Sheets("name").Select
Call unlockit
Range("A1").Select
Selection.End(xlDown).Select
x = ActiveCell.Row
srange = "A2:e" & x
Range(srange).Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

Call lockit
'ActiveWorkbook.Save
MsgBox propername & " Has Been Added To The Database", vbInformation, "Agency Personnel"
okthatsitthen:
Unload UserForm1
Sheets("Index").Select
Range("C5").Select
GoTo andtheresmore
End Sub

 

[mikerickson]

Try

ActiveCell.Offset(0, 4).Value = DateValue(holto)

 

[freckles81]

Hi
nope that didn't work, it still comes up as 12:00:00am in the cell.

If it helps when i run the macro and hover over the holto section against this line:
'ActiveCell.Offset(0, 4).Value = holto'
it comes up with a little box that says holto = 00:00:00
So somewhere i need to define this but i am missing where i need to do it and how.

Sorry i thought sleeping on it would help me figure it out but it appears that my brain is refusing to play!!

Freckles81

 

[mikerickson]

What kind of control is holto?
Is the cell formatted as Date or Time?
Is the userform modal or modeless?
Are the other pieces of data (e.g. the name) transfered from the UF to the worksheet properly?

 

[freckles81]

Hi
Holto is the name of the box the date appears in on the form

the cell is formatted as date and when i go and change it back to date (it reverts to time when it puts the data in) format it changes it to 00/00/00

the data is transferred exactly as it should be and i am not sure what model or modeless means, sorry i am not sure if i am of any help at all. would i be bale to send you a copy of the sheet to have a look at?

Thanks
kate