data validation updating

[yeaster]

Hi, I'm trying to create a drop down list using data validation. When I run my macro to extract the data from the drop down list, the values will turn to 0 or blank. Can anyone help me on this? I've attached a sample of what I have now, the sheet 'change' has the cell that needs to be changed when values are chosen.

Also is it possible for it to become automated and not need to have a button to update the values? Is there any code to solve my problem?

Thanks in advance.

[BigBas]

Rather than create a macro, you can automate it using a worksheet change event.

In the VBE, right click on the sheet that you would like to apply the event code (in this case, the Menu sheet). Next, use this code:

Please Login or Register  to view this content.

report back

[yeaster]

It seems alittle cranky. The values are not updating accurately. The day number will definately update properly, but the month and year are not. At times it doesn't update or the wrong month is shown.

Also if I just re write this code to also have the ability to choose and update end date too?

Thank you so much for your help!

[BigBas]

Yeaster:

To be very honest with you, I wasn't entirely sure what you wanted to happen. That said, I just fixed your existing code, and change the event type that initiates the code (worksheet change instead of clicking a button).

If you can specify what should happen and where you would like to it work, I'll be glad to take another look at the file and fix it further.

[yeaster]

I figured how to make the code work. But now I'm facing one small problem. When a single digit number(01 - 09)for day number is selected, it'll appear as 1 and not 01. Is there anyway for vba to follow the format and not remove the 0 that is infront of the number?

[BigBas]

Your best bet is to format the cells that these numbers go into directly. For example, if your info will go into column A, then highlight column A, right click on the selection, go to Format Cells--Custom, and enter this as your format: 00

Let me know if that works.

[yeaster]

It doesn't work, cause my cell that the info will go has words with it too. Is there a workaround for this?