PDA

View Full Version : Solved: Problem with update sheet data via userform



tlchan
12-12-2008, 07:46 PM
Previously prolem on initialize form with data selected from listbox for update been solved. However when I try to update the relevant record via Frm_editdata it seem there is error or changed data was not updated into worksheet as expected.

I need further help on this. :bug:

Thank you


Workbook as attached.

JimmyTheHand
12-13-2008, 12:08 AM
Hi

Something like this may work.

Private Sub Cmdupdate_Click()

With Worksheets("ALLSSE").Range("C" & FrmMain.ListBox1.ListIndex + 3)
.Value = Me.Tbname.Value
.Offset(, 2).Value = Me.TbacctNo.Value
.Offset(, 3).Value = Me.TbcsdsNo.Value
'etc
End With
End Sub

Jimmy

tlchan
12-13-2008, 02:59 AM
Thanks Jimmy for your response and suggested solution. Tested with your code but the edited data does not updated to the respective record field but instead append as new record few rows away from current record.

What I'm expected is select the item from listbox for edit and update the change to the same record field.

I hope you may able to assist me.

Thanks

JimmyTheHand
12-13-2008, 04:02 AM
Tested with your code but the edited data does not updated to the respective record field but instead append as new record few rows away from current record.

What I'm expected is select the item from listbox for edit and update the change to the same record field.
Yes, I overlooked that the listbox contains a filtered list of records. Sorry.

Basically, you need something by which you can identify the selected record, wherever it may be in the sheet. I think Column A (record number) would be perfect for this purpose.

You should modify the searching/listbox-filling part of the code, so that Column A be included in the lisbox. (I.e., the 1st column in the listbox would be the record number, the 2nd the SEC No, 3rd column the Name1, and so on.)

When updating the record on sheet ALLSSE, use this code:

Private Sub Cmdupdate_Click()
Dim RecNo As Long, Rng As Range

'Get the record number from the listbox
RecNo = FrmMain.ListBox1.List(FrmMain.ListBox1.ListIndex, 1)

'Look up the record in the sheet
Set Rng = Sheets("ALLSSE").Range("A:A").Find(what:=RecNo, LookIn:=xlValues, lookat:=xlWhole)

'Update the record
With Rng
.Offset(, 1) = Me.TbsdmNo.Value
.Offset(, 2) = Me.Tbname.Value
'etc, etc. Add code for further columns.
End With
End Sub

Jimmy

tlchan
12-13-2008, 08:03 AM
I'm sorry the code still can't work. It returns with error code as runtime error '13':type mismatch and error line at- RecNo = FrmMain.ListBox1.List(FrmMain.ListBox1.ListIndex, 1).

JimmyTheHand
12-13-2008, 08:48 AM
Then I guess we'll have to convert the record number to long, or change the variable declaration to a more general type.

Let's see how the latter works:

Private Sub Cmdupdate_Click()
Dim RecNo As Variant, Rng As Range 'RecNo changed to Variant type

'Get the record number from the listbox
RecNo = FrmMain.ListBox1.List(FrmMain.ListBox1.ListIndex, 1)

'Look up the record in the sheet
Set Rng = Sheets("ALLSSE").Range("A:A").Find(what:=RecNo, LookIn:=xlValues, lookat:=xlWhole)

'Update the record
With Rng
.Offset(, 1) = Me.TbsdmNo.Value
.Offset(, 2) = Me.Tbname.Value
'etc, etc. Add code for further columns.
End With
End Sub
Jimmy

tlchan
12-13-2008, 06:16 PM
Hi Jimmy,
The code still found with error -runtime error 91-Object variable or block variable not set. what did it means ?

Thank you

JimmyTheHand
12-13-2008, 06:29 PM
Please post the workbook in its present state.

tlchan
12-13-2008, 08:30 PM
Here is the workbook

david000
12-13-2008, 09:27 PM
change to
RecNo = FrmMain.ListBox1.Value

tlchan
12-14-2008, 02:02 AM
Yes! Its works fine now.

Thanks David & Jimmy for your assistance.

By the way is there any way to delete the selected item from sheet ALSSE and llistbox1 instead of update. eg by click update button or delete button ?

Thank you

JimmyTheHand
12-14-2008, 02:05 AM
Yes, David is right.

I would've suggested changing the column index to 0
RecNo = FrmMain.ListBox1.List(FrmMain.ListBox1.ListIndex, 0) but it has the same results as his suggestion.


Another part that needs to be changed is

Private Sub UserForm_initialize()

With Frm_editdata
.Tbname.Value = FrmMain.ListBox1.List(ListIndex, 2)
.TbacctNo.Value = FrmMain.ListBox1.List(ListIndex, 4)
.TbcsdsNo.Value = FrmMain.ListBox1.List(ListIndex, 5)
.TbsdmNo.Value = FrmMain.ListBox1.List(ListIndex, 1)
'etc
End With
'etc.
End Sub
All four occurrences of ListIndex need a qualifier. E.g.
.Tbname.Value = FrmMain.ListBox1.List(FrmMain.ListBox1.ListIndex, 2)
Also, the search algorithm gives duplicates if the search crietria is found in more than one column of the same row. You may want to redesign it.

Jimmy

JimmyTheHand
12-14-2008, 03:56 AM
is there any way to delete the selected item from sheet ALSSE and llistbox1 instead of update. eg by click update button or delete button ?
Try this

Private Sub CommandButton1_Click()
Dim RecNo As Variant, Rng As Range

'Get the record number from the listbox
RecNo = FrmMain.ListBox1.List(FrmMain.ListBox1.ListIndex, 0)
RecNo = FrmMain.ListBox1.Value
'Delete record from the sheet
Set Rng = Sheets("ALLSSE").Range("A:A").Find(what:=RecNo, LookIn:=xlValues, lookat:=xlWhole)
Rng.EntireRow.Delete
'Delete record from the listbox
FrmMain.ListBox1.RemoveItem FrmMain.ListBox1.ListIndex
Unload Me
End Sub
Jimmy

tlchan
12-14-2008, 04:45 AM
It's great !

Thank you for your great rescue all the way.