The intention of my macro is to put all the wb's sheet names into a
named range as per the code below. Then, use the named range as the
list in a "data validation" cell, which will use the named range as the
list.

I've successfully been able to convert the array into a range, although
i can only populate the range horizontally (columns x 1 row). if i try
to populate a vertical range, i get all the same value (the value of
index 1). I get similar results with the named range. And when I try to
use it in data validation, I get an error.

My workaround is to use the code to put the values into a horizontal
range, then use a named range to reference that range. Seems clunky and
unnecessary to include the intermediate step. Why not go from an array
to a "n x 1" named range (vs a "1 x n")?



Code:
--------------------
Sub updatesheets()

Dim m()
ReDim m(ActiveWorkbook.Sheets.Count)

For Each sht In ThisWorkbook.Sheets
m(n) = sht.Name
n = n + 1
Next sht

ActiveSheet.Range("a1").Resize(1, (n - 1)).Value = m

End Sub
--------------------


--
i-Zapp
------------------------------------------------------------------------
i-Zapp's Profile: http://www.excelforum.com/member.php...fo&userid=5768
View this thread: http://www.excelforum.com/showthread...hreadid=479125

Just make it a 'vertical' array to start with:

Sub updatesheets()
Dim Sht As Worksheet
Dim N As Integer
ReDim m(1 To ActiveWorkbook.Worksheets.Count, 1 To 1)

For Each Sht In ThisWorkbook.Worksheets
N = N + 1
m(N, 1) = Sht.Name
Next Sht
Range("a1").Resize(N).Value = m
End Sub


--
Jim
"i-Zapp" wrote in
message ...
|
| The intention of my macro is to put all the wb's sheet names into a
| named range as per the code below. Then, use the named range as the
| list in a "data validation" cell, which will use the named range as the
| list.
|
| I've successfully been able to convert the array into a range, although
| i can only populate the range horizontally (columns x 1 row). if i try
| to populate a vertical range, i get all the same value (the value of
| index 1). I get similar results with the named range. And when I try to
| use it in data validation, I get an error.
|
| My workaround is to use the code to put the values into a horizontal
| range, then use a named range to reference that range. Seems clunky and
| unnecessary to include the intermediate step. Why not go from an array
| to a "n x 1" named range (vs a "1 x n")?
|
|
|
| Code:
| --------------------
| Sub updatesheets()
|
| Dim m()
| ReDim m(ActiveWorkbook.Sheets.Count)
|
| For Each sht In ThisWorkbook.Sheets
| m(n) = sht.Name
| n = n + 1
| Next sht
|
| ActiveSheet.Range("a1").Resize(1, (n - 1)).Value = m
|
| End Sub
| --------------------
|
|
| --
| i-Zapp
| ------------------------------------------------------------------------
| i-Zapp's Profile:
http://www.excelforum.com/member.php...fo&userid=5768
| View this thread: http://www.excelforum.com/showthread...hreadid=479125
|

that's a step closer, but I still don't have the 'named range'
configured properly yet.

at the end of your code, I added the following to create the named
range:


Code:
--------------------
ActiveWorkbook.Names.Add Name:="mynamedrange", RefersTo:=myarray
--------------------
where 'myarray' is the array created by the macro.

if i then look at the newly created named range via Insert|Name|Define
in Excel, then I get as a formula what looks like the classical array
syntax, complete with brackets { }...


Code:
--------------------
={"Sheet1";"Sheet2";"Sheet3"}
--------------------
Problem is that it won't work as an argument for the data validation
list source. How do I un-array it?

at the end of your code, I added the following to create the named
range: ActiveWorkbook.Names.Add Name:="mynamedrange", RefersTo:=myarray


That really can't be true since I don't have a "myarray" in my code.

Once the sheet names are listed create a name for that range, not for the
array. At least if you're sticking with what you said in your first
message - "Then, use the named range as the
list in a "data validation"..."

--
Jim
"i-Zapp" wrote in
message ...
|
| that's a step closer, but I still don't have the 'named range'
| configured properly yet.
|
| at the end of your code, I added the following to create the named
| range:
|
|
| Code:
| --------------------
| ActiveWorkbook.Names.Add Name:="mynamedrange", RefersTo:=myarray
| --------------------
| where 'myarray' is the array created by the macro.
|
| if i then look at the newly created named range via Insert|Name|Define
| in Excel, then I get as a formula what looks like the classical array
| syntax, complete with brackets { }...
|
|
| Code:
| --------------------
| ={"Sheet1";"Sheet2";"Sheet3"}
| --------------------
| Problem is that it won't work as an argument for the data validation
| list source. How do I un-array it?
|
| .
|
|
| --
| i-Zapp
| ------------------------------------------------------------------------
| i-Zapp's Profile:
http://www.excelforum.com/member.php...fo&userid=5768
| View this thread: http://www.excelforum.com/showthread...hreadid=479125
|

I changed the array name for clarity's sake. Apparently, it had the
opposite effect.

I have not strayed from my original request, which is to seek a method
that takes the VB generated array and pumps it directly into a
named-range, but that is compatible with the source field of list-type
data validation, without needing to create an intermediate worksheet
range.

The issue, I believe, is that data validation's source field is looking
for a -cell reference-. And by putting a named-range into that field
that's actually just a list of string items, it returns an error.

Seems like the long way around the barn, but I was hoping that someone
was hip to some Excel trickery that would streamline the solution.


--
i-Zapp
------------------------------------------------------------------------
i-Zapp's Profile: http://www.excelforum.com/member.php...fo&userid=5768
View this thread: http://www.excelforum.com/showthread...hreadid=479125