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,083 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 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

19 Experts available now in Live!

Get 1:1 Help Now