+ Reply to Thread
Results 1 to 18 of 18

Code to put today's date in empty cell dependent on selection from drop-down list

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Code to put today's date in empty cell dependent on selection from drop-down list

    I'm a VBA novice looking for a solution to the following please:

    I have a drop down list with 10 different options. Depending on which option is selected I would like today's date to be inserted in a different cell IF that cell is empty. If it's not empty then nothing should happen, the idea being that the date each option was selected will be stored permanently in the relevant cell.
    So, to explain if "Briefed" is selected from the drop down list, I'd like the date to appear in cell M4 (if M4 is empty). If "Quoted" is selected from the list I'd like the date to appear in cell N4 (if N4 is empty) and so on and so forth for 10 options on the list.

    Being a novice this is way above my head - any help anyone can give me would be hugely appreciated!

    Many thanks

    Vicky

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Can you attach your workbook with a before and after example? This will help us understand what goes where.

    To attach a workbook, click 'Go Advanced' below and use the paperclip in the toolbar.

  3. #3
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Hi Solus

    I have attached the workbook. The drop down list I'm referring to is Column F - Status. I'd like the date to be entered in the cells in Columns M:V on the same line as the selection.

    Also I've realised I'll need the column G "Date" to reference the date that gets entered depending on selection. So if "Briefed" is selected in F4, cell G4 displays the data in M4. If "Quoted" is selected in F4, G4 displays Data in N4 etc.

    Many thanks for your help!

    Vicky
    Attached Files Attached Files

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Right click the Overview tab -> view code and paste this into the sheet module:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    That mostly works great, thanks so much! Only problem is the onsite option doesn't add a date, and I've just noticed I didn't put a column in for the "Received" option.

    I'm eager to learn some bits of code myself - would you mind explaining what each line/section does and I'll try to fix it myself?

    Thanks again

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    That's great - I've fixed the glitches too with that information - really appreciate your help

    Any chance you could give me a hint on how to display the relevant date for the selection in Column G please? Normally I would just paste a link but as it needs to change I'm a bit lost.

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Please Login or Register  to view this content.
    I added this line to the old code because I'm not sure what changes you made.

  9. #9
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Thanks for this - I fixed the code to the following to include the Received option:


    Please Login or Register  to view this content.
    The on-site issue was a typo as they weren't spelled the same.

    I can't get the new line of code to work but looking at it I'm not sure I explained myself properly.

    I'd like the date shown in column G to reference the original date the item was selected, (the date columns to the right will be hidden from view to avoid clutter). So if at a later date somebody wants to see when the job was initially received they can do so by reclicking on the "Received" option and it will display the date shown in the column to the right. I hope I'm making sense, probably not!

    Thanks

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Please Login or Register  to view this content.
    Okay this will put a date in Column G the first time the row is changed. If it already has a date in column G because the row was changed before it won't change the value in G.

  11. #11
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Thank you... Ideally I need Column G to change whenever the option in the drop down is changed. Is there a way to change which cell it references? Should I post this as a new thread as it seems like a different problem to the thread title (I'm new to the forum business!)

    I really appreciate your help though, there's no way I'd have worked it out on my own!

  12. #12
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Should it always be changed to today's date?

  13. #13
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    The first time a selection is chosen it should display today's date (ie the date on which it is selected) in the date columns to the right which works perfectly now.

    For column G it should change to the date that each selection was originally selected. For example if the job was marked as "Quoted" on 2nd September, the date would show up in the columns to the right and column G. At a later date, eg 4th September the status would change to "Confirmed" and the date would be entered in the relevant date cell to the right and column G. If then the handler wanted to see what date the job was marked as "Quoted" he could re-select "Quoted" in the drop down and column G would then display the original date it was selected on that is stored in the date cells to the right. What I'm after is for column G to be like a link to the date cells, and the cell that's referenced changes depending on which status is selected.

    Thank you!

  14. #14
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Oh of course! Maybe
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Hi

    Thanks so much for this - I am in awe of your VBA skills!

    I can't test it properly until the morning and the date changes so will let you know tomorrow how it has gone.

    Thanks again

  16. #16
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Hi Sorus

    You're a genius - it works perfectly

    Thank you!

  17. #17
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    Glad to help. And welcome to the forum.

    If you would please take a moment to mark the thread as [SOLVED] using the thread tools at the top.

    Also, you can show appreciation to anyone who helped you by clicking *Add Reputation under any of their posts.

  18. #18
    Registered User
    Join Date
    08-14-2012
    Location
    Bradford, UK
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Code to put today's date in empty cell dependent on selection from drop-down list

    I will mark the thread as closed now - I have added some reputation for the first part of the solution but it won't let me add any more for the second bit!

+ 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. Replies: 4
    Last Post: 05-28-2013, 06:06 AM
  2. Create a sheet with content dependent on the selection of a drop-down list
    By bbaumgardner22 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 04:25 PM
  3. Drop Down List, numeric output dependent on selection
    By Archibald_SM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2013, 06:44 PM
  4. [SOLVED] The VBA code to auto-clear dependent drop down selection when parent cell change.
    By Hotgirl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-29-2012, 07:48 PM
  5. VBA code for creating dependent drop down list
    By singhabhijitkumar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-26-2010, 08:25 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