Re: Range Object Misunderstanding

unqualified range/cell references refer to the *** containing the code
when located in *** modules. So you would need to qualify your references
such as

Set rngTgt = dSht.Range(Cells(rng1stCell, rngCol), _
Cells(rngCellCnt,rngCol))

change to

Set rngTgt = dSht.Range(dSht.Cells(rng1stCell, rngCol), _
dSht.Cells(rngCellCnt,rngCol))


--
Regards,
Tom Ogilvy


"Ken McLennan" <kenrmcl@xxxxxxxxxxx> wrote in message
news:h9i9unhbi0da$.ulq6u8t703mq$.dlg@xxxxxxxxxxxxx
> 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 work*** "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 work*** that I've imaginatively called "Main",
> gives me a "1004" run time error. The "Method 'Range' of object
> '_Work***' 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 work*** 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
>
>


.

Loading