"Change" Worksheet Event Code 1

[bdc138]

This was my original post:

Here is the basic idea....

A
7 N
8
9
10
11
12

The possible values to be entered are N,S,E,W. I want to create a formula that when A8 is highlighted it enters the appropriate value(N,S,E,W) into A8. In this case N. When A9 is highlighted it would automatically enter N. If the user were to change the value of A9 to S, then when A10 was highlighted "S" would automatically be entered...and so on...and so on....

I received the following code from RobBroekhuis(TechnicalUser)
Apr 4, 2003

Use worksheet events, something like:

dim CurrentDirection as string

sub worksheet_selectionchange
if target.column=1 and target="" then '(an empty cell in column A)
target=CurrentDirection
end if
end sub

sub worksheet_change
if target.column=1 and instr("NSEW",target)>0 then
CurrentDirection=target
end if
end sub

I inserted this as worksheet event code but I'm receiving compile errors. I'm not sure what is wrong. Can anyone provide some guidance. Any help would be much appreciated.

Thanks,
Brent

 

[xlbo]

go to the sheet that needs the code
Right click on the sheet tab
choose View Code
Selection change sub should be automatically generated
Choose "Change" from teh right side drop down
Change Event sub should be automatically generated
Take the code from Rob's subs (minus the sub.... and end sub lines) and paste them into the respective sub outlines in the module
I'm guessing the error was caused 'cos Rob (like me) is too lazy to write out the full (and proper) name of the change event sub and has just indicated which is which - if you paste the code wholesale (including sub names) excel won't recognise them as event subs and therefore won't complie (sic) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bdc138]

Thanks for the help. This works somewhat but I think the problem lies within the line:

if target.column=1 and target="" then '(an empty cell in column A)

because the target.column=1 needs to refer to a merged column which is actually both column A and B. How do I refer to both columns?

Cheers,
Brent

 

[nath]

Hi Brent,

Instead of

Target= ""

try

Target.Cells(1, 1).Value = ""

Nathalie