How to Use a Multi Conditional Time Stamp

[cpabusinessman]

I'm not the greatest VBA user, but I try.


I have the following issue in regards to creating a time stamp using VBA.



I want to be able to create a time stamp based upon a drop down list in column "E." Based on the variables in column "E," a time stamp should be placed in that same row in a column that corresponds with each different variable.


Example:

Each row in column "E" has a drop down list that contains the following items:

• In Preparation
• On Hold - Awaiting Client info
• In Review
• Billing
• Packaging
• Picked-up

Columns I through N are labeled with headings that correspond with the names in the drop down list.

Whenever an item in the drop down list in column "E" is selected I want a time stamp placed in the same row in the column that corresponds with the selected item.

Additionally, I want it so that a time stamp can only be entered once for each item on the drop list. For instance, when a project is "In Preparation" a time stamp should be place in that row in column "I." When the project is "In Review" a time stamp should be placed in column "K." The important thing is that the time stamp in column "I" is not deleted just because the item selected in the drop down list is now different than the original selection.

If possible, I would like for columns I through N to be only able to be updated with a time stamp once. Once a time stamp is placed in a cell, it shouldn't be able to be altered later.


Attached is what I came up using simple formulas. Obviously, my formulas are limited because every time a new item in the drop down list is selected, the time stamp of the original selection disappears. Also, the formulas don't prevent a user from manually changing columns I through N. Each of those columns should only be able to have data entered into them once (when they are time stamped).


To summarize, I am simply trying to create a macro that will allow a project to be tracked from start through completion. As the project progresses, a time stamp should be entered to document when the project has reached each stage of development. Users should not be able to change a time stamp in any particular cell after it has been entered.



Any help would be greatly appreciated!

[AlphaFrog]

Put this in the sheet's code module

Please Login or Register  to view this content.

[cpabusinessman]

I can't seem to get the above code to work.

I deleted all the formulas in the current file and then inserted the above code in the sheet's "View Code" module.

However, the "Case" usage seems very promising.

[AlphaFrog]

What do you mean exactly by can't get it to work? Does it error, do the wrong thing, does nothing?

[Fotis1991]

As i saw that you started using formulae and as i did an almost same project few days ago,i
offer a solution using formulae.

User can not delete or change anything in range I:N
User can choose from validation list only once. Then List is locked. Can not type anything in Cells with validation list.

The bad news are that user can delete the value in validation list, cell.

[cpabusinessman]

I got it working now....


THANKS!


This is a big help for helping me learn how to properly code!