Range Object Misunderstanding

[Ken McLennan]

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

 

[Bob Phillips]

Ken,

Struggling to run it.

Do you declare all of your variable up-front? Where is gStrArray and rngSrt
declared? What and how is range Groups defined?


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken McLennan]

G'day there Bob,

Do you declare all of your variable up-front? Where is gStrArray and rngSrt
declared? What and how is range Groups defined?

Sorry about that. I should have posted more information. That's what
happens when you try to rush things.

Last one first, I have a worksheet titled "Data". This worksheet is a
Worksheet Object called "dSht" (The sheet named "Calendar' is an object
called "cSht"; "Overtime" is "oSht"; etc). On "dSht" I have a named range
"Groups", defined by "=OFFSET(Data!$J$1,0,0,COUNTA(Data!$J:$J),1)" in my
Names dialogue.

gStrArray is declared in a general module called "MainModule":

Public gStrArray() As String

rngSrt is a function in the same module:

Public Function rngSrt(List() As String, UpDown As Boolean)

' Generic array sorting routine
' "List" is string array for sorting
' "UpDown" is direction - True = ascending
' "BubbleSort" coding courtesy of John Walkenbach

Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim gStr1 As String
First = LBound(List)
Last = UBound(List)

For i = First To Last - 1
For j = i + 1 To Last
If UpDown = True Then
If UCase(List(i)) > UCase(List(j)) Then
gStr1 = List(j)
List(j) = List(i)
List(i) = gStr1
End If
Else
If UCase(List(i)) < UCase(List(j)) Then
gStr1 = List(j)
List(j) = List(i)
List(i) = gStr1
End If
End If
Next j
Next i

' Return sorted array to calling routine
rngSrt = List

End Function


The idea is that the ListBox on my userform allows me to add or remove
entries from the named range "Groups". The entries are loaded into my
string array "gStrArray()", then the listbox displayed from those entries.
Whatever is in gStrArray() is put back into "Groups" when the userform is
terminated. Well, that's the theory anyway.

Hmmm... I've just realised that I've moved away from what I intended. I
originally used this sort of array manipulation so that I'd have a copy of
the starting entries from "Groups" that could be put back into the range if
the user decided not to make any changes and cancelled the form. However,
in its current state the code simply saves whatever is there in the array.
Not what I'd try to accomplish. I'll have to look at that.

However, I still can't figure out why the code works when run from the
VBE, but generates the error when called from my workbook. Even when
selecting "Macros" from the "Tools" menu it still generates the same error.

Any ideas will be gratefully explored.

Thanks for taking the time to look at it.
Ken McLennan
Qld, Australia.

 

[Blue Aardvark]

I had a similar problem to yours. It was because my cells were incorrectly
referenced. Try changing

dSht.range(cell(a,b),cell(c,d))
to
range(dSht.cell(a,b),dSht.cell(c,d))

 

[Dave Peterson]

It could be this:

Worksheets("Data").Range(Cells...

If Data isn't the activesheet, then this will fail. You'll want to qualify the
cells() portion, too.

Worksheets("Data").Range(Worksheets("Data").Cells(....

Or

with Worksheets("Data")
set yourrng = .Range(.Cells(...), .cells(...))
end with

The leading dots means that that object belongs to the object in the previous
With statement--in this instance, it means it belongs to worksheet("data").

===
One reason that this kind of stuff works from the VBE is that the "correct"
worksheet happens to be active when you run it.

===
Are you using xl97?

Are you running this code from a commandbutton from the control toolbox toolbar
placed on a worksheet? (Or any control from that control toolbox toolbar?)

If yes, try changing the .takefocusonclick property for that button to False.

If the control doesn't have that property, try adding:
activecell.activate

to the top of the code.

(This was a bug that was fixed in xl2k.)

 

[Tom Ogilvy]

unqualified range/cell references refer to the sheet containing the code
when located in Sheet 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))

 

[Ken McLennan]

G'day there One & All,

Dave & Tom, thank you so much (and you again too, Bob).

Now that I know where the problem is I can get it all up & running,
hopefully. I had previously tried referencing the range with dSht.Range...
but I'd not referenced the Cells as dSht.Cells... hence, it still didn't
work.

I think that had I read John Walkenbach's book once more I might have
found the problem, but I can't be sure of that =).

Thanks once more, the assistance you guys offer is extraordinary.

See ya
Ken McLennan
Qld, Australia