Excel Userform

[gramsisfunnybone]

I have created a userform in excel. I have entered data to the userform and added that to data to a worksheet. Is there a way to use the userform to update or change data that has already been entered to the worksheet. Example: John Doe is the clients name. I have an address field already entered. Can I go back to the userform and bring up John Doe in the userform to change his address.

 

[Marcelo Branco]

Hi,

Welcome to the forum.

Take a look at
http://msdn.microsoft.com/en-us/library/aa192538(v=office.11).aspx

HTH

M.

 

[gramsisfunnybone]

Thanks. The userform I developed enters data to the worksheet. Will this userform allow me to do both? i.e. add rows of data as well as update data or do I have to use two userforms?

 

[Marcelo Branco]

If you follow the example in the link i've showed you can do that, ie, insert, update or delete records

M.

 

[BrianExcel]

I would recommend setting ONE of your fields as the source value for your data. For example, if you were entering a 4 digit location number (1234) into a field named locationnumber then you would assign it as a string, something like

Dim sLocationNumber as String

sLocationNumber = userformnamehere.locationnumber.value

And then you would change your data based on the offset value of that source value.

So if you enter "5678" as the location number, then when you press enter or a button it searches for the text 5678 (where you tell it to search), finds the offset value you want to change, and changes.

Something like this...

Set rAll = .Range(.Cells(3, 1), .Cells(3, 1).End(xlDown))
        For Each r In rAll
            If InStr(r, sTerm) Then
                With Sheets("Complete List")
                    r.Offset(0, 1).Value = frmCenterLookup.cmbProject.Value
End With
End If
etc...

 

[gramsisfunnybone]

Thanks Brian. I am not a technical person and have created this userform with help from the internet. I'm not sure where I should put the code you have given. Am pasting the code from my userform with hopes that you can point me to exactly what I need to do. I created a client id which I think would be equal to your locationnumber.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Clients")

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

'check for a client id
If Trim(Me.txtclientname.Value) = "" Then
Me.txtclientname.SetFocus
MsgBox "Please enter a Client"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.ComboBox3.Value
ws.Cells(iRow, 2).Value = Me.ComboBox4.Value
ws.Cells(iRow, 3).Value = Me.ComboBox1.Value
ws.Cells(iRow, 4).Value = Me.ComboBox6.Value
ws.Cells(iRow, 5).Value = Me.ComboBox7.Value
ws.Cells(iRow, 6).Value = Me.ComboBox2.Value
ws.Cells(iRow, 7).Value = Me.ComboBox5.Value
ws.Cells(iRow, 8).Value = Me.txtreason.Value
ws.Cells(iRow, 9).Value = Me.txtclientname.Value
ws.Cells(iRow, 10).Value = Me.txtadd1.Value
ws.Cells(iRow, 11).Value = Me.txtadd2.Value
ws.Cells(iRow, 15).Value = Me.txtphone.Value
ws.Cells(iRow, 16).Value = Me.txtnotes.Value
ws.Cells(iRow, 17).Value = Me.txtclientid.Value

'clear the data
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox6.Value = ""
Me.ComboBox7.Value = ""
Me.ComboBox2.Value = ""
Me.ComboBox5.Value = ""
Me.txtreason.Value = ""
Me.txtclientname.Value = ""
Me.txtadd1.Value = ""
Me.txtadd1.Value = ""
Me.txtphone.Value = ""
Me.txtnotes.Value = ""
Me.txtclientid.Value = ""
Me.txtclientname.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

Thanks.