| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ok in 2000 not in 97
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ok in 2000 not in 97
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) -- Regards Tom Ogilvy cg wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ok in 2000 not in 97
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! -----Original Message----- 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) -- Regards Tom Ogilvy cg wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ok in 2000 not in 97
yes. Install from oldest to newest. During the install, choose not to
replace the existing version - install to a different location. -- Regards, Tmo Ogilvy cg wrote in message ... 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! -----Original Message----- 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) -- Regards Tom Ogilvy cg wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Open 2000 file attachments in Excel 2000 | Excel Discussion (Misc queries) | |||
| I cannot edit cell format in Excel 2000 (Part of office 2000)! | Excel Discussion (Misc queries) | |||
| Using Excel 2000 as Data source for Word 2000 document | Excel Discussion (Misc queries) | |||
| Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) | |||
| Excel Programs developed in Office 2000 on Windows 2000 | Excel Discussion (Misc queries) | |||
Hybrid Mode
