Assign data validation drop down list using range of cells

[Vincent88]

Hi All, How to assign a range of cells to the dropdown list ? FORMULA1 seems not recognize the range of cells !

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name = "Data" Then Exit Sub


Dim rngg As Range
Dim lr As Long
lr = Range("A2").End(xlDown).Row
Set rngg = Range("C3:AL" & lr)

On Error Resume Next

If Target.CountLarge > 1 Then Exit Sub

If Not Intersect(Target, rngg) Is Nothing Then

With rngg.Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  Operator:=xlBetween, Formula1:=Sheets("Data").Range("G1:G" & Range("G1").End(xlDown).Row)   

 .IgnoreBlank = True
 .InCellDropdown = Truem
 .InputTitle = ""
 .ErrorTitle = ""
 .InputMessage = ""
 .ErrorMessage = ""
 .ShowInput = True
 .ShowError = True
End With

End If

End Sub

 

[JoeMo]

You may need to qualify the sheet that range is on:
Formula1:=Sheets("Data").Range("G1:G" & Sheets("Data").cells(rows.Count,"G").end(xlup).Row)

 

[OilEconomist]

Hi All, How to assign a range of cells to the dropdown list ? FORMULA1 seems not recognize the range of cells !

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Sh.Name = "Data" Then Exit Sub


Dim rngg As Range
Dim lr As Long
lr = Range("A2").End(xlDown).Row
Set rngg = Range("C3:AL" & lr)

On Error Resume Next

If Target.CountLarge > 1 Then Exit Sub

If Not Intersect(Target, rngg) Is Nothing Then

With rngg.Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  Operator:=xlBetween, Formula1:=Sheets("Data").Range("G1:G" & Range("G1").End(xlDown).Row)  

 .IgnoreBlank = True
 .InCellDropdown = Truem
 .InputTitle = ""
 .ErrorTitle = ""
 .InputMessage = ""
 .ErrorMessage = ""
 .ShowInput = True
 .ShowError = True
End With

End If

End Sub

@Vincent88 This post may assist you: VBA Code for Data Validation on Another Sheet.

Also, in your line of code: Sh.Name = "Data", don't you need something like the following before it so you get the sheet name and test it.
Dim Sh.Name as String
Sh.Name = ActiveSheet.Name

Also, how are you getting to the active sheet? I assume you posted only part of your code. I always take my code and make another workbook and test it to make sure somebody can take my code and test it easily on a workbook of their own.

 

[johnnyL]

Also, how are you getting to the active sheet? I assume you posted only part of your code. I always take my code and make another workbook and test it to make sure somebody can take my code and test it easily on a workbook of their own.

@OilEconomist Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range), if code is placed properly, will refer to the active sheet.