Error on adding validation list

[sllaksvb]

Hi all. I created this code to update Categories and validation lists on Sheet1 by what I input in Sheet2. My code was working fine in a module when I was testing it, but when I moved it to Worksheet_Change under Sheet2, it keeps returning an 'Run-time error 1004: Application-defined or object-defined error."

Can anyone advise? Thank you!



Private Sub Worksheet_Change(ByVal Target As Range)

Dim a As Integer, ValidationList As String
Dim i As Integer

a = 1

For i = 3 To 2 + Sheets("Sheet2").Range("BQ1").End(xlToLeft).Column
Sheets("Sheet1").Range("A" & i).Value = Sheets("Sheet2").Cells(1, a).Value

''create validation list
For v = 2 To Sheets("Sheet2").Cells(50000, a).End(xlUp).Row
ValidationList = ValidationList & ", " & Sheets("Sheet2").Cells(v, a).Value
Next v


With Sheets("Sheet1").Range("B" & i & ":G" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="" & ValidationList & ""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

ValidationList = VBANullString

a = a + 1
Next i

[Bob Phillips]

Does this work


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ary As Variant
Dim a As Long, ValidationList As String
Dim i As Long, v As Long

a = 1

For i = 3 To 2 + Sheets("Sheet2").Range("Q1").End(xlToLeft).Column

Sheets("Sheet1").Range("A" & i).Value = Sheets("Sheet2").Cells(1, a).Value

''create validation list
With Sheets("Sheet2")

ary = Application.Transpose(.Range(.Cells(2, a), .Cells(50000, a).End(xlUp)))
ValidationList = Join(ary, ",")
End With

If ValidationList <> "" Then

With Sheets("Sheet1").Range("B" & i & ":G" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="" & ValidationList & ""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

ValidationList = vbNullString
End If

a = a + 1
Next i
End Sub

[sllaksvb]

Hi xld, thank you for your reply. It occasionally produces an type mismatch error on this line

ValidationList = Join(ary, ",")

I managed to solve it by putting my code in Worksheet_Deactivate instead of change, and now it does not produce that error. Thank you!!