W
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.
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.