+ Reply to Thread
Results 1 to 99 of 99

Insert Dropdown List inside Calendar Days

  1. #1
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Insert Dropdown List inside Calendar Days

    Hi I have this code to create a Calendar and I want to add a Dropdown List for each days in the Calendar.
    Please Login or Register  to view this content.
    Thank you!

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Yes, okay, you want to add a dropdown list
    What's the data to be in those dropdown lists?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Yes, okay, you want to add a dropdown list
    What's the data to be in those dropdown lists?
    BTW you should dimension your variables, It's bad practice if you use undimmensioned variables
    Check Options Explicit in Excel and read about it, makes sure that you always calculate with the correct variable types.

  4. #4
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Here are the data that I'll be needing to the drop down list
    Assignment
    Send Product
    Creates Draft
    Reviews
    Create Final Draft

    Thank you!

  5. #5
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    This is what's the information looks like in the Calendar. Thank you!
    Attached Images Attached Images

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    You can take care of the formatting.
    Try this one, I added the code to your own macro (with dimensioned variables)
    Attached Files Attached Files

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    You'll have to add a comma (,) between Draft and Reviews to split that line too, forgot it

  8. #8
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Thank you this work perfect! I have a question is it possible to have a multiple selection in dropdown list per day?

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I think it can be done, here's a link explaining but I haven't tried it
    http://www.contextures.com/excel-dat...-multiple.html
    I would go for a userform dropdown since you want the selections one below the other and also I assume you also want to add a selection if there is also one already present ?

  10. #10
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Quote Originally Posted by Keebellah View Post
    I think it can be done, here's a link explaining but I haven't tried it
    http://www.contextures.com/excel-dat...-multiple.html
    I would go for a userform dropdown since you want the selections one below the other and also I assume you also want to add a selection if there is also one already present ?
    Yes exactly the drop down is not limited to one selection only. I'm have no idea how to convert this into userform, is there anyway to convert the codes intouserform interface?
    Last edited by 323100N; 04-26-2018 at 03:15 AM.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I'll take look later.
    But since you work with vba too, try this, create userform with one listbox and multi select then you trigger this as a right mouse click even in the worksheet for each field where it should apply so that the userform is displayed and the selected items are posted into the then active cell.
    It is explained in the link too.
    I'll see this afternoon, I'm tied-up now with other mattres

  12. #12
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Yes Please, any help is very much appreciated. thank you!

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I already 'wrote' in my head now put it in code

  14. #14
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    You are such a Macro Expert!!

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Well, I put it together.
    I am already imagining your next question
    You've got a series of actions in your 'dropdown list' which according to my impression are actions that follow a certain order.
    I could imagine that when the cell is empty there is only one choice
    the choices you have:
    1. Assignment
    2. Send Product
    3. Creates Draft
    4. Reviews
    5. Create Final Draft

    So the first will be Assignment
    if the dropdown is called again then the next choice should be Send Product etc. etc.
    If Send Product is skipped and Create Draft is selected then you should not be allowed to select Send Product anymore.

    Take a look at the attached file, I removed the dropdown data validation from all the cells.
    You now right click in the large cell and a userform will pop-up next to the cell and this speaks for itself
    Enjoy it
    Attached Files Attached Files

  16. #16
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Wow this works great! The only thing that I want to update is the particular order or certain order to choose in the list. The user can choose any task he/she wants and also if possible to auto adjust the cell if all of the list was checked by the user, and if there is a way to call the Outlook then attached the Calendar as a picture attachment? ? thank you in advance, again your knowledge is very much appreciated
    Last edited by 323100N; 04-27-2018 at 03:22 AM.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    What is your exact question?
    I would not auto adjust the calendar to the contents of the list. You chose for row height 65 which looks good. If I were you I would check and see if all the items were in the cell what the font size would be to display them correctly but leave the row height as is.

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    With the current font size (on my system) the cell is nicely filled if all items are selected
    Font is Calibri size 10
    Attached Images Attached Images

  19. #19
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Yeah maybe I'll adjust the font size just incase. Is it possible to have a macro that will capture the whole calendar and call the Outlook Application then attached as image in the main body of email?

    and also can we remove this statement or arguments/


    Thank you!
    Last edited by 323100N; 04-27-2018 at 04:23 AM.

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Have you tried your BFF Google?

    I got this hit when I enterde the question
    https://excel-macro.tutorialhorizon....k-using-excel/
    And what is "this statement or arguments" yu mention?

  21. #21
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Sorry forgot about the statement and arguments I thought the selections has this statement. Thank you!

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I don't know if you managed the Outlook part with the link I sent you.
    I did add functionality that allows you to delete (selectively) one or more of the entries already in a cell.
    Also there is a hidden sheet that holds the choices for the task list so that all you need to do unhide that sheet and add, edit or remove task list items
    This avoids having to hard-code lists in the VBA code
    About the cell size, with a calendar I think that all cells with the same size and format is more organized, you can always make these larger.
    I've attached the modified file.
    I still do not understand what you mean with 'statement and arguments'
    Attached Files Attached Files

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    The Outlook part is a little more complicated.
    I managed to put something together.
    You will either have to add a userform to enter the receiver's email address and some body text but right now this just generates a message with you can edit to sent.
    I've attached the file and a scree capture
    Attached Images Attached Images
    Attached Files Attached Files

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Your favorite stalker here again
    I added the (Canadian) Holidays script so the day number cell will be colored Yellow and right click will tell you it's a Holiday and not permit selection.
    Just modify the code it it's okay to select tasks but maybe show the msgbox informing the that the selected day is a Holiday, I don't know what you do with the weekends.
    So now you can Mail and even have the Holidays dynamically marked in the corresponding Months.
    Attached Files Attached Files
    Last edited by Keebellah; 04-30-2018 at 02:05 AM. Reason: Corrected Attachment

  25. #25
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Oh my! I'm just thinking about those holidays , and here you are you already created the code for that! You are awesome. Thank you!

  26. #26
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    How can I add a highlight if a specific information in drop-down was selected for example the "assignment" was selected, the main word selected will be highlighted in orange? and also I tried to edit the email portion and remove the default recipient email add and try to Mail as Attachment but the calendar wasn't able to attach to the body of the mail.
    Last edited by 323100N; 04-30-2018 at 04:53 AM.

  27. #27
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Hi,
    1. Highlighting: What do you want to highlight? the selection on the Worksheet? that will take quite some code, it is possible and is explained on different sites with sample code, you have to load the entire contents into a variable and calculate the position of the text you want highlighted in the complete list (including the hard returns) then you return the string to the cell concatenated three ways, this initial part until the beginning of the text you want highlighted, then the highlighted part which you then highlight and the remaining text
    2. Email: what have to changed or done, can you show that? Did you remove the wait command? That one's necessary so it has time to load the image.
    Just paste the macro code for that section

  28. #28
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Allright, just forget about the highlight options , with regards to a blank attachment it happens when I send a second email. I just remove a default email address. for me to send a new set of email I need to create again a calendar and It takes a while to load the new calendar.
    Please Login or Register  to view this content.
    Thank you!
    Last edited by 323100N; 04-30-2018 at 10:20 AM.

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    That means that for every mail or more you need to edit the vba code set a new email address and run the macro again?
    You don't have to create a calendar again, just press the attach button.
    And like I mentioned you could either add a user form to add new addresses or make a list of possible mail receivers and select one or more (using a listbox) to send.
    If you're sending the same calendar without other information and don't want the others to see who received it, use the BCC string.
    Can you explain exactly what you want to do?
    But, create a new calendar every time is not necessary so I do not understand why you're doing that.
    And if the message is complete you can use .Send instead of .Display or .Save instead then the message is saved as draft and you can afterwards open outlook and send all the drafts one-by-one.

    Many options and with VBA you can do most of it

  30. #30
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    The coloring of a selected text inside the cell was simpler that I thought
    I'll implement it.
    What color does the item you want highlighted need to get?

  31. #31
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    You'll have to play around a little, but you can select a task and highlight it (red)
    the mhh stands for month holiday highlight
    Attached Files Attached Files

  32. #32
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi, can we remove the holiday restriction? and also for the selection list can we still retain the list that already chosen? because some of the list it takes 3 days to process so I need to insert again the specific list into to different date.. thank you once again!
    Last edited by 323100N; 05-03-2018 at 05:12 AM.

  33. #33
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    please? Thank you very much

  34. #34
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Just change this section for the Holiday restricton

    Please Login or Register  to view this content.
    This is all you need

    Please Login or Register  to view this content.
    I do not understand what you mean by the selection list, you can still select anything the next date

  35. #35
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    yeah my bad! hahahaha..I'll update the holiday section code. once again thank you

  36. #36
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Let me know if you get it to work like you want

  37. #37
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    I tried to removed the code for the holiday calendar I think i messed up the code the right click doesn't work and the highlight selection it only highlights 1 list. I'm not sure if I also messed up this portion. I'm sorry

  38. #38
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  39. #39
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    You did not do what I told you, all you dis was deactivate the lines of code, but you deactivated ALL
    Please Login or Register  to view this content.
    DO NOT deactivate the Green line

  40. #40
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Now I got it, the only problem is the highlighted in red only 1 information can be highlighted even if I checked all the list. thank you

  41. #41
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    That's what I told you,
    I would have to check the code and rewrite to highlight one or more, to highlight all the selections would be simpler, but then I think highlighting misses the goal.
    If you really need it I can take a look and see if I can manage to highlight more than one (is selected)

  42. #42
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Yes please. Its kind a confusing every every time a removed the default email To: sender the calendar didn't attached to email but when I put it back it works well. Here is the section where i removed the .To email.

    Please Login or Register  to view this content.

  43. #43
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I suggest you attach YOUR file now, I'm not building something on unknow modificatiosn.
    And please tell me WHAT you want.
    You mention highlighting and now you mention the mail,

  44. #44
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Sorry for the inconvenience just forget about the highlighting section, I'll remove that portion. Just want to check regarding attachment of the calendar sometimes it doesn't work. sometimes I need to close the file and create again a new calendar so the attachment will work. Thanks
    Attached Files Attached Files

  45. #45
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    The reason the pasting does not work is because Outlook expects the fields to be completed like Sent to, CC and subject.

    I add the the following code and disabled the line with application wait, that's unnecessary

    Please Login or Register  to view this content.
    The only thing is you have to check is how many address fields you have in your outlook besides the subject line

    I have BCC on that is why I have 4 tab value, if BCC is nof showing in your outlook then 3 would be enough, try it an see
    You need to be in the message body section for the paste to work

  46. #46
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Thank you, It works fine now. I really appreciate your help.

  47. #47
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I can't stand it when things don't work like they should and I needed to find a solution.
    You can now select one or more task items to be highlighted.
    I tested and it works here.
    Attached Files Attached Files

  48. #48
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    One minor modification, I added a userform that makes it simpler to select the year and month, reduce margin of error
    And well, like the previous post said, I also managed to permit to select one or more tasks to be highlighted.
    If one is already highlighted and you want to highlight a second one you have to select these all again or else the previously highlighted will not be highlighted anymore.

    I activated the coloring of the holidays again but not the restriction, it seems handy to know which day is a holiday, you could even consider adding a comments cell to show which holiday it is, but I'll leave that to your programming imagination.

    I'll stop stalking you (for now)
    Attached Files Attached Files

  49. #49
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Thank you! Please don't stop stalking to me, because I learned a lot with you

  50. #50
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    It looks great! I have some question on how to add additional year? thanks

  51. #51
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    It's dynamic now current year -1 to currentyear + 2 but if you want more change the code in this section of the YaerMonthForm

    Please Login or Register  to view this content.

  52. #52
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    You can also do it this way

    Please Login or Register  to view this content.
    The red is number of extra years

  53. #53
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I discovered a remnant of a non-used variable in a piece of code that is not used and corrected it to work properly
    Its the function LastDay with parameter month number and optional a year to calculate the last day of that month for that year (comes in handy with February)
    And well I also added the extra year while at it with method two
    Attached Files Attached Files

  54. #54
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    wow thanks for the updates. Is it possible to have an option to create to calendar month in one sheet and also option for the attachment if they want to attached the calendar into the mail body or as an File attachement? thank you!

  55. #55
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Do you mean give the choice to mail the calendar sheet as a loose attachment or like it is now pasted in the mail body?
    And if as attachment, how do you want it as an Excel file (no macros) or as a pdf ?

  56. #56
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Do you mean give the choice to mail the calendar sheet as a loose attachment or like it is now pasted in the mail body? - Have an option to attached as loose attachment and as pasted in the mail body
    And if as attachment, how do you want it as an Excel file (no macros) or as a pdf - I think PDF will do.

    Thank you

  57. #57
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I think you know me a little by now
    I've attached the modified file
    When you mail you will get the prompt to choose between 1- Attach the calendar as file, 2- Paste the calendar in the body, 3- both

    The default option is set to 1 but you can change that in the code with the InputBox

    Hope it's to your and you colleague's liking
    Attached Files Attached Files

  58. #58
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    This one is perfect with an option for attachment they are very happy with than part I have another questions if its possible to have an option to insert a 2 month calendar in 1 sheet? but if takes to long to code forget about it I'll explain to them thank you again and again!!

  59. #59
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Hi,
    I hope you're not going to take me wrong but let me explain.

    I love your enthusiasm for Excel and your ideas, I liked this challenge and think I helped you out. No problem there but I offered a finger and now your wanting the whole hand.
    Apart from helping users with VBA issues on one or two forums I also do this type of customizing based upon a user's files as paid service (say for a living).

    You've got the code (the one you already had to create a calendar, well what let's you to dig into that and create a calendar sheet for each month and when the workbook opens it checks if the current month is already there and in not add the new sheet.

    The process will need to be rewritten but that's where VBA comes in, and yes I could program the whole thing and develop an application for you.

    If you analyze the code you have try and picture the process flow and write it down as text in Word or whatever with that laid out try and re-code, you've got sample code and I'm sure you've got ideas.
    You can easily integrate the code I wrote an see if you can make a start, I will gladly help when you're that far but don't take the whole hand when a finger is offered.

    VBA is nothing more than logical thinking with a very basic and necessary requirement, well, at least two basic requirements, namely Time (very much of it) and Imagination (as much as possible)
    Apart from that it's no more that a series of actions that are executed one after the other with conditions and calculations.
    If you write down the actions you want done in words like you would a book then all you need to do after that is 'translate' it into (VBA) code respecting the syntax and you can go a long way with that.
    I started programming in 1983 (HP-Basic).
    When VBA for Applications came by in Office I really got bitten by the VBA bug and since then have 'played' with code and saved all the code I have written in modules I reuse and modify on-the-fly.
    It's fun, honestly, but Time and Imagination
    My BFF Google points me to interesting sites where code is shared and ideas are posted, much of the found code is free to use, nice to mention it in remarks when using it but it gives you ideas.

    Sorry for the whole story and so but I think it's correct to mention how I stand in this and yes, I love to help where I can.

    I will put some ideas in writing (when I have the time) but I suggest you make a start, create a new version, tip use an empty calendar sheet as template since the vba code with the sheet is required for the task selection.

    Make modules public and move them out of the worksheet like the create calendar one, remember you will then have to address the worksheet that you want the calendar created in.
    The worksheet events remain in the worksheets, they do NOT work outside the worksheet module.

    If I didn't scare you off I hope to hear from you

    I suggest you create a new post for this next step you want and drop me a line when you've started.

    Hope to hear from you and your progress ...

  60. #60
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    This is noted Thank you once again

  61. #61
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I helped someone with a similar idea on another forum: https://forums.techguy.org/threads/s...4#post-9500822

    I've attached the last file which might give you ideas, code is different but ... it might help
    Attached Files Attached Files

  62. #62
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Great! I'll check on this Thank you very much I really appreciate all of your help

  63. #63
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi i add additional information in the RefSheet for an additional List but it doesn't show everything, I added 22 information but only 18 information reflects on the TaskList. Thank you!

  64. #64
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    It's a dynamic named range, check the named range which I set to 20 rows.

    Under the Tab Formulas adn name manager check TASK_LIST

    change the settings from 20

    Please Login or Register  to view this content.
    to 60 or more

    Please Login or Register  to view this content.

  65. #65
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Hi, did you manage the TASK_LIST?

    I couldn't abandon you with the VBA development so I put together this file making as little modifications as possible, well, a little more than a little.
    You cannot attach two worksheets but you can create the calendar sheets you need and have to mail them separately.
    The file opens on the HOME worksheet where you will see 12 large round shapes which depict each month in a year, one for each month and a dropdown for the year selection.
    Select the year and press the desired month
    If in the past you will asked to confirm if in the next year the same
    The month calendar sheet is created and the previous calendar create button is just a refresh button which clears the calendar and makes it again.
    Each sheet has a HOME button to return you to the HOME sheet.
    If a calendar was already created the month button will just activate that calendar.

    If you wish to have the option to attach more than one monthly calendar in one message, well that will require some additional code
    Hope you have fun with this one
    Attached Files Attached Files
    Last edited by Keebellah; 05-09-2018 at 04:31 PM. Reason: Extra info

  66. #66
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Wow this is a great enhancement and color combination I like what you did, Thanks also in guiding where to locate the TASK LIST actually I'm not familiar with that section "name manager" I thought it was inside the module or forms. thank you once again.

  67. #67
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    You're welcome.
    I suggest you take a look at this link and save it in four favorites.
    It explains the Name Manager and what you can do with the named ranges
    https://support.office.com/en-us/art...2-abd7ff379c64

  68. #68
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Thank you! Correct me if I'm wrong, I'm not allowed to create future Year Calendar right?

  69. #69
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Correction: you are allowed to add future calendar sheets, just select the drop-down year and the press the months current year -1 to current year + 3
    The sheets will always be in the correct order so if you add January 2019 it will appear at the end and January 2017 as frst, etc.
    I have attached a modified file that permits you to delete selected calendar sheets and prepared for the mailing of one or more calendar sheets as attachment in one mail, this is not yet implemented but the button will appear on the Home sheet as well as well as the delete if there calendar worksheets present.

    You will have to update the task_list since I don't have that data here.
    Enjoy
    Attached Files Attached Files

  70. #70
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Its weird after I select Year 2019 then click calendar month, it doesn't generate a sheet.

  71. #71
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Strange, I'll check when I get home

  72. #72
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Yeah, Thanks I'll check your response tomorrow, Its time to go home now. Have a great day!

  73. #73
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Checked online, that's what I get for going too fast

    Corrected and attached
    Attached Files Attached Files

  74. #74
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Goodmorning, I hope I can add a little 'VBA joy' to your working day.
    Give the attached file a spin and try it out, mailing one or more sheets etc, I think it works nicely (I hope that Murphy stays home today )

    I renamed the file
    Attached Files Attached Files

  75. #75
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi good morning!

    Very happy as always especially the way you code and simplified everything, it works well and perfect. Thank you once again!
    Last edited by 323100N; 05-11-2018 at 06:06 AM.

  76. #76
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi Keebellah,

    How are you? I have some question and I'm not sure if it's possible, I finalized the TASK LIST information and they want to be the font as color coded, instead of using the Highlight Option. here are the list of color combination for each TASK LIST.

    Leave the following text as is:
    • RFP assigned
    • Kickoff email sent to DT
    • Kickoff call conducted
    • Response sent for proofreading
    • Final response submission prep

    Leave the following text as black, but BOLD:
    • 1st draft due to DT
    • 2nd draft due to DT
    • 3rd draft due to DT
    • Final draft due to DT
    • Executive summary draft due to DT
    • Response sent to print
    • Response uploaded to supplier system

    Change the following text to Teal (RGB: 0, 149, 200):
    • Product checklist due to PM
    • All draft edits due to PM
    • Executive summary draft due to PM
    • Executive summary edits due to PM
    • Cover letter due to PM
    • Completed forms due
    • Pricing due to PM
    • Electronic response submitted to customer

    Change the following text to Orange (RGB: 237, 136, 0)
    • Non-standard content due to Compliance for review/approval
    • Compliance review due to PM

    Thank you!

  77. #77
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Now is the time to start practicing. The Highlight is not highlight, is already color coded, the only thing you have to do is apply the color for the selection you want.
    I used vbRed and vbBlack, instead you can use RGB values , so just do that instead of the vbBlack or vbRed
    Of course you will have to include the check to identify the selected text.

  78. #78
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    If you place the colors in a public variable

    Please Login or Register  to view this content.
    then you can use them like the vbBlack and vbRed you use cOrange or cAqua

    I haven't changed anything except added the constants
    Attached Files Attached Files

  79. #79
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Ok thank you, I will try to analyze on how to work on that.

  80. #80
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi,

    I tried to play around the codes inside the OptionButton1_Click() using this code
    Please Login or Register  to view this content.
    But still no luck...

  81. #81
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    And? the result?

  82. #82
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    This text is showing ""Non-standard content due to Compliance for review/approval" in color orange but the rest was unable to insert into calendar.

  83. #83
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    can you attach the file with the list of tasks or just the sheet with the list?

  84. #84
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Thank you, here is the updated task list sheet.
    Attached Files Attached Files

  85. #85
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Thanks, will see if I can make it work as you described

  86. #86
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Thanks a lot and have a great day

  87. #87
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    After (re)reading what you're asking I've come to the conclusion that the whole thing about highlighting and not highlighting is, well, let's say it nicely "all for nothing"
    What you want and have is a pre-arranged signaling for each task, either leave as is, bold, or colored.
    If my assumption is correct then the entire code about selecting to highlight or not is over-kill.
    It does mean that the code has to be reviewed to accommodate but then if you organize it and write the code in such a way to take that into account it will become dynamic.

    You know, I understand that once you have something that works new ideas come up, also, and I am basing this on your questions, the options a possibilities were not thoroughly discussed, you decided to design something but 'forgot' to ask all the questions.

    Will think this over and see what I can come up with, like I said earlier, give them a finger and ...

    You'll hear from me.

  88. #88
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi Keebelah,

    Actually they want to remove the Highlight Radio Button in userform and have a preset color format depending on the task list given. Once the task list was selected it automatically has a color in the calendar. I'm trying if its possible using the if statement on each Tasklist. Thank you

  89. #89
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    The highlight Radio Button had to go anyway since it is obsolete, it's still there but hidden
    The only thing that doesn't work is that when you add a task the previous highlights need to be set, I haven't done that yet.
    What I did was quite simple, you just go to the RefSheet en set the font as you want it displayed, Bold or not (no Italics) and the color of the font
    Just select the font color
    The code takes care of it.
    Attached Files Attached Files

  90. #90
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Wow you are such a genius I also tried that changing color in RefSheet but I cannot execute the code.

  91. #91
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    what code?
    You don't run any code, just change the font color of the task and make it bold or not and then go back to the calendar and add that task

  92. #92
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Yup, and I compare the new code inside the Add selection item radio button and there is an additional code, Is that a code to capture the TASKLIST format in RefSheet?

  93. #93
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Yes, I added a column to the Listbox but the column is hidden, in that item I keep the corresponding row number for that task so that I can use it to read the tasks properties like bold, and color

    Like I mentioned the highlight will be removed if you only add a new task to an existing list, that code needs to be (re)written and will require more time and imagination

  94. #94
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Yeah no worries and again thank you for your time looking into this and for helping.

  95. #95
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I'm sure there are other ways to approach this but this the way I would do it.
    I'll do some testing and see if I can come up with some code to also allow adding a task and keeping the original formatting, this will however imply that the order of the tasks will result in the same alphabetical order as it is in the RefSheet.
    I'll also see if I can manage the deleting of one task which at this moment doesn't really work as it should.
    VBA is all about playing around with conditions, write it down in words and then 'translate' it into (vba)code, that's really the basic idea

  96. #96
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Hi, I think I got it all working now, the Task Lists will be formatted according to the setting you have decided in the RefSheet.
    Unhide and read the textbox 'My Solution'
    In the Userform VBA I removed the Highlight routine since it's no longer necessary but dit leave the Radio Button to 'Refresh Cell Content'
    Let's say you decide to change the Tasklist format in the RefSheet then all you need to do is select the cell in the monthly sheet open the form and click the reset radio button to update the cell content, of course this Radio Button is only visible when the cell has content
    Enjoy the file
    Attached Files Attached Files

  97. #97
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    I noticed that some message boxes were incorrect with the OK / Cancel buttons
    Corrected and have attached updated file (v3.4)
    Attached Files Attached Files

  98. #98
    Forum Contributor 323100N's Avatar
    Join Date
    08-11-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    272

    Re: Insert Dropdown List inside Calendar Days

    Hi Keebellah,

    I hope everything is well with you. I already saw the updated version and its fantastic and working really well. Thank you again for helping and taking your time with this.

    Have a great day.

  99. #99
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Insert Dropdown List inside Calendar Days

    Hi, all's well here.
    Glad to have been able to make it work, I learned 'new tricks' on the way too
    You know where to find me
    Have a great day too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. transfer days from list to calendar format
    By egemencoskun in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-08-2015, 12:37 PM
  2. VLOOKUP that can insert dropdown list
    By Charlotte88 in forum Excel General
    Replies: 6
    Last Post: 09-23-2014, 10:31 PM
  3. [SOLVED] insert State name exactly same as dropdown list
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2014, 12:01 PM
  4. Replies: 0
    Last Post: 06-27-2014, 04:16 PM
  5. Excel 2008 : Colour text inside dropdown list?
    By Bezy in forum Excel General
    Replies: 1
    Last Post: 07-13-2012, 06:43 AM
  6. how do i insert a dropdown list
    By Milen in forum Excel General
    Replies: 2
    Last Post: 01-04-2007, 11:34 AM
  7. how to insert a calendar list and or oricon
    By od in forum Excel General
    Replies: 1
    Last Post: 03-13-2006, 05:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1