Retrieve data from sheet to Form for Edit

E

ebincharles

New Member
Joined
Feb 22, 2010
Messages
10
Hey!
I use excel 2003 for data entry.All the data are stored in "Data" sheet. Like this Hundred of entry i need to enter. And I need to edit these entry by bring back to the form and store it again in same row of the sheet.

below is my code to save data:


Code:
Option Explicit

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet

Set ws = Worksheets("Data")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.TextBox1.Value
.Cells(lRow, 2).Value = Me.TextBox2.Value
.Cells(lRow, 3).Value = Me.TextBox3.Value
.Cells(lRow, 4).Value = Me.TextBox4.Value
.Cells(lRow, 5).Value = Me.TextBox5.Value
.Cells(lRow, 6).Value = Me.TextBox6.Value
.Cells(lRow, 7).Value = Me.TextBox7.Value
.Cells(lRow, 8).Value = Me.TextBox8.Value
.Cells(lRow, 9).Value = Me.TextBox9.Value
.Cells(lRow, 10).Value = Me.TextBox10.Value
.Cells(lRow, 11).Value = Me.TextBox11.Value
End With

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox1.SetFocus

End Sub
Code:

I tried many ways for my sheet. But i cannot. Can u help me?


Regards
Charles:mad::cool:
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
hi charles and welcome to the forum. can i ask.. where abouts have you placed this code? have you designed a userform yet? if so what have you got on it?
 
Upvote 0
Yes, Thank you so much to invite me to your forum. I am really happy.

I made this code to run data entry. everything goes fine. when i try to save the data, it goes to sheet 1 and save. Fine.

But i need to retrieve back to form for modify the entry and re-save.

Can u help me to place code to take back the record I entered with the code i posted.

Thank you in advance.

Regards
Charles
 
Upvote 0
the data is stored on a sheet, in rows from column A to K. is that right?

and now u need to make a userform where you can load in a row of data, edit it if you need, and then save it back onto the sheet. is that what you want to do?

do you already have a userform which u use to save the data in the first place?
 
Upvote 0
Yes what u said right that data range is A to K in a sheet.

Yes I have, At present i have an user form to enter the data, can I bring back the entry on same way i saved to form and modify to save again.

Thanks
Charles
 
Upvote 0
post all of the code in the userform and then we can work from there
 
Upvote 0
Below is my code. This code for just to save the data and to multiply
the data between column ( Hours worked x Rate) only.

No other code is there:

Code:
Option Explicit

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet

Set ws = Worksheets("Data")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.TextBox1.Value
.Cells(lRow, 2).Value = Me.TextBox2.Value
.Cells(lRow, 3).Value = Me.TextBox3.Value
.Cells(lRow, 4).Value = Me.TextBox4.Value
.Cells(lRow, 5).Value = Me.TextBox5.Value
.Cells(lRow, 6).Value = Me.TextBox6.Value
.Cells(lRow, 7).Value = Me.TextBox7.Value
.Cells(lRow, 8).Value = Me.TextBox8.Value
.Cells(lRow, 9).Value = Me.TextBox9.Value
.Cells(lRow, 10).Value = Me.TextBox10.Value
.Cells(lRow, 11).Value = Me.TextBox11.Value
End With

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox1.SetFocus

End Sub


Private Sub cmdfind_Click()
Unload Me
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
If .TextBox6 = "" Then
MsgBox "Hours worked is missing"
Exit Sub
Else: .TextBox8.Value = CLng(.TextBox6.Value) - CCur(.TextBox7.Value)

End If
End With
End Sub

Code:


Only the above code i have for data entry.

Thank u so much

Regards
Charles
 
Upvote 0
you need to decide how you want to select the record to edit... eg do you list them all in a controlbox or listbox or ??

but heres a bit to get you started

Code: 
Option Explicit

Private Sub cmdAdd_Click()
    Dim lrow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    lrow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    'check for a part number
    If Trim(Me.TextBox1.Value) = "" Then
        Me.TextBox1.SetFocus
        MsgBox "Please enter a part number"
        Exit Sub
    End If
    
    'copy the data to the database
    With ws
        .Cells(lrow, 1).Value = Me.TextBox1.Value
        .Cells(lrow, 2).Value = Me.TextBox2.Value
        .Cells(lrow, 3).Value = Me.TextBox3.Value
        .Cells(lrow, 4).Value = Me.TextBox4.Value
        .Cells(lrow, 5).Value = Me.TextBox5.Value
        .Cells(lrow, 6).Value = Me.TextBox6.Value
        .Cells(lrow, 7).Value = Me.TextBox7.Value
        .Cells(lrow, 8).Value = Me.TextBox8.Value
        .Cells(lrow, 9).Value = Me.TextBox9.Value
        .Cells(lrow, 10).Value = Me.TextBox10.Value
        .Cells(lrow, 11).Value = Me.TextBox11.Value
    End With
    
    'clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = ""
    Me.TextBox9.Value = ""
    Me.TextBox10.Value = ""
    Me.TextBox11.Value = ""
    Me.TextBox1.SetFocus
