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,089 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 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