Edit a cell depending on previous choice.

[Hoopsah]

Hi,

looking for help again.

I have a spreadsheet that has a number of user input details.

One of the questions asks the process number, I have done this via a data => Validation, and have allowed only the numbers 1,2,3,4 & 5

If the user chooses Process No 1, then the box below needs to be filled in (2 choices, Standard or Non-Standard)

If any other process is chosen then there is no need to complete this box.

What I would like is if the user chooses Process 2,3,4, or 5 then the next box gets blanked out and no entry can be made, if however, Process 1 is chosen then the next box should be open for editing (Preferable another Validation choice)

Any help would be appreciated, if you want me to post the screen that I am working on I shall, or if I haven't explained things clearly enough.

Cheers for any help

[grichey]

box text box?

[Hoopsah]

See I knew English would get in the way.

My bad, when I said box I actually meant cell.

[Simon Lloyd]

This goes in the worksheet code module:
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If Target.Value = 1 Then
Target.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=choices" 'choices was my named range
End With
ElseIf Target.Value = 2 Then
Target.Offset(2, 0).Select
ElseIf Target.Value = 3 Then
Target.Offset(2, 0).Select
ElseIf Target.Value = 4 Then
Target.Offset(2, 0).Select
ElseIf Target.Value = 5 Then
End If
End Sub
[/VBA]

[Hoopsah]

Hi Simon,

Thanks for your help.

One more thing though, I already have worksheet code for this particular module and I am unsure where and how to fit your coding into it. my current coding is:

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H9" '<== change to suit
Dim pos As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

On Error Resume Next
pos = Application.Match(.Value, Worksheets("Process 2").Columns(3), 0)
On Error GoTo 0
If pos > 0 Then

Me.Range("H12").Value = Worksheets("Process 2").Cells(pos, "B").Value
Me.Range("H14").Value = Worksheets("Process 2").Cells(pos, "D").Value
Me.Range("H16").Value = Worksheets("Process 2").Cells(pos, "E").Value
Me.Range("H18").Value = Worksheets("Process 2").Cells(pos, "F").Value
Me.Range("H20").Value = Worksheets("Process 2").Cells(pos, "H").Value
End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[Hoopsah]

??

[Ago]

im having problems understanding the problem here.
and it didnt make it easier when you suppy us with some code that seems to do the job.

[grichey]

have you tried just inserting it into the bottom of your code? I haven't, but trial and error gets you a long ways.

[Hoopsah]

Uch! I Know, trial and error and all that.

Main reason I re-posted this is that I have spent all Friday, Saturday and most of Monday trying with lots of errors.

To be 100% honest, I don't really understand the code that Simon provided, that is probably why I am struggling to get it to fit into the coding I already have.

[Simon Lloyd]

This is just a wild shot in the dark but - how about posting a workbook?, i know its a crazy idea but it may just work i'm not sure it's ever been attempted before as we are all mind readers but i'm not proud i'll take myself down a peg or two and look at a workbook if you provide one!

[Hoopsah]

As requested, I have attached a copy of the workbook that I am having trouble with, purely for all the non-mindreaders,

Cheers.

[Simon Lloyd]

I just slotted it in and adjust cells to work, also just added a named range in sheet lists for the validation.

[Hoopsah]

Thanks for your help Simon.