Calender appear when click on cell to select a date

you are talking abt the DATETIME PICKER control that VB.NET is providing...
you dont need any specific coding for this...you can use the calender control for the same...

Here some more information:
http://www.rondebruin.nl/calendar.htm

or
you might take a look at: http://www.geocities.com/eeroos01/Q_21279605.xls

I have here stored another exampled which uses the calendar control.
open VBA editor (alt + F11) and select the userform and run it.

I didn't implement here the selection of the cell below. as I said it was from a previous Q.

I hope this explains it a bit further for you.

the code line which does the trick is:

ActiveCell.NumberFormat = "dd/mm/yyyy" 'formats the date
ActiveCell.Value = Calendar1.Value 'fills the selected date

regards
Jeroen

Try the Add-in download at the bottom of this site:
http://www.fontstuff.com/vba/vbatut07.htm

Thanks experts.

Thats got me going on the right lines.

Roos01,
I think the xls sample you linked to seems to be along the line i need.

Is it posile to have the calender popup as soon as a cell is clicked on rather than double click?

Mant thanks
k

You might post this code in the sheet code pane instead.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rg As Range, cel As Range
Dim strFormat As String
Set rg = Intersect(Target, Range("A:A")) 'here it is only triggered when a selection is changed in Column A
UserForm1.Show
End Sub

regards,
Jeroen

Hi Jeroen,

Brilliant thanks for the suggestion. The code however seems to call the userform when anycell is selected.

Any ideas?

Thanks again
k

Your correct,
it should be:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rg As Range, cel As Range
Dim strFormat As String
Set rg = Intersect(Target, Range("A:A")) 'here it is only triggered when a selection is changed in Column A
If rg Is Nothing Then Exit Sub
UserForm1.Show
End Sub


Jeroen

Hi Jeroen,

thanks forthe help there.

calender is popping up now in column a. However, is they a way though prevent certain cells in column A calling the calendar?

The problem is though that a user would be working from sheet "new". cell ranges from another sheet will be pasted to the new sheet. These cell ranges could be pasted anywhere though in the new sheet. I would like only particular cells in column a from the pasted cell range to call the calender. Is they any way to set this in the other sheet but still have it applied to the cells when pasted into  thenew sheet even though the cells once pasted might not be the same cell refernce. eg was A3 ut will be pasted into A15.

Many thanks
k

You can define the range where to look in by changing this line like:
Set rg = Intersect(Target, Range("A1:A10"))

or for non contingious cells:
Set rg = Intersect(Target, Range("A1:A10,A20:A30,A40:A50"))

regards,
Jeroen

Hi jeroen,
the problem is though I wont know what cells are to e used for thedate in thenew sheet.

Thanks again
k

How can we identify those cells? If it can be identify because there is some kind of condition we could addapt the macro to it.

Is a condition something such as clicking on toolbar format>cells and then set thecell as a date cell?

If there is a fixed date format defined like: m/d/yyyy then it could be something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rg As Range, cel As Range
Dim strFormat As String
Set rg = Intersect(Target, Range("A:A")) 'here it is only triggered when a selection is changed in Column A
If rg Is Nothing Then Exit Sub
If rg.NumberFormat <> "m/d/yyyy" Then Exit Sub
UserForm1.Show
End Sub

mmm. thanks again.

I cant seem to get it to work.

Ive changed ....
Set rg = Intersect(Target, Range("A:A")) 'here it is only triggered when a selection is changed in Column A
to
Set rg = Intersect(Target, Range("C:C")) 'here it is only triggered when a selection is changed in Column A

and also
If rg.NumberFormat <> "m/d/yyyy" Then Exit Sub
to
If rg.NumberFormat <> "dd/mm/yyyy" Then Exit Sub

and also
UserForm1.Show
to
toUserForm8.Show

but nothing happens when i click a cell.

thanks again
k

Hi knowvb,

I don't think you have tried my suggested link:
http://www.fontstuff.com/vba/vbatut07.htm
At the bootom of the page, there is a link to download.

It has an Excell add-ini, that once installed allows you to right-click on any cell and select insert data, then a calander pops-up to pick the date from.
Try it, it is very handy.

Hi all,

tawfick, thanks forthesuggestion, butineed the calender toappear assoon asauserclicks on a cell.

Jeroen,
Thank you. I seem tohave it working now. However is it possibleforthe calender to appear on todays date rather than a date in the past?

Manythanks again for allyour help here.

kind regards
k

Hello k,
I see what you mean. I noticed that in the calendar control the old date value is set.
1. select the calendar control
2. open the property window and move to value. here the date 12/5/2005 is mentioend.
3. remove the date

now you will see that the current date is shown

regards,
Jeroen

Hi Jeroen,

Thanks for that.

I dont quite understand though. Do you mean open the userform witht he calender on and then select todays date?

If this is the case, i dont think it will do the job. I idealy need the date shown when the alender appears to be the same as the date set on the computer.

Hope this makes sense.

many thanks
k

Ah, just got what you mean.
OK the value is now cleared ut it does not open automaticly on todays date.

Regards
k

Not open on todays date? strange.
add this line in the beginning of the userform code:
Calendar1.Value = Today()
If im correct that should do the trick.

Jeroen

hi Jeroen,

tried that but still no luck.

heres an example of my code in the userform the for calender....

Private Sub Calendar1_Click()
Calendar1.Value = Today()
ActiveCell.NumberFormat = "dd/mm/yyyy"
ActiveCell.Value = Calendar1.Value
Unload Me
UserForm9.Show
End Sub

For ifo, userform9 asks if this date should be added tothe outlook calender.

Many thanks
k

Brilliant!. That seems to do the trick.

I just noticed a bug though. The following code pasted below is the one that tells excel if to launch the calender or not dependant on the format of the cell.
If for example one selects a cell and the calender pops up  then you select a date but if this date was selected in error, if you try to click on the cell again then the calender wil not pop up. If however i then save the workbook and then reopen it i am able to selectthe same cell and the calender will pop up.
The code........

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rg As Range, cel As Range
Dim strFormat As String
Set rg = Intersect(Target, Range("C:C")) 'here it is only triggered when a selection is changed in Column A
If rg Is Nothing Then Exit Sub
If rg.NumberFormat <> "m/d/yyyy" Then Exit Sub

UserForm8.Show
End Sub

thanks again jeroen
k

Jeroen, well done. job done!.

Thank you somuch for your helponthis one.
I do have another question about compiling if you fancy adding your thoughts to that one.

Maythanks again.
k

Glad to help.
Thanks for the grade!.
I will monitor the questions, If im not too late and still online I will check your Q.
Have a nice evening.
with regards,
Jeroen