Userform searching and update

  • Thread starter Thread starter peter
  • Start date Start date
P

peter

Hi, I am quite inexperienced in VBA but none the less I have managed to
create a userform to load data from the various text and list boxes
into the next blank line on a worksheet.
What I am trying to do now is to search for a particular reference
number which is on the worksheet (there will be several thousand
entries)and then automatically display a second userform that loads the
currently entered data for the row containing the reference number into
the userform, the user will then add more data into an number of extra
text boxes and possibly amend the existing data, all the data will then
be loaded onto the correct row in the worksheet.
Getting the data from the userform into the worksheet I think I can
do(apart from one problem)the trouble that I'm currently having is
searching for the reference number and populating the 2nd userform,
associated with that is how do I make sure that the data is being
entered onto the correct row, i.e the row containing the reference
number. I have purchased a number of books but none seem to have the
answers to my questions.

Any assistance would be very gratfully received
 
I made two userforms.

In a general module, I put this code:

Option Explicit
Public myRow As Long
Sub showUserForm1()
UserForm1.Show
End Sub

Behind the userform1, I put this code:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
With .Range("a:a")
Set FoundCell = .Cells.Find(what:=Me.TextBox1.Value, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
LookIn:=xlValues, _
searchdirection:=xlNext, _
MatchCase:=False, _
searchorder:=xlByRows)
End With

If FoundCell Is Nothing Then
MsgBox "Key wasn't found"
Exit Sub
End If

myRow = FoundCell.Row
Load UserForm2
UserForm2.TextBox1.Value = .Cells(myRow, "B").Value
'rest of stuff

Me.Hide

UserForm2.Show

Me.Show

End With

End Sub

Behind userform2, I put this code:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
With Worksheets("sheet1")
.Cells(myRow, "C").Value = Me.TextBox1.Value
'and more??
End With
Unload Me
End Sub
 
Thank you for the help, I'm looking forward to trying it out when I
have a bit of spare time
 
Back
Top