Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "RUNNING" Application.EnableEvents = False
Select Case Target.Value
Case 1
Target.Value = "Read Only"
Case 2
Target.Value = "Assessor"
Case 3
Target.Value = "Reviewer"
End Select
Application.EnableEvents = True
End Sub


???

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"excelnaive" wrote in message
...
In a event macro I have to specify the column I want to change but is it
possible to do it generically I mean not tying it to one column or row and
instead put the functionality in to work for any column/row and let the user
choose which column he wants that format in?Is it possible to do that?
This is what I currently have....So how I change it so that I need not
specify target.column and make it generic......

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "RUNNING"
If Target.Column = 5 Then
Application.EnableEvents = False
Select Case Target.Value
Case 1
Target.Value = "Read Only"
Case 2
Target.Value = "Assessor"
Case 3
Target.Value = "Reviewer"
End Select
Application.EnableEvents = True
End If

End Sub

This works can I tie this functionality to menu bar items like format o
data validation etc

--
Message posted from http://www.ExcelForum.com

Great Logic!!! But how will the code above specify that entering 1,2 o
3 is based on the column that user selects in the sense how would yo
achieve the functionality of format cell where the user selects a rang
or specifies a particular cell he wants to format??:) :confused

--
Message posted from http://www.ExcelForum.com

No, it's worksheet event code, so it is triggered by changing a cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"excel_naive " wrote in message
...
This works can I tie this functionality to menu bar items like format or
data validation etc?


---
Message posted from http://www.ExcelForum.com/

Hi,
I understand that it is a worksheet driven event but th
problem is by not specifying the column name this is applied to th
entire worksheet.But if I could get this working in the custom forma
menu then when a user chooses which column he wants to apply thi
format of 1-readonly,2-assessor,3-reviewer ,he will right click o
format cells and pick this proc and run it..So we can get mor
reusability..... So I am looking at tying this to custom format menu o
change the existing buttons to perform this functionlaity.


:

--
Message posted from http://www.ExcelForum.com

Either I am misunderstanding you, or you me.

Event code doesn't get picked, it runs automatically when the event occurs.
This code will fire whenever there is a change in the value.

You can also add code to trap the range to work upon.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"excel_naive " wrote in message
...
Hi,
I understand that it is a worksheet driven event but the
problem is by not specifying the column name this is applied to the
entire worksheet.But if I could get this working in the custom format
menu then when a user chooses which column he wants to apply this
format of 1-readonly,2-assessor,3-reviewer ,he will right click on
format cells and pick this proc and run it..So we can get more
reusability..... So I am looking at tying this to custom format menu or
change the existing buttons to perform this functionlaity.


:)


---
Message posted from http://www.ExcelForum.com/

Hi Bob,
:) Thanks for the prompt reply.....Ok....let me explai
in detail what I am looking for......

The above event will occur when a user clicks 1,2 or 3 anywhere in th
worksheet correct??But ....I dont want that...I want the user t
specify the column he wants this format to apply....
for e.g the user picks up col 5 and right clicks on format cells ...
want this function to be seen in any of the drop down we have ther
like the custom,text etc etc so can I add a format of my own and plac
it there????

In other words can I recode this to get the user selected rang
and apply it to only that range and NOT the whol
worksheet?????:confused

--
Message posted from http://www.ExcelForum.com

Okay, here's a shot

Sub ChangeText()
Dim cell As Range
Application.EnableEvents = False
For Each cell In Selection
Select Case cell.Value
Case 1
cell.Value = "Read Only"
Case 2
cell.Value = "Assessor"
Case 3
cell.Value = "Reviewer"
End Select
Next cell
Application.EnableEvents = True
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"excel_naive " wrote in message
...
Hi Bob,
:) Thanks for the prompt reply.....Ok....let me explain
in detail what I am looking for......

The above event will occur when a user clicks 1,2 or 3 anywhere in the
worksheet correct??But ....I dont want that...I want the user to
specify the column he wants this format to apply....
for e.g the user picks up col 5 and right clicks on format cells ...I
want this function to be seen in any of the drop down we have there
like the custom,text etc etc so can I add a format of my own and place
it there????

In other words can I recode this to get the user selected range
and apply it to only that range and NOT the whole
worksheet?????


---
Message posted from http://www.ExcelForum.com/

Hey Bob,
I can get this proc working I pasted it in the code an
executed it and when I select a column and enter 1,2 or 3 nothing
happening :

--
Message posted from http://www.ExcelForum.com