Hi,

I have a validation list (H1:H5), I hope, using VBA, the list can
automatically attach itself to any cell in the first column of the worksheet
when the cell gets input focus.

How can I do this?

Thanks.

Why not just select column a and apply Data=Validation.

--
Regards,
Tom Ogilvy



"John" wrote:

Hi,

I have a validation list (H1:H5), I hope, using VBA, the list can
automatically attach itself to any cell in the first column of the worksheet
when the cell gets input focus.

How can I do this?

Thanks.

Thank you very much.

"Tom Ogilvy" wrote:

Why not just select column a and apply Data=Validation.

--
Regards,
Tom Ogilvy



"John" wrote:

Hi,

I have a validation list (H1:H5), I hope, using VBA, the list can
automatically attach itself to any cell in the first column of the worksheet
when the cell gets input focus.

How can I do this?

Thanks.

Paste this code into the specific Sheet module you want this to happen
on:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns(1)) Is Nothing Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=TheGuys" 'This refers to an existing
Named Range
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub

Worked for me...
Jim May



"John" wrote in message
:

Hi,

I have a validation list (H1:H5), I hope, using VBA, the list can
automatically attach itself to any cell in the first column of the worksheet
when the cell gets input focus.

How can I do this?

Thanks.

Not sure if in right spot. Want to try for validation of entry in a column
"D" befor any thing else can be entered in row. Up to 100 rows possible. Is
this possible? Other things rely on entry in the column for sorting. Could be
left out and would spoil sort.
Thanks



"JMay" wrote:

Paste this code into the specific Sheet module you want this to happen
on:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns(1)) Is Nothing Then
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=TheGuys" 'This refers to an existing
Named Range
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub

Worked for me...
Jim May



"John" wrote in message
:

Hi,

I have a validation list (H1:H5), I hope, using VBA, the list can
automatically attach itself to any cell in the first column of the worksheet
when the cell gets input focus.

How can I do this?

Thanks.