PDA

View Full Version : Solved: Create Validation List In Next Cell



CreganTur
08-29-2008, 07:45 AM
Google is failing me today:igiveup:

Cell G2 has a validation list where the value is the named range 'TypeNames'.

When no selection has been made the cell is, of course, blank (or the cell = ""). What I would like have happen is when a User changes the value of G2 to something other than "", I would like the worksheet to automatically create a validation list in H2 that is also set to the named range 'TypeNames'.

This needs to be a continuing process, so that whenever you use a validation list the very next cell, which was previosuly a general cell, becomes a validation list.

I need this to control User input as they are filling out the worksheet.

Bob Phillips
08-29-2008, 08:32 AM
Google is failing me today:igiveup:

But VBAX never does!



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G2:Z2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
With .Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=TypeNames"
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

CreganTur
08-29-2008, 08:42 AM
But VBAX never does!

How very true!

You are truly a master of your art.

Aussiebear
08-29-2008, 02:49 PM
You are truly a master of your art.

We have been trying to tell him that....