Cell validation

[NikNik]

I have validation in one cell based on a value in an adjacent cell i.e. user selects "numbers" in cell A1 and the choices in A2 are "1","2" and "3", user selects "letters" in cell A1 and the choices in A2 are now "a","b" and "c".

The only problem is that, having made a selection in cell A2, if the user then goes back and changes cell A1, A2 retains the same (now invalid) value.

I am using Excel's standard cell validation - is there any way I can better link the 2 cells or trap the fact that the value in A1 has changed and at least clear the value from A2?

 

[lenze]

How about a Change event?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address<>"$A$1" Then Exit Sub
Range("$A$2").ClearContents
Range("$A$2").Select
End Sub

 

[NikNik]

Thanks lenze.

In the real spreadsheet where i am using this, the value in cell A1 is also passed into a function in another cell. This function has to be set as volatile and as a result of this the rest of the worksheet_change procedure never seems to be run i.e. the clearcontents bit?

Help...?

 

[lenze]

I'm not clear on what you are saying. If A1 is changed by user entry or selection from a validation dropdown, the code should run. The fact that The value in A1 is being used by another cell should have no impact. Where did you place the code. It needs to be in the WorkSheet module. Post back if I'm missing something

 

[NikNik]

Sorry - try this

The code you provided is in the worksheet module and works fine under normal circumstances.

However I also have the following function in Module 1:

Function testfunc(a As Range, b As Range)

Application.Volatile
testfunc = a.Value & b.Value

End Function

And cell C2 on the worksheet contains =testfunc(A1,A2)

The worksheet_change code is always called (checked it in debug) but the clearcontents line isn't run when application.volatile is set in the function...comment this line out and it all works fine??

Any ideas?

 

[lenze]

Can you not just use the formula =A1&A2 in C2? I don't see the need for the UDF
This message was edited by lenze on 2002-08-27 07:42

 

[NikNik]

Yeh - the snippets I have posted are just to provide examples to work with, the function in the real spreadsheet is pretty complex. It also needs to be volatile because of the way the spreadsheet is used, but it appears to be the volatile setting that is causing the problem.

 

[lenze]

I'm really not sure what's happening, but just for grins try adding this to the code

Application.Calculation = xlCalculationManual
Range("$A$2").ClearContents
Application.Calculation = xlCalculationAutomatic

or maybe

Application.Volatile = False
Range("$A$2").ClearContents
Application.Volatile = True

Just a guess

 

[NikNik]

Nah - sorry lenze still no joy - any other thoughts..

P.S. Thanks for trying

 

[lenze]

Maybe change the code to

Range("$A$2").Select
Selection.ClearContents