Changing a named ranged based on array size on a form

[Wanderer]

I've created a form with a list box that is populated from a named
ranged on a spreadsheet. I am allowing the users to add/delete/reorder
the values in the listbox.

My problem is when I write the listbox values back to the named range.
If my listbox has fewer items than the original range then the old
values that were not overwritten are still in the range. The ideal
solution would be to clear the range then re-define the range limits
before I write back the values. I can't seem to find a way to change
named range's cell range definition via VB.

Here's what I've attempted

Form load - I load the named range to the list box on the form. Assume
7 items were loaded

Private sub UserForm_Initialize()
For each e in Range("Setup!Current_State_Column_Names")
if e.value <> "" then
setup_form.cs_columns_list.AddItem e.value
end if
end for
End sub

After the user completes their edits (all working) I attempt to write
back/redefine the named range

Dim CellsDown as Integer
Dim i as long, j as integer, x as integer, itemnum as integer
Dim TempArray() as string
Dim Current_State_Column_Names as Range

If cs_columns_list.listIndex = cs_columns_list.ListCount -1 then exit
sub

CellsDown = cs_columns_list.listcount ' 5 items are in the list box
for example

set Current_State_Column_Names = Range(Cells(1,1), Cells(CellsDown, 1))

' Fill array with listbox items

x = 0
for i = 1 to CellsDown
for j = 1 to 1
TempArray(i,j) = cs_columns_list(x)
x = x + 1
next j
next i

' Now attempt to write the array back to the named ranged

Current_state_column_names.Value = TempArray

' The works except the two extra values in the original named range are
still there. Obviously I could write a loop to clear the values from
the names range before I write the new values but it seems like it
would be easier to be able to redefine the named range in the
spreadsheet based on the number of items in my new list. I currently
have the range extended a lot longer than I need simply to accomodate
the fact I can't seem to redefine the range.

 

[Guest]

Try changing the RefersTo property of your named range. I played around with
it a little, and maybe this will give you some ideas. Using a range object
and a name object, you'll note they refer to the same range at the beginning
of the macro, but different ranges at the end of the macro by changing what
the named range refers to.

Sub test()
Dim rngTest As Range
Dim nmeTest As Name

With ThisWorkbook
Set nmeTest = .Names("MyRange")
With nmeTest
Set rngTest = .RefersToRange
End With
End With

MsgBox rngTest.Address
MsgBox nmeTest.RefersTo

nmeTest.RefersTo = Sheet1.Range("A1:A5")
MsgBox rngTest.Address
MsgBox nmeTest.RefersTo

End Sub

 

[Wanderer]

That worked like a charm...thank you very much!

Try changing the RefersTo property of your named range. I played around with
it a little, and maybe this will give you some ideas. Using a range object
and a name object, you'll note they refer to the same range at the beginning
of the macro, but different ranges at the end of the macro by changing what
the named range refers to.

Sub test()
Dim rngTest As Range
Dim nmeTest As Name

With ThisWorkbook
Set nmeTest = .Names("MyRange")
With nmeTest
Set rngTest = .RefersToRange
End With
End With

MsgBox rngTest.Address
MsgBox nmeTest.RefersTo

nmeTest.RefersTo = Sheet1.Range("A1:A5")
MsgBox rngTest.Address
MsgBox nmeTest.RefersTo

End Sub