End Sub

Private Sub cmdButt******_Click()
    Dim selRow As Long, lrow As Long, ctrl As MSForms.Control
    Dim lPart As Long
    
    Dim ws As Worksheet
    
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    lrow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
        
    'clear the data
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then ctrl.Value = ""
    Next ctrl
    
    'get the data from database
    With ws
        TextBox1.Value = .Cells(lrow, 1).Value
        TextBox2.Value = .Cells(lrow, 2).Value
        TextBox3.Value = .Cells(lrow, 3).Value
        TextBox4.Value = .Cells(lrow, 4).Value
        TextBox5.Value = .Cells(lrow, 5).Value
        TextBox6.Value = .Cells(lrow, 6).Value
        TextBox7.Value = .Cells(lrow, 7).Value
        TextBox8.Value = .Cells(lrow, 8).Value
        TextBox9.Value = .Cells(lrow, 9).Value
        TextBox10.Value = .Cells(lrow, 10).Value
        TextBox11.Value = .Cells(lrow, 11).Value
    End With
    
End Sub
 
Upvote 0
if you put a listbox on your userform then this will put column A info in the listbox and when you click on it you will load the corresponding row into your textboxes.

Code: 
Option Explicit

Private Sub cmdAdd_Click()
    Dim lrow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    lrow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    
    'check for a part number
    If Trim(Me.TextBox1.Value) = "" Then
        Me.TextBox1.SetFocus
        MsgBox "Please enter a part number"
        Exit Sub
    End If
    
    'copy the data to the database
    With ws
        .Cells(lrow, 1).Value = Me.TextBox1.Value
        .Cells(lrow, 2).Value = Me.TextBox2.Value
        .Cells(lrow, 3).Value = Me.TextBox3.Value
        .Cells(lrow, 4).Value = Me.TextBox4.Value
        .Cells(lrow, 5).Value = Me.TextBox5.Value
        .Cells(lrow, 6).Value = Me.TextBox6.Value
        .Cells(lrow, 7).Value = Me.TextBox7.Value
        .Cells(lrow, 8).Value = Me.TextBox8.Value
        .Cells(lrow, 9).Value = Me.TextBox9.Value
        .Cells(lrow, 10).Value = Me.TextBox10.Value
        .Cells(lrow, 11).Value = Me.TextBox11.Value
    End With
    
    'clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox7.Value = ""
    Me.TextBox8.Value = ""
    Me.TextBox9.Value = ""
    Me.TextBox10.Value = ""
    Me.TextBox11.Value = ""
    Me.TextBox1.SetFocus
End Sub

Private Sub ListBox1_Click()
    Dim lrow As Long, ctrl As MSForms.Control

    If ListBox1.ListIndex <> -1 Then
        selRow = ListBox1.ListIndex + 1
    End If
        'clear the data
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then ctrl.Value = ""
    Next ctrl
    
    'get the data from database
    With ws
        TextBox1.Value = .Cells(selRow, 1).Value
        TextBox2.Value = .Cells(selRow, 2).Value
        TextBox3.Value = .Cells(selRow, 3).Value
        TextBox4.Value = .Cells(selRow, 4).Value
        TextBox5.Value = .Cells(selRow, 5).Value
        TextBox6.Value = .Cells(selRow, 6).Value
        TextBox7.Value = .Cells(selRow, 7).Value
        TextBox8.Value = .Cells(selRow, 8).Value
        TextBox9.Value = .Cells(selRow, 9).Value
        TextBox10.Value = .Cells(selRow, 10).Value
        TextBox11.Value = .Cells(selRow, 11).Value
    End With

End Sub

Sub userform_initialize()
    Dim lrow As Long
    
    Dim ws As Worksheet
    
    Set ws = Worksheets("Data")
    
    'find first empty row in database
    lrow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    For Row = 1 To lrow
        ListBox1.AddItem ws.Cells(Row, 1)
    Next Row
End Sub
 
Upvote 0
Thank you for your kind reply.

I made "TextBox1" as serial number for the data entry.
Now the base to save and retrieve the data is "TextBox1" (Serial Number).

So when I try enter the serial number of the particular entry, that data should display for modification. The data i enter through "TextBox1" stores in A:A column. So u can place a combobox to select which entry to modify or just enter the serial number of entry which i need to edit.

I hope u understand my difficult u will help me.

Regards
charles
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help Needed
2
Replies
13
Views
319
kwavic
K
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
343
Rhodie72
Rhodie72
I
  • Question Question
Delete customers row of values after my code has completed moving its copy
2
Replies
17
Views
544
ipbr21054
I
E
  • Question Question
If textbox is empty, do not copy the value to worksheet
Replies
1
Views
382
NoSparks
N
G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
183
genesix
G

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top