Enter date and time in userform

[roscoe]

I asked several related questions but have not gotten much response; I've also searched and searched and not found what I'm looking for. The hits are either too basic or too advanced.

If someone could help guide me through a couple of simple steps, I hope to be able to go from there.

1) I need a user form where a default time (Excel serial coded value) can be displayed in a custom format (e.g. "dd mmm yyyy hhmm").

2) Use multiple spin buttons (my method of choice on worksheets) to "roll" the date or time up or down and have the displayed value reflect the change (I use spinners for week, day, hour, minute but for this example one spinner to roll the day up or down would be sufficient)

3) When done,save the date to a worksheet.

Thanks

 

[roscoe]

OK, I got past a few rookie issues (like where in the heck does the code get entered...) and now have the code below

Note: I used "now" just to seed the text box and "Load Me" just so I can hit F5 and start it

Private Sub UserForm_Initialize()
     Textbox1.Value = Format(Now, "d mmm yyyy hhmm")
     Load Me
End Sub

Private Sub Spinday_spinup()
    textbox1.Value = Format(Now +1, "d mmm yyyy hhmm")
End Sub

That worked when I pressed the spinner up button. Press it again and nothing happens because N+1 is the same as it was the first time.

I need to replace Textbox1.Value with textbox1.value +1 so each click of the spinner button increases it by one. I tried:

textbox1.Value = Format(textbox1.Value +1, "d mmm yyyy hhmm")

but that gave me an error.

Ideas?

Also, how do I open the user form via a worksheet-placed command button?

Thanks

 

[roscoe]

I'm getting there slowly. Here's what I have so far:

Dim datevalue As Double

Private Sub UserForm_Initialize()
    datevalue = Date
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
    Load Me
End Sub
Private Sub ResetButton_Click() '// starts over in case of screw up
    datevalue = Date
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub Spinday_spinup()        '// adds one day
    datevalue = datevalue + 1
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub Spinday_spindown()      '// subtracts one day
    datevalue = datevalue - 1
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub SpinWeek_spinup()       '// adds one week
    datevalue = datevalue + 7
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub SpinWeek_spindown()     '// subtracts one week
    datevalue = datevalue - 7
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub SpinHour_spinup()       '// adds one hour
    datevalue = datevalue + 1 / 24
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub SpinHour_spindown()     '// subtracts one hour
    datevalue = datevalue - 1 / 24
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub SpinQtrHr_spinup()      '// adds 15 minutes
    datevalue = datevalue + 1 / 24 / 4
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub SpinQtrHr_spindown()    '// subtracts 15 minutes
    datevalue = datevalue - 1 / 24 / 4
    startdate.Value = Format(datevalue, "ddd d mmm yyyy hhmm") & Chr(104)
End Sub
Private Sub CancelButton_Click()    '// cancels
    Unload Me
End Sub
Private Sub SaveButton_Click()      '// writes value to cell A1 and quits
    Worksheets("Sheet1").Range("A1") = datevalue
    Unload Me
End Sub

Everything works except the "SaveButton" (last routine)...nothing is written to A1. I did it earlier before I figured out how to store the value in a variable so long as the user form stayed open, but now it's not working. Ideas?

 

[roscoe]

Never mind, I got that. Next question (not that anyone is listening...)

I initialize the user form with the following :

Dim datevalue1, datevalue2 As Double '// to carry them between user form control subroutines

Private Sub UserForm_Initialize()
    datevalue1 = Date
    datevalue2 = Date + 1
    StartDate.Caption = Format(datevalue1, "ddd d mmm yyyy hhmm") & Chr(104)
    StopDate.Caption = Format(datevalue2, "ddd d mmm yyyy hhmm") & Chr(104)
    Load Me
End Sub

Right now I'm preloading two label captions with values based on the system date. What I really need to do is pass two Excel serial date values to this user form.

1) How do I call a user form from a macro?
2) How do I pass the values via that macro to the user form?

(edit: if you couldn't tell I'm really new at this...)

 

[roscoe]

I got 1, but no idea about the second. All search hits are on passing values from a user form, not into one.

Really need ideas here...please???

 

[roscoe]

Seriously...nobody?