• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

VBA code to add data validation to cells in Sheet1, where the validation data is in Sheet2

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
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:="=" & rng.Address & ""
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
 
End Sub

Open in new window


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

0
Dale Fye
Asked:
Dale Fye
  • 9
  • 6
1 Solution
 
SiddharthRoutCommented:
Change the line

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:="=" & rng.Address & ""

to

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
        Formula1:="=" & Sheets("Parish Code").rng.Address & ""

and try.

Sid
0
 
SiddharthRoutCommented:
If the above doesn't work then try this

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
        Formula1:="=" & Sheets("Parish Code").Range(rng.Address).Address & ""

Sid
0
 
Dale FyeAuthor Commented:
SiddharthRout,

I tried both and got a couple of errors:
Version 1: 1004, Method 'Sheets' of object '_Global' failed
Version 2: 1004, Method 'Sheets' of object '_Global' failed

Then added the wbk. to Sheets(SourceSheet) to get: wbk.Sheets(SourceSheet)....
Version 1: 438, Object doesn't support this property or method
Version 2: No Error

So, Version #2, with wbk. didn't generate any errors, but it is still using the rng.Address from "Severance Tax", not from the SourceSheet spreadsheet.  Current code looks like:
Public Sub DataVal(wbk As Object, SourceSheet As String, SourceColumn As String, DestCol As String)

    Dim rng As Range
    
    On Error GoTo ProcError
    
    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
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:="=" & wbk.Sheets(SourceSheet).Range(rng.Address).Address & ""
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
 
ProcExit:
    Exit Sub
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error in DataVal"
    Debug.Print "Error in DataVal: " & Err.Number & ", " & Err.Description
    
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
I should have tested it... give me few moments and I will post a tested code.

Sid
0
 
SiddharthRoutCommented:
I think the error is because the validation list is another sheet. We have to save the range in a Named Variable and then pass that here.

Preparing a sample.

Sid
0
 
SiddharthRoutCommented:
Before I create a sample, could you test this for me?

Public Sub DataVal(wbk As Object, SourceSheet As String, SourceColumn As String, DestCol As String)
    Dim rng As Range
    
    On Error GoTo ProcError
    
    Set rng = wbk.Sheets(SourceSheet).Range(SourceColumn & "2:" & SourceColumn _
            & wbk.Sheets(SourceSheet).Cells(65536, SourceColumn).End(xlUp).Row)
         
    wbk.Names.Add Name:="ListRange", RefersToR1C1:="=Parish Code!" & rng.Address
        
    With wbk.Sheets("Severance Tax").Range(DestCol & "10:" & DestCol _
    & wbk.Sheets("Severance Tax").Cells(65536, "A").End(xlUp).Row).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
             Formula1:="=" & "ListRange"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
 
ProcExit:
    Exit Sub
ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "Error in DataVal"
    Debug.Print "Error in DataVal: " & Err.Number & ", " & Err.Description
End Sub

Open in new window


Sid
0
 
Dale FyeAuthor Commented:
Sid,

Won't be able to test until this evening (currently 08:52 AM)
0
 
SiddharthRoutCommented:
Sure fyed :)

Sid
0
 
Dale FyeAuthor Commented:
Sid,

That give me an error:

Error in DataVal: 1004, The formula you typed contains an error.

• For information about fixing common formula problems, click Help.
• To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
• If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (').

0
 
SiddharthRoutCommented:
I think it is because of line 9

wbk.Names.Add Name:="ListRange", RefersToR1C1:="=Parish Code!" & rng.Address

Add "s" after "Parish Code". The actual sheetname is "Parish Codes" ?

If the above doesn't work (which it should) then can you upload a sample file for me?

Sid
0
 
Dale FyeAuthor Commented:
Sid,

Got it resolved.  The problem was with the "RefersToR1C1", it should have just read:

RefersTo

Thanks for your assistance
0
 
SiddharthRoutCommented:
fyed: You are welcome but you had to do the changes that I mentioned above?

Sid
0
 
Dale FyeAuthor Commented:
Here is the sample spreadsheet I used to test.  Works just as I intended.  
DataValidation.xlsm
0
 
Dale FyeAuthor Commented:
Sid,

I wanted it to be more generic, so I passed it a SourceSheet instead of hard coding the source spreadsheet.
0
 
SiddharthRoutCommented:
Ok :)

Sid
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now