Userform to update

jaiD

Board Regular
Joined
Jun 13, 2004
Messages
92
OK i have my userform to input data on my work sheet, now i wish to enable the same form to have ability to update on previous information. i.e. i enter data in user form with account number, name etc, i want to be able to load the userform and type in a account number i already have inputted on the sheet and for it to pull all of the information into the userform and allow me to complete text boxes i missed last time and then press save to save it in the same place. here is the userform code i am using.

Private Sub UserForm_Click()
Dim rng As Range
Dim LastRow As Long

If datebox.Text = " " Then
MsgBox "You Must Enter A Date."
datebox.SetFocus
Exit Sub
End If

LastRow = Worksheets("Main Data").Range("a65536").End(xlUp).Row

Set rng = Worksheets("Main Data").Range("a" & (LastRow + 1))

With rng
.Offset(0, 0).Value = datebox.Text
.Offset(0, 1).Value = txtcode.Text
.Offset(0, 2).Value = txtacnumber.Text
.Offset(0, 3).Value = Txtpersonnumber.Text
.Offset(0, 5).Value = txtsuccess.Text
.Offset(0, 6).Value = txtcost.Value
.Offset(0, 7).Value = txtmade.Value
.Offset(0, 8).Value = txtgross.Value
.Offset(0, 9).Value = txtcompleted.Text
End With

Unload frmUserForm1

End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi jaiD,
Instead of a userform for this, you should check out the data form.
It can be used for entering data, showing data and editing data, without having to reinvent the wheel in a userform.

Simply put your catagories in (say) row1 and make them bold.
Then put the individule records in the rows below.
When you're done, while you have (at least) one cell in the range selected, from the Menu bar choose Data > Form...

(You can record yourself opening the dataform if you want a button for opening it.)

I recommend giving this a shot before writing all the code necessary to do the same thing in a userform.

Hope it helps,
Dan
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
IN the following example I've created two command buttons on the user form ...

cmdGetOldData : :oops:
This button is used to retrieve records that have already been enter into the db.

cmdSave: :oops:
This button is used to save data from the form to the DB.

Private Sub cmdGetOldData_Click()
RecordRow = GetOldRow(Trim(txtacnumber.Text))
If RecordRow > 65536 Then Exit Sub

With Worksheets("Main Data").Range("a" & RecordRow)
datebox.Text = .Offset(0, 0).Value
txtcode.Text = .Offset(0, 1).Value
txtacnumber.Text = .Offset(0, 2).Value
Txtpersonnumber.Text = .Offset(0, 3).Value
txtsuccess.Text = .Offset(0, 5).Value
txtcost.Value = .Offset(0, 6).Value
txtmade.Value = .Offset(0, 7).Value
txtgross.Value = .Offset(0, 8).Value
txtcompleted.Text = .Offset(0, 9).Value
End With

End Sub

Private Sub cmdSave_Click()
Dim rng As Range
Dim LastRow As Long

If datebox.Text = " " Then
MsgBox "You Must Enter A Date."
datebox.SetFocus
Exit Sub
End If

LastRow = GetRow(Trim(txtacnumber.Text))

If LastRow > 65536 Then Exit Sub


With Worksheets("Main Data").Range("a" & LastRow)
.Offset(0, 0).Value = datebox.Text
.Offset(0, 1).Value = txtcode.Text
.Offset(0, 2).Value = txtacnumber.Text
.Offset(0, 3).Value = Txtpersonnumber.Text
.Offset(0, 5).Value = txtsuccess.Text
.Offset(0, 6).Value = txtcost.Value
.Offset(0, 7).Value = txtmade.Value
.Offset(0, 8).Value = txtgross.Value
.Offset(0, 9).Value = txtcompleted.Text
End With

Me.Hide
Unload Me
End Sub

Public Function GetOldRow(AccNum) As Long
With Worksheets("Main Data")
Set c = .Columns("C:C").Find(AccNum, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
GetOldRow = c.Row
Else
' if acc num is NOT in data
pt = MsgBox("Record not found", vbInformation, "No Record")
GetOldRow = 70000
End If
End With
End Function



Public Function GetRow(AccNum) As Long
With Worksheets("Main Data")
Set c = .Columns("C:C").Find(AccNum, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
' if acc num is already in data
ln1 = "The account number " & Chr(34) & AccNum & Chr(34) & " is already in DB." & vbCrLf
ln2 = "Do you want to overwrite old data ?" & vbCrLf
ln3 = "YES : Overwrite" & vbCrLf
ln4 = "No : Add new line" & vbCrLf
ln5 = "Cancel : do nothing" & vbCrLf
msg = ln1 & ln2 & ln3 & ln4 & ln5
resp = MsgBox(msg, vbQuestion + vbYesNoCancel, "Duplicat Acc #")
Select Case (resp)
Case vbYes: GetRow = c.Row
Case vbNo: GetRow = .Range("a65536").End(xlUp).Row + 1
Case Else: GetRow = 70000
End Select

Else
' if acc num is NOT in data
GetRow = .Range("a65536").End(xlUp).Row + 1

End If
End With

End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

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
Top