Excel Vba Problem - Validate Data in cell

E

excel_naive

Hello,
I have been trying to validate the entry & change its valu
in a cell when user enters it.For Eg:
In column G I have enteries for permissions as 1,2,3
which have to be interpreted as
1-Read Only
2-Assessor
3-Reviewer
I have written a macro that searches and replaces them.But what I a
looking for is the value of the cell should change as soon as the use
enters its value .So when user enters 1 its hould change to Read-onl
 
C

chris

put this in your sheetchange even
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range
Sto
If Target.Row = 7 The
Select Case Targe
Case
Target = "Read Only
Case
Target = "Assessor
Case
Target = "Reviewer
Case Els
MsgBox ("Use values: 1, 2, 3 only!"
Target.Selec
Target = "
End Selec
End I
End Su

----- excel_naive > wrote: ----

Hello
I have been trying to validate the entry & change its valu
in a cell when user enters it.For Eg
In column G I have enteries for permissions as 1,2,
which have to be interpreted as
1-Read Onl
2-Assesso
3-Reviewe
I have written a macro that searches and replaces them.But what I a
looking for is the value of the cell should change as soon as the use
enters its value .So when user enters 1 its hould change to Read-onl


This is what I have

Sub ChgInfo(
Dim Sht As Workshee
For Each Sht In Worksheet
Columns("G:G").Selec
Selection.Replace What:="1", Replacement:="ACTIVE USER"
SearchOrder:=xlByRows, MatchCase:=Fals
Sht.Cells.Replace What:="1",
Replacement:="1-Read Only", SearchOrder:=xlByColumns
MatchCase:=Fals
Sht.Cells.Replace What:="2",
Replacement:="2-Assessor", SearchOrder:=xlByColumns
MatchCase:=Fals
Sht.Cells.Replace What:="3",
Replacement:="3-REVIEWER", SearchOrder:=xlByColumns
MatchCase:=Fals

Nex
End Su

I will be thankful if anyone can help me resolve this asap

Thanks in advanc
 
C

chris: sorry, error in last posting

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range
Sto
If Target.Column = 7 Then ' use Column not row
Select Case Targe
Case
Target = "Read Only
Case
Target = "Assessor
Case
Target = "Reviewer
Case Els
MsgBox ("Use values: 1, 2, 3 only!"
Target.Selec
Target = "
End Selec
End I
End Sub
 
1

1 more thing

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range
Sto
If Target.Column = 7 The

If Target = "" Then Exit Sub ' you need this for empty cell

Select Case Targe
Case
Target = "Read Only
Case
Target = "Assessor
Case
Target = "Reviewer
Case Els
MsgBox ("Use values: 1, 2, 3 only!"
Target.Selec
Target = "
End Selec
End I
End Sub
 
E

excel_naive

Hi,
Thanks a lot for the reply but I have couple of question
when you say put this in your sheetchange event...does it mean I hav
to code another event??I am not sure how to run this macro as I a
pretty new to VBA.It would be great if you can help me figure tha
out....


Thanks again
 
Top