sllaksvb
10-19-2017, 08:04 AM
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
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