ok in 2000 not in 97

[cg]

I have a user form with a list box. It has 3 columns and
has a named range as its rowsource. When the user double
clicks an item in the listbox, it deletes the row and
bring up a user form with the information populated. The
user can then save the info back to the sheet. It will be
place in the next empty row. This works fine in 2000 but
when I took it to work where I have 97 it does not.
The 'update' info is not refelected back in the listbox?
I also started getting a message that excel has generated
error and will close. (not sure if that is related) Belwo
is the code I have. If anyone has any ideas where I am
going wrong, I would be most greatful.

Private Sub lbPending_DblClick(ByVal Cancel As
MSForms.ReturnBoolean)

ufEntry.Hide

'when an item is double clicked in the 'pending
transactions' listbox
'bring up the create userform and populate with the
entries made so far

Dim Rowcnt As Long
Dim r As Long

Rowcnt = 0
RemoveDuplicates

For r = 0 To lbPending.ListCount - 1
If lbPending.Selected(r) Then

Rowcnt = r + 2 'listcount begins with 0 and row 1 is
headings so we add 2
'to match the row # with the listindex
#
Dim how As String
how = Sheet2.Range("a" & Rowcnt).Value
Select Case how
Case "ebuy"
ufCreate.obebuy.Value = True
Case "PS7381"
ufCreate.ob7381.Value = True
Case "NA"
ufCreate.obNA.Value = True
End Select

Sheet2.Columns("B:B").EntireColumn.Hidden = False
ufCreate.tbApprovalDate.Value = Sheet2.Range("b" &
Rowcnt).Text
Sheet1.Columns("B:B").EntireColumn.Hidden = True


ufCreate.tbOrderDate.Value = Sheet2.Range("c" &
Rowcnt).Text
ufCreate.tbDescription.Value = Sheet2.Range("d" &
Rowcnt).Text
ufCreate.cbCatagory.Value = Sheet2.Range("e" &
Rowcnt).Text
ufCreate.cbVendor.Value = Sheet2.Range("f" &
Rowcnt).Text
ufCreate.tbInvoiceNum.Value = Sheet2.Range("g" &
Rowcnt).Text
ufCreate.tbReceiveDate.Value = Sheet2.Range("h" &
Rowcnt).Text
ufCreate.tbPaidDate.Value = Sheet2.Range("i" &
Rowcnt).Text
ufCreate.cbMethod.Value = Sheet2.Range("j" &
Rowcnt).Text
ufCreate.tbCost.Value = Sheet2.Range("k" &
Rowcnt).Text

'Now remove the transaction form the pending list
Sheet2.Range("a" & Rowcnt).EntireRow.Delete

End If
Next r

ActiveWorkbook.Names.Add Name:="lbPendingList",
RefersToR1C1:= _
"=PendingLog!R2C3:R40C5"

ufCreate.Show

End Sub

 

[Tom Ogilvy]

I have found that deleting something in the rowsource in Excel 97 causes a
crash.

I would try

set rng = Range(lbPending.RowSource)
lbPending.RowSource = ""
Sheet2.Range("a" & Rowcnt).EntireRow.Delete
lbPending.RowSource = rng.resize( _
rng.rows.count-1).address(external:=True)

 

[cg]

Thanks Tom, that explains alot. I am finding that these
version issues come up alot. Can I have both 2000 and 97
on the same computer?

Thanks for all you great help!

 

[Tom Ogilvy]

yes. Install from oldest to newest. During the install, choose not to
replace the existing version - install to a different location.