alright now, something's rotten in denmark.

another userform, which is supposed to select that worksheet to add a
new building, is also not working correctly (when it was). it adds the
information to the cover worksheet, instead of where it is supposed to.
i only have one workbook open.................
isn't this sufficient for declaring a page?

(this is in the global declarations module)
Public Bldgs As Worksheet

& then in each individual module/userform sub where it's needed, i've
got
Set Bldgs = ActiveWorkbook.Worksheets(2)

and it's definitely the #2 worksheet....... i've looked @ the
properties in the VBA editor.....

is there something different i need to indicate a particular sheet?????
susan


Susan wrote:
still the same error with stuff commented out (reprinted below as
separate sub)...... (i like that msgbox showing the variable, tho -
easier than a "watch"!).

using File Name: EXCEL.EXE
Display Name: Microsoft Office 2000
Description: Microsoft Excel for Windows
File Version: 9.0.3822

on Microsoft(R) Windows (R) 2000 Operating System.

vba project tree =
sheet1 (sheet1)
sheet2 (Bldgs)
sheet3 (Order)
etc..........
range shows correctly on correct sheet & msgbox says correct range &
sheet.

i don't know why it won't work, but adding in the .activate in the
initialization sub does make it work.......... but it doesn't make it
work here.................
xxxxxxxxxxxxxxxxxxx
Public Sub sort()

Set Bldgs = ActiveWorkbook.Worksheets(2)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
MsgBox MyBldgLastRow

Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MsgBox MyFindRange.Address(external:=True)

MyFindRange.sort Key1:=Bldgs.Range("A4"), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
susan


Dave Peterson wrote:
I've never had to activate the sheet first.

If you get rid of some of the other stuff, what happens:

Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
'Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
msgbox myBldgLastRow
'Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

msgbox myfindrange.address(external:=true)

MyFindRange.Sort Key1:=bldgs.Range("A4"), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'For Each cell In MyBldgs
' cboBldgList.AddItem cell.Value
'Next cell

End Sub

Do you still have a problem?

And are you sure you checked the correct worksheet (worksheets(2))?



Susan wrote:

:) thank you for trying!
it looks like i have to use
Bldgs.Activate
first, in order to be able to sort a range on that sheet.
sheesh!
susan

Dave Peterson wrote:
Sorry, I don't have another guess.

Susan wrote:

dave -
no, that didn't work - even tho it looked identical to mine, i copied &
pasted your code & commented-out mine just to be sure........
also still did not work with the with/end with syntax.

i added in
MyFindRange.Select
before to make sure it was "looking" @ the right stuff, and it is (when
stepping thru).

the worksheet has no merged cells, is not protected, is not hidden; i
can sort it manually, recorded sort looks the same as written code, and
yes there is data in the range & in the sort key.

i am getting
Run-time error 1004
Sort method of range class failed.

when i comment out the sort, the userform loads fine (just the combo
box data is not sorted).

:P aaaaarrrgggggg!
susan

Dave Peterson wrote:
This didn't work?

MyFindRange.Sort Key1:=bldgs.Range("A4"), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I like this syntax

with myfindrange
.Sort Key1:=.columns(1), Order1:=xlAscending, _
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

======
Can you sort that range manually? Do you have data in that range? Do you have
any merged cells in that range causing trouble? Is the worksheet protected?



Susan wrote:

i've tried all of the following, with the same error:

Sort Key1:=MyFindRange("A4")
Sort Key1:=MyFindRange("A4:A4") (these 2 give me type mismatch errors,
after which i tried declaring MyFindRange as Range instead of Variant,
but that didn't work either.)

Sort Key1:=MyFindRange.Range("A4")
Sort Key1:=Bldgs.Range("A4")
Sort Key1:=("A4")
Sort Key1:=Range("A4")
Sort Key1:=Range("A4:A4")
Sort Key1:=Range("A:A")
Sort Key1:=Bldgs.Range("A:A")
Sort Key1:=Bldgs.Range("A4:A4")

<sigh any other ideas?
thanks
susan

Dave Peterson wrote:
I'd try:

Key1:=bldgs.Range("A4")


Susan wrote:

i have the worst time trying to code a sort with the sort key......
usually i can make it work (eventually), but i'd like to understand it
better.
so can somebody explain why this is not working?!
xxxxxxxxxxxxxxx
Sub userform_initialize()

Set Bldgs = ActiveWorkbook.Worksheets(2)
Set Temp = ActiveWorkbook.Worksheets(5)

MyBldgLastRow = Bldgs.Cells(20000, 1).End(xlUp).Row
Set MyBldgs = Temp.Range("a4:a" & MyBldgLastRow)
Set MyFindRange = Bldgs.Range("a4:e" & MyBldgLastRow)

MyFindRange.Sort Key1:=Range("A4:A4"), Order1:=xlAscending,
Header:=xlNone, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each cell In MyBldgs
cboBldgList.AddItem cell.Value
Next cell

End Sub
xxxxxxxxxxxxxxxxxxx
it worked fine until i added the sort. i've tried making the key range
a4:a5
a4 (only)
a:a

i get the error message "sort method of range class failed"

maybe i should take out the "range" & just make it "a4"???
thanks for the assistance
(fyi everything's declared in a global mod).
susan

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson