Edit Userform

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
At the moment I have a userform which when submitted posts the data to the next empty row in excel. However I have made it so that the user can view other records on the userform. However I don't know how to enable the user to then amend that record. At the moment it just posts all the information into the next cell.

The code to view an existing record is

Code:
Private Sub UserForm_Initialize()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
 
cboUniqueID.List = Worksheets("Sheet1").Range("A3:A" & lngLastRow).Value
End Sub
 
Private Sub cboAccidentID_Change()
 
If cboUniqueID.ListIndex <> -1 Then
txtFirstName.Value = Range("A" & cboUniqueID.ListIndex + 3).Offset(, 1) 'etc
End Sub
 
ActiveWorkbook.Sheets("Sheet1").Activate
 
Range("A3").Select
 
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
 
End If
 
Loop Until IsEmpty(ActiveCell) = True
 
ActiveCell.Value = cboUniqueID.Value 'etc
End Sub

So, I guess I need to put a clause in which specifies that if the uniqueID value exists in Range("A3:A" & lngLastRow) Then Activecell = where this is true.

but I can't figure out how to say this. Hope that makes sense to somebody. Thanks in advance.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You know where the record came from because you have:

Code:
txtFirstName.Value = Range("A" & cboUniqueID.ListIndex + 3).Offset(, 1)

Therefore you know where to put any amendments.
 

Noz2k

Well-known Member
Joined
Mar 15, 2011
Messages
688
I know, but as I already have a lot of code specifying where to post new information

ActiveCell.Offset(, 1) = txtFirstName.Value etc (for around 50 controls), I was hoping for a way to just change the value for the ActiveCell, rather than pasting in all of the listindex + 3 type data, as I didn't want to make the code so long, and couldn't figure out how to do this.

Have got it going now though, I just had my End If in the wrong place

Code:
If cboUniqueID.ListIndex <> -1 Then
Range("A" & cboUniqueID.ListIndex + 3).Select
 
Else
    Range("A3").Select
 
Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
 
 
Loop Until IsEmpty(ActiveCell) = True
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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