• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

Calender appear when click on cell to select a date

Hi All, is it possible to have a calender pop up when you click on a cell. Something similar to the windows calender when you click on the clock in the bottom right corner on the windows task bar. Id like it then to populate the cell with what ever date selected.

Thanks all
regards
k
0
knowvb
Asked:
knowvb
  • 12
  • 12
  • 2
  • +1
3 Solutions
 
Jai STech ArchCommented:
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...
0
 
Jeroen RosinkCommented:
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
0
 
m_tawfickCommented:
Try the Add-in download at the bottom of this site:
http://www.fontstuff.com/vba/vbatut07.htm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
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
0
 
knowvbAuthor Commented:
Hi Jeroen,

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

Any ideas?

Thanks again
k
0
 
Jeroen RosinkCommented:
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
0
 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
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
0
 
knowvbAuthor Commented:
Hi jeroen,
the problem is though I wont know what cells are to e used for thedate in thenew sheet.

Thanks again
k
0
 
Jeroen RosinkCommented:
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.
0
 
knowvbAuthor Commented:
Is a condition something such as clicking on toolbar format>cells and then set thecell as a date cell?
0
 
Jeroen RosinkCommented:
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
0
 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
I posted an asmaple file where I set the rangce to column C and colored the cells yellow where the date format is set.

https://filedb.experts-exchange.com/incoming/ee-stuff/4902-Q_22867496.zip 

regards,
Jeroen
0
 
m_tawfickCommented:
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.
0
 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
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
0
 
knowvbAuthor Commented:
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
0
 
knowvbAuthor Commented:
Ah, just got what you mean.
OK the value is now cleared ut it does not open automaticly on todays date.

Regards
k
0
 
Jeroen RosinkCommented:
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
0
 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
Hello K,
here another approach,
remove the Today()-line and add this code to your userform code:
Private Sub UserForm_Initialize()'actions to be performed when the userform is shown
Calendar1.Value = Now()
End Sub



Jeroen
0
 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
This can be explained that in the macro the format of the cell is set to the date format. which might be different.

this is because the code shows something like:
strtDate = Format(ActiveCell, "m/d/yyyy")

Change this into your format like:
strtDate = Format(ActiveCell, "dd/mm/yyyy")
0
 
knowvbAuthor Commented:
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
0
 
Jeroen RosinkCommented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 12
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now