OK, here is what I am trying to accomplish: I have one cell (M12) that uses a data validation list that is tied to all the names of the worksheets in the workbook. I want the user to be able to select the worksheet they want to use in the first validation list, then have a second validation list update with data from the selected sheet. I have the following code, but something is not right.
I get a 1004 application defined or object defined error on this line:
I think it is in my Formula1:= section, something not right with how I am calling 'sheetname' into the code.
Any help is greatly appreciated! Thanks!
Code:
Sub ValidateOptions()
Dim sheetname As String
Dim last
sheetname = Range("M12").Text
last = Range("X1").End(xlDown).Offset(0, 0).Row
Range("M13").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='sheetname'!$X$1:$X$" & last
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Code:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='sheetname'!$X$1:$X$" & last
Any help is greatly appreciated! Thanks!