I have an Excel Range ?

hey gang- I can't seem to make these ranges do what I want them to! Would someone mind taking a look at this macro and slap me with the right answer?

Code:

---

Range("RegisterList").Select
    Selection.Resize(RegRowCount + 1, RegColCount).Select
    ActiveWorkbook.Names("RegisterList").Delete
    NewAddress = "=Register!" & Selection.AddressLocal
    ActiveWorkbook.Names.Add Name:="RegisterList", _
        RefersToLocal:=NewAddress

---

this defines the range name, and shows it in the Excel list, but for some reason, when I need to call the name from a different form,(i.e. to load a combobox on a different form) I get an error telling me the name is not valid.

help?

Hello,

I changed your code a little bit and it works fine. Open a new workbook and give this a tried.

Code:

---

Option Explicit

Sub Main()
  Range("RegisterList").Select
  Selection.Resize(3 + 1, 5).Select
  ActiveWorkbook.Names("RegisterList").Delete
 
  Dim NewAddress As String
  NewAddress = "=Register!" & Selection.AddressLocal
  ActiveWorkbook.Names.Add Name:="RegisterList", _
      RefersToLocal:=NewAddress
End Sub

---

:D

Here is a more simple version of this.

Code:

---

Option Explicit

Sub Main()
  Range("RegisterList").Resize(5, 5).Select
  ActiveWorkbook.Names("RegisterList").Delete
 
  ActiveWorkbook.Names.Add Name:="RegisterList", RefersToLocal:="=" & ActiveSheet.Name & "!" & Selection.AddressLocal
End Sub

---