G'day there One and All,

As you can see from the subject, I'm having a little difficulty with a
Range Object and can't find any reference to the cause of my error when I
check.

I have a Userform with a TextBox, a multiselect ListBox, and 3
CommandButtons - Cancel, Add, Remove. The latter work on the Listbox
entries. Cancel simply unloads the form.

At initialization the contents of a range on worksheet "dSht" are placed
in a string array. The listbox is loaded from that array. The buttons
either manipulate the list, or remove the form from the screen, and the
form's terminate routine places the string array back into the range.

My problem is that all works fine (an odd problem I hear you say!!). The
rest of the story is that it only works fine when I run the code from the
VBE. The range is cleared of its entries; the listbox is filled; the "Add"
& "Remove" buttons do their thing with the changes immediately reflected in
the listbox; and "Cancel" puts the array contents where they belong - in a
named dynamic range.

Running the code from a calling routine:

Public Sub shwGrpFrm()
frmGrpAdmin.Show
End Sub

which is on the front worksheet that I've imaginatively called "Main",
gives me a "1004" run time error. The "Method 'Range' of object
'_Worksheet' failed".

It's pretty obvious that I'm misunderstanding some subtlety of the Range
Object, but I can't figure out where to start looking. John WALKENBACH's
"Excel 2003 Power Programming with VBA" didn't show me anything obvious,
but that's likely to be a function of my thick head. I intend to read what
I can find in it again tonight.

I've tried referring to the worksheet by name -
Worksheets("Data").Range(Cells...

but that didn't work either.

Here's what I've got so far. Parts are commented for later reference by
those with no idea of Excel, not just for me. On completion I intend to
have comments as far as the eye can see, since there's a real good chance
that it won't be me maintaining it.


Thanks for looking at it.
Ken McLennan
Qld, Australia

Private Sub CommandButton2_Click()
' "Remove"
gNum1 = 0
For gNum = 0 To ListBox1.ListCount - 1
' Debug.Print gNum; " "; gStrArray(gNum + 1)
If ListBox1.Selected(gNum) Then
gStrArray(gNum + 1) = ""
gNum1 = gNum1 + 1
End If
Next
ListBox1.List = rngSrt(gStrArray, False)
ReDim Preserve gStrArray(UBound(gStrArray) - gNum1)
ListBox1.List = rngSrt(gStrArray, True)
End Sub

Private Sub CommandButton3_Click()
' "Add"
ReDim Preserve gStrArray(UBound(gStrArray) + 1)
gStrArray(UBound(gStrArray)) = Me.TextBox1.Text
ListBox1.List = rngSrt(gStrArray, True)
TextBox1.Text = ""
TextBox1.SetFocus
End Sub

Private Sub UserForm_Initialize()
' Set range "Groups" as object
Set gRng = Range("Groups")
' Get column number of range "Groups"
rngCol = gRng.Column
' Get number of cells in range "Groups"
rngCellCnt = gRng.Cells.Count
' Get address of 1st cell in range "Groups"
rng1stCell = gRng.Cells(1).Row
' Get values of each cell and save in general use string array
ReDim gStrArray(rngCellCnt)
For gNum = 1 To UBound(gStrArray)
gStrArray(gNum) = gRng.Cells(gNum).Value
Next
' Set userform listbox from array
ListBox1.List = gStrArray
gRng.ClearContents
End Sub


Private Sub UserForm_Terminate()

' Initialize variable to hold range object for this routine only
Dim rngTgt As Range
' Set range address to the size of "gStrArray"
' Start by setting number of rows/cells to the number of array elements
rngCellCnt = UBound(gStrArray)
' Then set the range to this size. "Groups" has only a single column
' the number of which is known from the form initialization
Set rngTgt = dSht.Range(Cells(rng1stCell, rngCol), Cells(rngCellCnt,
rngCol))
' The "Transpose" function must be used for a column alignment of a
' single dimensioned array
rngTgt.Value = Application.WorksheetFunction.Transpose(gStrArray)
End Sub