user form with Calendar - txt box not Cell

[David0079]

Hey all,

I've been scouring the internet trying to find a solution to this one and I keep hitting a wall. I have a userform and I would like people to be able to click on a txtbox and a calendar pop up. they select a date, hit ok and it populates the txt box with that date. I have made the calendar form and I am able to get it to appear but how do I get it to put the date back into the txtbox? There are multiple date txtboxes on the sheet and I need to populate each one individually as you click on them. All of the methods I have found so far are about populating a field in excel Any help anyone?Dave

 

[David0079]

I just used the DTPicker as a drop down selection next to each text box. At first it didnt enter the time which was great but now it does, is there a way to stop it entering the time? I have enabled the check box to be able to delete the initial value which is working ok.

 

[Norie]

Dave

I'm getting a bit confused now.

What exactly are you doing and what code are you using?

Are you transferring the date from the datepicker(s) to textbox(s)?

If so, how?

Also what checkboxes? And what initial values?

 

[David0079]

Thats funny, I laughed when I read that! First, sorry for my poor choice of vocabulary which is clearly confusing us both! I added the DTPicker by right clicking on the tool box and selecting it. I have four text boxes on my form with two buttons on the bottom...'add info' and 'close'. I want to put a date next to each event. So I selected the date picker tool and put one of these things next to each event. each date then feeds back into cells in the spreadsheet. My question was, the date picker defaults to todays date but in some cases users might not fill in all four boxes but it still inserts the date regardless so I wanted the intial value of the textbox set to nothing/false. Also It pulls through the date nicely but it also puts the time next to it which I dont want. I hope I havent confused the issue even more

 

[Norie]

Dave

I can sort of understand what you are trying to do.

But I don't understand how you are trying to do it.

Do you have any code?

How are you transferring data from userform to worksheet?

 

[David0079]

Private Sub cmdAddEvents_Click()
Range("D2").Select

Range("D2").Value = Me.txtEvent1.Value
Range("E2").Select
Range("E2").Value = DTPicker1.Value
Range("D3").Select
Range("D3").Value = Me.txtEvent2.Value
Range("E3").Select
Range("E3").Value = DTPicker2.Value
Range("D4").Select
Range("D4").Value = Me.txtEvent3.Value
Range("E4").Select
Range("E4").Value = DTPicker3.Value
Range("D5").Select
Range("D5").Value = Me.txtEvent4.Value
Range("E5").Select
Range("E5").Value = DTPicker4.Value

Unload Me

End Sub



Private Sub cmdClose_Click()
Unload Me
End Sub


This is what I have at the moment

 

[Norie]

Dave

Thanks for posting the code.

I've got a couple of suggestions.

1 Drop the date pickers.

2 Drop the Select, it's not needed.

For 1 I would suggest using textboxes then validating what the user enters, using for example IsDate like I suggested earlier.

I'm just shooting off but if you need me to elaborate post back.

 

[David0079]

I know, that would be the normal and intelligent way but my boss wants a calender to appear, I've tried to reason but it doesnt work so I've spent most of the afternoon pulling my hair out. The date picker is working well its just that the time is appearing as well and I really dont want it too!

 

[Norie]

Dave

Are you sure the time is included?

When I try it it isn't.

 

[David0079]

Its only included if I active the 'checkbox' in the settings. I found I have to activate the checbox to be able to set the initial value of the DTPicker to null. I have to do this because sometimes there might not be four events so I get empty cells with todays date next to them, if that makes sense!

 

[Norie]

Sorry but it doesn't.

What checkbox are you talking about?

Do you mean you are setting a property in design mode?

If so which one?