Re: [ExcelVBA] Enter text in a cell, but retain original value

 

I have something VERY similar!

There is some differences however.

 

To address your question.

You cannot use conditional formatting to accomplish what you wish, because once it "reverts" to the original date, the "condition" no longer exists! 

 

What I've done, is create a button to modify the selected cells.

I show SCHEDULED half days and full days

then record USED half days and full days.

 

I use "V" for scheduled full days and "H" for scheduled half days.

I use "T" for used full days and "D" for used half days.

 

I select a group of cells, and hit the appropriate button.

The macro keeps the DAY and appends the letter.

 

So, today (5/27/2015) is originally displayed as 27, but showing it as a "used" vacation day becomes 27T (I also change the color to dark blue and the border to show diagonal lines "X").

 

A clear button is used to remove the formatting and read from adjacent cells to restore the date.

 

I also keep track of allocated vacation days and count the number of used days and scheduled days to compare to the total allocated.

 

I'd be glad to send you what I have so you can play...

 

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

---

From: "duncan.edment@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, May 27, 2015 6:12 AM
Subject: [ExcelVBA] Enter text in a cell, but retain original value

 

 Morning.

OK, let me try and explain this!

I have a spreadsheet that is used to record annual leave. Each TAB refers to a different person, and each sheet shows a calendar view for the annual leave year.

Each month entry shows the name of the month, and under that are the days Monday - Sunday. This is repeated for three months on the same section, then the next three months appear below it, then three months, then three months. So, it's laid out as follows:

January
M   T    W   T   F   S    S

                 1    2   3    4
5    6    7    8    9   10  11
12  13 14  15  16  17  18
19  20  21  22  23  24  25
26 27   28  29  30  31

There is the February & March to the right of this, with April, May & June below it, and so on.

There are three options for annual leave...Full day, half-day AM or half-day PM. 'F' is used for Full day, 'A' for half-day AM & 'P' for half-day PM. Using conditional formatting, I can get it colouring the cell as appropriate, based on the character entered. However, I want it to be a bit more 'advance'.

What I'm looking for is the person enters an 'F' against the 17 January above. The cell colours as appropriate, but the cell still contains the value 17 and not the 'F' that was entered.

Possible?

__._,_.___

---

Posted by: Paul Schreiner <schreiner_paul@att.net>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___