Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-02-23
15
Medium Priority
?
537 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Author Comment

by:Dale Fye
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
Industry Leaders: 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!

 
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 2000 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 48

Author Comment

by:Dale Fye
ID: 34969797
Sid,

Won't be able to test until this evening (currently 08:52 AM)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34969836
Sure fyed :)

Sid
0
 
LVL 48

Author Comment

by:Dale Fye
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 48

Author Closing Comment

by:Dale Fye
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 48

Author Comment

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

Author Comment

by:Dale Fye
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

618 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