Solved

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

Posted on 2011-02-23
15
469 Views
Last Modified: 2012-05-11
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
Comment
Question by:Dale Fye (Access MVP)
  • 9
  • 6
15 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966717
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966722
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 34966852
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966867
I should have tested it... give me few moments and I will post a tested code.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966873
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34966891
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 34969797
Sid,

Won't be able to test until this evening (currently 08:52 AM)
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34969836
Sure fyed :)

Sid
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 34976299
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34978864
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
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 34979296
Sid,

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

RefersTo

Thanks for your assistance
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34979303
fyed: You are welcome but you had to do the changes that I mentioned above?

Sid
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 34979304
Here is the sample spreadsheet I used to test.  Works just as I intended.  
DataValidation.xlsm
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 34979324
Sid,

I wanted it to be more generic, so I passed it a SourceSheet instead of hard coding the source spreadsheet.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34979345
Ok :)

Sid
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now