I've got an Access application that is dumping a bunch of data in an Excel workbook. Once there, my user wants to have dropdown lists in some of the columns that are based upon data which exists in several hidden worksheets. I made a few changes to some code I found here on EE, shown below.
Public Sub DataVal(wbk As Object, SourceSheet As String, SourceColumn As String, DestCol As String)
Dim rng As Range
Set rng = wbk.Sheets(SourceSheet).Range(SourceColumn & "2:" & SourceColumn _
& wbk.Sheets(SourceSheet).Cells(65536, SourceColumn).End(xlUp).Row)
With wbk.Sheets("Severance Tax").Range(DestCol & "10:" & DestCol _
& wbk.Sheets("Severance Tax").Cells(65536, "A").End(xlUp).Row).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=" & rng.Address & ""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
I then call this sub like: Call DataVal(wbk, "Parish Codes", "A", "C")
However, when it gets to the .Add line the value of rng.Address looks like "$A$2:$A$12" and the dropdown list that it creates uses the values of those cells in the "Severance Tax" worksheet instead of those cells from the "Parish Codes" worksheet.
So, how do I modify this code so that it will point to: 'Parish Codes'!$A$2:$A$12