View original (French)

Insert calendar cell excel

Solved
dat -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,

I am working on a small file in Excel 2007.
I have a column intended to hold the installation date of a vehicle.
To simplify and make the use of this file ergonomic for future users, I would like that when they click into the cell to enter the date, a calendar appears that they can click on to insert the date into the cell, which would be nicer...
Problem: I’m rubbish at IT!
I have tried through various forums to tinker something with VBA but I can’t get anything to work; could someone kindly explain in detail how to do it, with the codes and the buttons to click... ????

I thank this superhero in advance...
Configuration: Windows Vista

20 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Replace the first line with:
    If Not (Target.Column = 3 Or Target.Column = 5) Or Target.Row < 2 Or Target.Cells.Count > 1 Then
    3 and 5 are the numbers of the concerned columns.

    For your other post you’re complicating things by trying to make a form (if it’s just for that...). Its management takes time that I wouldn’t have.
    When you ask a question don’t hesitate to upload a sample file on cijoint.fr and paste the link in the post. Not everyone has the courage to recreate your file and moreover it won’t fit with yours...

    In a sheet List create your lists and name the model ranges.
    Example: in B1: Renault, in B2:B4 the models, select B1:B4 then 'Insert / Name / Create...', check 'Top row', validate
    Your range B2:B4 is now named Renault
    In A have a list of brands with the title (name also)

    In your worksheet for your first list in A2:A8 you do a validation by list with =Marques
    For the cascading list a data validation by list in B2:B8 with =INDIRECT($A2)

    example

    eric

    eric
    42
    1. dat
       
      Thank you Eric, it works I am very grateful to you; I will finally be able to present a file that resembles something..
      If I have other questions about these software, how can I contact you directly on this site?
      Are you also knowledgeable about Access?
      Thanks again and see you soon
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello everyone,

    It’s on the sheet that you need to add the Calendar control, so you need the toolbar in Excel 'normal'.
    Otherwise you can upload your file to cijoint.fr (in .xls format) and paste the provided link here.
    However I can only guide you on Excel 2003; with 2007 you have to search...
    eric
    7
  3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    it is necessary to modify the macro a bit (note there is 1 line less, copy the whole thing and add another one). 

    Private Sub Calendar1_Click() ActiveCell.Value = Calendar1.Value End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' if the selection on the sheet changes (target is the reference to the selection) If Target.Column <> 3 Or Target.Row < 2 Or Target.Cells.Count > 1 Then 'if the column <> 3 (C) or the row < 2 or the selection covers more than 1 cell Calendar1.Visible = False 'then we hide the calendar Exit Sub Else 'otherwise Calendar1.Top = Target.Offset(1, 0).Top + 2 ' align the calendar with the top of the cell below Calendar1.Left = Target.Left + 10 ' align it to the left of the cell 'Calendar1.LinkedCell = Target.Address ' set the control's linked cell to the selected cell If IsDate(Target.Value) Then 'if the selected cell contains a date Calendar1.Value = Target.Value ' fetch it Else ' otherwise set today's date Calendar1.Value = Date End If Calendar1.Visible = True 'show the calendar End If End Sub

    eric

    edit : i slightly modified the code to offset the calendar and be able to select the cells underneath that were hidden
    5
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening,

    An example of what you could do.
    Click column C (except row 1) to display the calendar.
    Only small issue, the calendar control provides a string rather than a date value. Calculations on it are feasible, but a date comparison would be incorrect if you don’t multiply the value by 1.
    Not knowing your exact level, ask where you’re stuck...

    eric
    3
  6. dat
     
    Thank you Eriiic for replying, it’s always a pleasure to see that you can find help on the Internet when the IT manager at one's company doesn’t even take the time to explain...anyway...

    However, when I say I’m bad at computers, it’s unfortunately true....and apart from the SUM function, if...I don’t know how to do anything....

    So to return to our question, could you detail please (if you have 5 minutes to spare), the step-by-step manipulations, the buttons... in Excel 2007?
    I did try copying your formula into VBA but I really don’t know anything about it...
    When I try to run it, the software asks me to create, whereas I thought I had finished...I don’t know at all how to proceed. Do I need to create a UserForm? How to do it?

    The vehicle commissioning date must be in column G, except in G1 which is the header of the column....

    I didn’t think inserting a simple calendar into a cell would be so hard...

    Thanks in advance for your valuable help
    2
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Hi everyone,
      Hi Eriiic,
      (by the way, superb table, very nice solution)
      Okay for Dat, it looks relatively easy to adapt this code. Just follow the steps:
      Part 1:
      on the sheet:
      - menu 'view / toolbar' check 'Control Toolbox'
      - in this toolbar click on the 'More Controls…' tool (far right)
      - select 'Calendar Control 11.0'
      - draw it on the sheet

      I suppose that shouldn’t pose any problem for you?

      Part 2 the code: right-click on the tab of your sheet and:
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      ' if the selection on the sheet changes (target is the reference of the selection)
      If Target.Column <> 3 Or Target.Row < 2 Or Target.Cells.Count > 1 Then
      'if column number <> 3 (C) in your case, column G replace 3 above with 7 or row number < 2 or the selection spans more than 1 cell
      Calendar1.Visible = False
      'then hide the calendar
      Exit Sub
      Else
      'otherwise
      Calendar1.Top = Target.Offset(1, 0).Top
      ' align the calendar with the top of the cell below
      Calendar1.Left = Target.Left
      ' align it to the left edge of the cell
      Calendar1.LinkedCell = Target.Address
      ' bind the control to the selected cell
      If IsDate(Target.Value) Then
      'if the selected cell contains a date
      Calendar1.Value = Target.Value
      ' retrieve it
      Else
      ' otherwise put today's date
      Calendar1.Value = Date
      End If
      Calendar1.Visible = True
      'show the calendar
      End If
      End Sub

      Try it in a blank Excel workbook and come back to tell us where you get stuck.
      --
      Regards,
      -- What’s worth doing is worth doing well --
      0
  7. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    No worries pijaku, you’re doing well.

    dat: you can draw the calendar wherever you want but give it the correct dimensions.
    However, after you’ve drawn it you need to exit the 'construction' mode by clicking the icon with the ruler + pencil + set square.
    Then click anywhere: it disappears, click in the correct column: it reappears.
    eric
    2
  8. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hi,

    the same comment: calendar.xls
    As needed, we could complete the tests so that it only displays if the cell is empty or contains a date, to avoid data loss.

    eric
    1
    1. UsulArrakis Posted messages 7683 Status Contributor 3 196
       
      Hello eriiic
      I’m passing along this message because I’d like to know
      1- if it’s possible to set the calendar to 3 columns instead of a single one (in my case E, F and M)
      2- if it’s possible in the macro to trigger multiplication by 1 to be able to use the date (calculation)

      thank you
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello Usul, Quick adaptation done due to having to pack a suitcase… ;-) http://www.cijoint.fr/cjlink.php?file=cj201107/cijO2M6mst.xls There's still a small issue (which may have existed in the other version): when deleting a date it still shows 12:00:00 AM You can complete it if needed I think Note: declaring a variable in a module. Bad reflex, I think you can bring it back into the sheet, but outside the sub eric
      0
    3. UsulArrakis Posted messages 7683 Status Contributor 3 196
       
      thanks, you didn’t have to hurry, nothing urgent but it’s nice that you took time from your precious schedule, so enjoy your vacation
      for me it’s in August ;-)

      we’ll talk about it again in the new school year
      0
  9. dat
     
    Hello Eriiic,

    I managed to draw a calendar on the Excel sheet, well... I’m drawing it anywhere, it doesn’t matter? Then I right-clicked on the tab "feuill1" still in Excel's "normal" mode and chose "view code"; I clicked it and I entered pijaku's data directly. Is that good so far?

    But once the code is entered (assuming I didn’t mess up), what do I do to run it and see if it works because when I click "run" it asks me to create the macro, which isn’t what I just did?

    It’s rough when you don’t know anything!

    Thanks to you
    1
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      No once you have copied and pasted the code, closed the Visual Basic window (there you see your Excel sheet) it works all by itself, by clicking on the cells of your sheet...

      Edit: for Eriiic, I take the liberty to intervene here without wanting to intrude on your discussion...
      --
      Best regards,
      -- What is worth doing is worth doing well --
      0
  10. dat
     
    Here it is, it works!!!! Thanks for your patience and your help... without you I would have given up!
    One last question before clicking on resolved; how can I make it so that when I click in a column (the calendar appears...), if I leave it without clicking a date it doesn’t set today's date?
    Thanks in advance for this question and thanks for helping me.
    1
  11. dobo69 Posted messages 1593 Registration date   Status Member Last intervention   835
     
    Good evening,

    I was passing by and if I may, I would be strongly interested in an explanation from Professor eriiic...

    --
    If you don’t need anything, ask me.
    0
  12. dobo69 Posted messages 1593 Registration date   Status Member Last intervention   835
     
    and a big thank you, Mr. Professor, I’ll go to bed less stupid tonight..

    --
    If you don’t need anything, ask me.
    0
  13. dat
     
    Hello and thank you for your reply

    I’m unfortunately stuck in the first part.... No joking around...

    Let me explain:

    Display menu... Should it be done in VBA? or simply in the sheet as it is?
    Because I can draw this 12.0 calendar by going into VBA and clicking on New UserForm and inserting the calendar; is that it?
    0
  14. dat
     
    Thank you very much to everyone, it works... should we put "solved" somewhere on this forum?

    See you soon.
    0
  15. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Yes, it’s better. But if you’re not registered you maybe can’t.
    I did it for you.
    Good continuation...
    eric
    0
  17. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    It’s better to post on the forum, its goal is that everyone benefits from the answers.
    And if I’m not here, others can reply, in private...
    No, I only know the minimum about Access, sorry.
    eric
    0
  18. gunbafo Posted messages 55 Status Member 3
     
    Hello,

    This explanation is appropriate for a manipulation I want to perform, but I’m encountering a problem.

    I’m using Excel 2003 and I can’t find in the "Other controls" tool the option 'Calendar Control 11.0'. Is this a version issue with Excel?

    Thank you for your help in finding the problem.
    0
  19. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    A priori it ships with Access, so install Access.
    Otherwise search the net for MSCAL.ocx or an equivalent

    eric
    0
  20. t4nch3
     
    Hello,

    I just saw this topic a few months ago, but not finding the solution elsewhere I’m taking the liberty to ask you the question.

    I would like to do exactly what Eric does, but in Word.

    In the end, I’d like to display a calendar by clicking on a cell of my table that I’m making in Word.

    And through this calendar, be able to set the date that interests me.

    Is that possible?

    Thank you for your help.
    0
    1. Tqd Posted messages 1 Status Member
       
      Great, it works!
      Now, I’d like to apply it to two columns side by side.
      For example: Follow-up date and required date
      It seems I can’t use the same code because I get an error message...
      What should I do?
      0
  21. Loryth
     
    Hello,

    I would like to do exactly the same thing but is it possible that you upload your test files again so I can understand how it works
    Thank you very much and have a good afternoon
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello, The Calendar control no longer exists in the new versions. MS changes with almost every version; in 2010 for example it’s the DTPicker control. I therefore discourage you from using them and from taking a calendar built into a form. You can find multiple examples on the web. Here, for example, is the one from pijaku: https://www.commentcamarche.net/faq/41159-vba-excel-toutes-versions-controle-calendrier-transposable eric
      0