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:
 
Sort by date Sort by votes
When i try run your code

it shows a error message Variable not define for the "Row...


Code:
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

Code:

Regards
Charles
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
ok.. this will need a listbox (but you can modify to combobox). the Find button should work as well...

Code: 
Option Explicit

Private Sub cmdAdd_Click()
    Dim 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
    
    '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
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then ctrl.Value = ""
    Next ctrl
    Me.TextBox1.SetFocus
End Sub

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

    'the selected row in worksheet is found by getting the listindex (which is base 0) and adding 1
    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

Private Sub cmdFind_Click()
    Dim row As Long, res As String
    res = InputBox("Find what?")
    For row = 0 To ListBox1.ListCount - 1
        If ListBox1.List(row, 0) = res Then   '  this entry is the search text
            ListBox1.ListIndex = row
            Exit For
        End If
    Next row
End Sub


Sub userform_initialize()
    Dim lrow As Long, 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

    'add the data in column A to the listbox 1 worksheet row at a time until the last row
    For row = 1 To lrow - 1
        ListBox1.AddItem ws.Cells(row, 1)
    Next row
End Sub
 
Upvote 0
you now have a sub which at atartup loads all the serial numbers onto a listbox. if you click in the listbox on a serial, it will load the data from the worksheet and whenever you click the save button it will save. problem now is to decide whether to edit or add a new row. you will need to check whether the listbox has been selected or not. if it has then you are editing, otherwise you are adding. otherwise use 2 different buttons <add new> and <save changes>
 
Upvote 0
now this version uses 1 save button for save and add, but changes the text on the button as required.
note that selRow is now Dim'ed outside a sub, making it shared for all subs on the userform

Code: 
Option Explicit
Dim selRow As Long

Private Sub cmdAdd_Click()
    Dim lrow As Long, ctrl As MSForms.Control
    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
    
    If selRow < 0 Then selRow = lrow
    'copy the data to the database
    With ws
        .Cells(selRow, 1).Value = Me.TextBox1.Value
        .Cells(selRow, 2).Value = Me.TextBox2.Value
        .Cells(selRow, 3).Value = Me.TextBox3.Value
        .Cells(selRow, 4).Value = Me.TextBox4.Value
        .Cells(selRow, 5).Value = Me.TextBox5.Value
        .Cells(selRow, 6).Value = Me.TextBox6.Value
        .Cells(selRow, 7).Value = Me.TextBox7.Value
        .Cells(selRow, 8).Value = Me.TextBox8.Value
        .Cells(selRow, 9).Value = Me.TextBox9.Value
        .Cells(selRow, 10).Value = Me.TextBox10.Value
        .Cells(selRow, 11).Value = Me.TextBox11.Value
    End With
    
    'clear the data
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is TextBox Then ctrl.Value = ""
    Next ctrl
    Me.TextBox1.SetFocus
End Sub

Private Sub ListBox1_Click()
    Dim lrow As Long, ctrl As MSForms.Control
    
    selRow = -1
    'the selected row in worksheet is found by getting the listindex (which is base 0) and adding 1
    If ListBox1.ListIndex <> -1 Then
        selRow = ListBox1.ListIndex + 1
        cmdAdd.Caption = "Save changes"
    Else
        cmdAdd.Caption = "Add new"
        Exit Sub
    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

Private Sub cmdFind_Click()
    Dim row As Long, res As String
    res = InputBox("Find what?")
    For row = 0 To ListBox1.ListCount - 1
        If ListBox1.List(row, 0) = res Then   '  this entry is the search text
            ListBox1.ListIndex = row
            Exit For
        End If
    Next row
End Sub


Sub userform_initialize()
    Dim lrow As Long, 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

    'add the data in column A to the listbox 1 worksheet row at a time until the last row
    For row = 1 To lrow - 1
        ListBox1.AddItem ws.Cells(row, 1)
    Next row
End Sub
 
Upvote 0
for the minute, get rid of option explicit. we can put it back once everything is tidied up.. i prolly forgot to Dim something...
 
Upvote 0
i am finishing up for the night as its quite late here. i will have a look again tomorrow and see how you are going.
Bye for now.
 
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
344
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,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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