Solved

How can I add in excel vba a range.name to formula1 of a validationlist if range inludes more lines

Posted on 2008-10-30
9
2,094 Views
Last Modified: 2013-11-10
Please I need help in excel vba with validationlist of dropdowns

I use the code below. I create a range, give the range a name and put it to a validation list of a cell.
This works fine as long as the range consists only of one line.

If I write in my code:
.Range(.Cells(1, 2), .Cells(1, 255)).Name = strBereichsName

the code works fine

If i write in my code a range with more than one lines per example
.Range(.Cells(1, 2), .Cells(2, 255)).Name = strBereichsName

I get an error when the debugger is on following point:
With AktiveZelle.Validation 'Selection.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & strBereichsName

I want to use a range for strBereichsName wich can have more than only one line.
Please help me to get over this (bug?). Is this a bug, or is this a limitation? How can I add a validation list to an active cell using a range name, when the range has more than one line?

Thanks a lot in advance for any help!
Dim myRange As Range
 
With shtSheet
        .Range(.Cells(1, 2), .Cells(1, 255)).Name = strBereichsName
           
End With
      
        ' Hide the new sheet.
'        shtSheet.Visible = xlSheetHidden
        
        'Hide the new range
        ActiveWorkbook.Names(strBereichsName).Visible = False
        ' End major changes to code *********************************
     
        On Error GoTo errdaten
        With AktiveZelle.Validation 'Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=" & strBereichsName
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        
        End With

Open in new window

0
Comment
Question by:rolandkg1001
[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
  • 6
  • 2
9 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 22844912
Sorry,  Was doing my real job:-).
I've created a summary for you based on the information you gave me, but the numbers from your sample don't seem to make any sense to me, so I'm hoping it just the test data....Hope they do for you.  Let me know.

DESIRED-FORMAT-1-.xls
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 22844924
Crap...posted to wrong thread!!!  Sorry.
 
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 22844950
I was working on yours, and I'm not able to recreate the error on my copy of excel.  Can you post the error message you are recieving?
 
Cal
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 16

Expert Comment

by:Calvin Brine
ID: 22844961
One thing I would to know, is what type of object is AktiveZelle?
Is it a worksheet or a range object?  If a worksheet, that's where your problem might be.  Validation needs to be on a range.
 
Cal
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 22849374
A data validation list has to be one row or one column. There is no way around that other than to build the list up as a text string, but then you are limited to 255 characters in total.
Regards,
Rory
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 22849419
Rory,
  Good catch, I missed that entirely!!!  I replaced his range with my own test range, which of course only had one column, so I didn't encounter it in my testing!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 22849546
Luckily, I'm on my eighth cup of coffee this morning, so I'm quite alert for once! :)
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 22849771
No excuses for me, I was hopped up on about 6 cups of coffee yesterday, so the caffiene levels were all topped up. Just started going off in the wrong direction, charging like a bull.  One of these days I will starting reading the entire post:-)
0
 

Author Closing Comment

by:rolandkg1001
ID: 31511838
Thanks, as far as I understand: no way because of limitation.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
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…

732 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