here is the snippet of code I'm using
it fails on the red line
Please Login or Register to view this content.
as you can see, according to Excel, the named range exists in the proper sheet. However I'm not able to access (populate) the cells in that range
I've attached the workbook it it helps
Looks like num_design_sheets is undefined.
Entia non sunt multiplicanda sine necessitate
at the time the error is raised, num_design_sheets is defined as type Long, value=2 (it is under the tally_design_sheets breakout in the Locals window)
A defined range can't use a VBA variable as part of its definition. It has no visibility.
ok, that's what I'm seeing. If I hard-code the #2, instead of num_design_sheets, it works.
How do I workaround this limitation? It needs to be a variable so that the named range will be redefined each time based on the length num_design_sheets
Create a named constant on the same sheet.
BTW, if a named range is (correctly) defined, you'll see marching ants around it when you select its 'refers to' in the define name dialog.
Edit: You realize you only need a named range if formulas refer it it; it can just be a VBA range variable otherwise, right?
Also, you can index outside a range in VBA.
Last edited by shg; 12-01-2008 at 12:30 AM.
thanks - I will make a named constant in the worksheet
the reason I made it a named range was so I can use it as a Data Validation formula - which is my next question
how do I fix this formula so it refers to the named range that I just created?Please Login or Register to view this content.
Record a macro and then edit it.
I can't get the data validation to work in Excel, so I can't record a macro. It looks like it doesn't understand the named range reference. However
I do see the ants marching
BTW, if a named range is (correctly) defined, you'll see marching ants around it when you select its 'refers to' in the define name dialog.
If Sheet1 has a cell with data validation list myVal, then myVal need not refer to a range on Sheet1, but it must be either a Sheet1 or workbook name.
When you have problems doing things in VBA, try doing them manually first to make sure what you're trying to do actually works.
...double post
Last edited by twd000; 12-02-2008 at 12:26 PM. Reason: double post
having trouble getting data validation to work in Excel. I create a named range in one sheet using VBA code, then I want to define a data validation on another sheet that refers to that named range as the List in the Data Validation dialog box.
The problem is with the named range definition
when I manually define the range it works fine. It also does not specify which sheet contains this named range.
but when I use this VBA code to define the range it doesn't work
as you can see I fully qualified the named range by declaring that it appears in the sheet "hidden_named_ranges"Please Login or Register to view this content.
what is the significance of the second column in the dialog box "Define Name"? I thought it meant that the name appears in more than one worksheet, which is not true in my case.
In order to reference the data validation list to the named range "output_response_sheet_dv_list" which appears on another sheet "hidden_named_ranges", do I need a workbook-level or worksheet-level named range? How do I create that in VBA code?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks