Looking for a way to allow only three possible entries into a field and
using a one letter entry to spell out the full word of that entry,
Example:

If "P" then (the word) "Pass" is entered.
If "F" then "Fail"
If "N" then N/A

I believe this can only be achieved using a macro, which I have no clue
how to write.

Thanks!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

Two formulas without macros
1) =IF(D19="P","Pass",IF(D19="F","Fail",IF(D19="N","N/A","Invalid")))

2) =LOOKUP(D19,{"F","N","P"},{"Fail","N/A","Pass"})

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Kenshe" wrote in
message ...

Looking for a way to allow only three possible entries into a field and
using a one letter entry to spell out the full word of that entry,
Example:

If "P" then (the word) "Pass" is entered.
If "F" then "Fail"
If "N" then N/A

I believe this can only be achieved using a macro, which I have no clue
how to write.

Thanks!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile:
http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

Try
=IF(A1="P","Pass",IF(A1="F","Fail",IF(A1="N/A","N/A","Put an error message
here")))
Regards,
Alan.

"Kenshe" wrote in
message ...

Looking for a way to allow only three possible entries into a field and
using a one letter entry to spell out the full word of that entry,
Example:

If "P" then (the word) "Pass" is entered.
If "F" then "Fail"
If "N" then N/A

I believe this can only be achieved using a macro, which I have no clue
how to write.

Thanks!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile:
http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

I believe the OP wants to enter the one letter into a cell and have the
resulting word automatically entered into that same cell.
Kenshe
The following macro will do what you want. This is a Worksheet_Change
event macro and is triggered automatically upon any change to the contents
of any cell in the sheet. I'm assuming that you want this to work within a
specific range only and not all over the sheet. The range A1:A10 is used in
this macro. Change it to suit your needs. Note that this macro works with
either case of P, F, and N.
This macro must be placed in the sheet module of the pertinent sheet.
To do that, right-click on the sheet tab, select View Code, and paste this
macro into that module. Click on the "X" in the top right corner to get
back to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If UCase(Target) = "P" Then
Target = "Pass"
Else
If UCase(Target) = "F" Then
Target = "Fail"
Else
If UCase(Target) = "N" Then Target = "N/A"
End If
End If
End If
End Sub

wrote in message
...

Looking for a way to allow only three possible entries into a field and
using a one letter entry to spell out the full word of that entry,
Example:

If "P" then (the word) "Pass" is entered.
If "F" then "Fail"
If "N" then N/A

I believe this can only be achieved using a macro, which I have no clue
how to write.

Thanks!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile:
http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

I use a similar example and this works for me
Try Auto Correct P = Pass, F= Fail, N = N/A. Then use data validation For
these letters.
Paul.


On 18/11/05 9:55 AM, in article
, "Kenshe"
wrote:


Looking for a way to allow only three possible entries into a field and
using a one letter entry to spell out the full word of that entry,
Example:

If "P" then (the word) "Pass" is entered.
If "F" then "Fail"
If "N" then N/A

I believe this can only be achieved using a macro, which I have no clue
how to write.

Thanks!

Ken

That is correct OM, I desire to do just that, I will give it a go
today.

Thanks to all for the quick response! I will post back with the
results.

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

Hey thanks Otto, it works great, should help speed up the process
greatly!

One other possibility though, is there another line of code that could
possibly be added to restrict the cell from allowing anything but P, F
or N/A?

Again, Thanks!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

Ken
Here it is. I changed the code to a Select Case construct. That is
easier to follow than the nested "IF" statements. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
Select Case UCase(Target.Value)
Case "P": Target.Value = "Pass"
Case "F": Target.Value = "Fail"
Case "N": Target.Value = "N/A"
Case Else
MsgBox "Only 'P', 'F', or 'N' are allowed in this cell."
Target.ClearContents
End Select
Application.EnableEvents = True
End If
End Sub

"Kenshe" wrote in
message ...

Hey thanks Otto, it works great, should help speed up the process
greatly!

One other possibility though, is there another line of code that could
possibly be added to restrict the cell from allowing anything but P, F
or N/A?

Again, Thanks!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile:
http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

Otto,
Works great, your help and knowledge is much appreciated to a novice
like me.

If possible, I have one more request,

I am looking for a way to gather up all the "comments" into a single
report sheet. I have 29 sheets in the work book, the 30th sheet is
where I would like to copy any comments we may have. The cell range
where a comment can only be put (from the 29sheets) is B25:IV29.
Right now I use Word imbedded into sheet 30 and copy and paste. so, I
am looking for an easier way to compile all the comments to read in a
single report. Word works good for this task, but the time it takes to
copy and paste is the issue.

Thanks much!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

Dave,

Thanks for the macro, it really works great! The other macro from your
suggested site is a little more problematic for what I am trying to
achieve. I have been working with the one you posted and I am having a
little trouble in modifying it.
It works great and is ready to use, but I notice I needed to generate
the "comment" report by date. I wanted to generate a new report every
day leaving out the previous comments already generated the day before.
Another words, the current comments for that day are the only coments
being reported. For once I create a report and print it, I delete the
created report page (sheet 1) for that day. I dont want to keep
reporting the old comments along with the new.

Here is the modified macro I am using:

Private Sub CommandButton1_Click()
Dim cmt As Comment
Dim wks As Worksheet
Dim rptWks As Worksheet
Dim DestCell As Range
ActiveWorkbook.Unprotect
Set rptWks = Worksheets.Add
With rptWks
.Range("a1").Resize(1, 3).Value _
= Array("Sheet", "Location", "Comment")
Set DestCell = rptWks.Range("a2")
End With

With ActiveSheet.Range("C1")
.ColumnWidth = 600 / .Width * .ColumnWidth
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
For Each cmt In wks.Comments
DestCell.Value = "'" & wks.Name
DestCell.Offset(0, 1).Value = cmt.Parent.Address(0, 0)
DestCell.Offset(0, 2).Value = cmt.Text
Set DestCell = DestCell.Offset(1, 0)
Next cmt
End If
Next wks
Sheets("Comment Rpt").Select
ActiveWorkbook.Protect Structu=True, Windows:=False
End Sub

Private Sub CommandButton2_Click()

'
' SheetDelete Macro
' Macro recorded 12/9/2005 by Oliver User
'

'
Sheets("Sheet1").Select
ActiveWorkbook.Unprotect
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Protect Structu=True, Windows:=False
End Sub


Maybe it would be better not use the "comment" window and just enter
any needed comments in a cell and use a bunch of "if then" statements
to do what I need with more avenues of attack??

Thanks again for all your help!

Ken


--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

I don't see anything where you're keeping track of when the comments were
added. And that's gonna make the solution much more difficult than it has to
be. (I think you'd have to keep the historical comments, compare addresses of
the comment's cell and then compare the comment itself--sounds like a problem
just waiting to happen!)

Personally, if I have a choice between using comments and using cells, I'll use
cells. There's lots of things you could do based on cells (autofilter/sorting)
that become a problem with comments.

I think I'd use cells to contain the info. In fact, I'd add another column that
included the date that the "comment" was added. Then you could retrieve any set
of comments you wanted.

And you could have an even macro fire each time you changed a cell in a certain
column that would put the date in another column.

J.E. McGimpsey shows how at:
http://www.mcgimpsey.com/excel/timestamp.html

Kenshe wrote:

Dave,

Thanks for the macro, it really works great! The other macro from your
suggested site is a little more problematic for what I am trying to
achieve. I have been working with the one you posted and I am having a
little trouble in modifying it.
It works great and is ready to use, but I notice I needed to generate
the "comment" report by date. I wanted to generate a new report every
day leaving out the previous comments already generated the day before.
Another words, the current comments for that day are the only coments
being reported. For once I create a report and print it, I delete the
created report page (sheet 1) for that day. I dont want to keep
reporting the old comments along with the new.

Here is the modified macro I am using:

Private Sub CommandButton1_Click()
Dim cmt As Comment
Dim wks As Worksheet
Dim rptWks As Worksheet
Dim DestCell As Range
ActiveWorkbook.Unprotect
Set rptWks = Worksheets.Add
With rptWks
Range("a1").Resize(1, 3).Value _
= Array("Sheet", "Location", "Comment")
Set DestCell = rptWks.Range("a2")
End With

With ActiveSheet.Range("C1")
ColumnWidth = 600 / .Width * .ColumnWidth
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
For Each cmt In wks.Comments
DestCell.Value = "'" & wks.Name
DestCell.Offset(0, 1).Value = cmt.Parent.Address(0, 0)
DestCell.Offset(0, 2).Value = cmt.Text
Set DestCell = DestCell.Offset(1, 0)
Next cmt
End If
Next wks
Sheets("Comment Rpt").Select
ActiveWorkbook.Protect Structu=True, Windows:=False
End Sub

Private Sub CommandButton2_Click()

'
' SheetDelete Macro
' Macro recorded 12/9/2005 by Oliver User
'

'
Sheets("Sheet1").Select
ActiveWorkbook.Unprotect
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Protect Structu=True, Windows:=False
End Sub

Maybe it would be better not use the "comment" window and just enter
any needed comments in a cell and use a bunch of "if then" statements
to do what I need with more avenues of attack??

Thanks again for all your help!

Ken

--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

--

Dave Peterson