Hi I have this code to create a Calendar and I want to add a Dropdown List for each days in the Calendar.
Thank you!Please Login or Register to view this content.
Hi I have this code to create a Calendar and I want to add a Dropdown List for each days in the Calendar.
Thank you!Please Login or Register to view this content.
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
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.
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!
This is what's the information looks like in the Calendar. Thank you!
You can take care of the formatting.
Try this one, I added the code to your own macro (with dimensioned variables)
You'll have to add a comma (,) between Draft and Reviews to split that line too, forgot it
Thank you this work perfect! I have a question is it possible to have a multiple selection in dropdown list per day?
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 ?
Last edited by 323100N; 04-26-2018 at 03:15 AM.
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
Yes Please, any help is very much appreciated. thank you!
I already 'wrote' in my head now put it in code
You are such a Macro Expert!!
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
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.
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.
With the current font size (on my system) the cell is nicely filled if all items are selected
Font is Calibri size 10
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.
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?
Sorry forgot about the statement and arguments I thought the selections has this statement. Thank you!
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'
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
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.
Last edited by Keebellah; 04-30-2018 at 02:05 AM. Reason: Corrected Attachment
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!
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.
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
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.
Thank you!Please Login or Register to view this content.
Last edited by 323100N; 04-30-2018 at 10:20 AM.
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
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?
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
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.
please? Thank you very much
Just change this section for the Holiday restricton
This is all you needPlease 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 datePlease Login or Register to view this content.
yeah my bad! hahahaha..I'll update the holiday section code. once again thank you
Let me know if you get it to work like you want
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
Please Login or Register to view this content.
Please Login or Register to view this content.
You did not do what I told you, all you dis was deactivate the lines of code, but you deactivated ALL
DO NOT deactivate the Green linePlease Login or Register to view this content.
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
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)
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.
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,
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
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
The only thing is you have to check is how many address fields you have in your outlook besides the subject linePlease Login or Register to view this content.
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
Thank you, It works fine now. I really appreciate your help.
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.
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)
Thank you! Please don't stop stalking to me, because I learned a lot with you
It looks great! I have some question on how to add additional year? thanks
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.
You can also do it this way
The red is number of extra yearsPlease Login or Register to view this content.
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
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!
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 ?
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
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
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!!
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 ...
This is noted Thank you once again
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
Great! I'll check on this Thank you very much I really appreciate all of your help
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!
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
to 60 or morePlease Login or Register to view this content.
Please Login or Register to view this content.
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
Last edited by Keebellah; 05-09-2018 at 04:31 PM. Reason: Extra info
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.
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
Thank you! Correct me if I'm wrong, I'm not allowed to create future Year Calendar right?
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
Its weird after I select Year 2019 then click calendar month, it doesn't generate a sheet.
Strange, I'll check when I get home
Yeah, Thanks I'll check your response tomorrow, Its time to go home now. Have a great day!
Checked online, that's what I get for going too fast
Corrected and attached
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
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.
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!
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.
If you place the colors in a public variable
then you can use them like the vbBlack and vbRed you use cOrange or cAquaPlease Login or Register to view this content.
I haven't changed anything except added the constants
Ok thank you, I will try to analyze on how to work on that.
Hi,
I tried to play around the codes inside the OptionButton1_Click() using this code
But still no luck...Please Login or Register to view this content.
And? the result?
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.
can you attach the file with the list of tasks or just the sheet with the list?
Thank you, here is the updated task list sheet.
Thanks, will see if I can make it work as you described
Thanks a lot and have a great day
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.
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
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.
Wow you are such a genius I also tried that changing color in RefSheet but I cannot execute the code.
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
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?
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
Yeah no worries and again thank you for your time looking into this and for helping.
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
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
I noticed that some message boxes were incorrect with the OK / Cancel buttons
Corrected and have attached updated file (v3.4)
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks