Search Worksheet, Input Data to Form, Back to Worksheet

[sd2173]

I am trying to create a form which will "add" a record by searching a worksheet for and ID number. I need to be able to add cell information from the row which is found (identified by ID number).

The data I am seraching from is organized as follows:

I used : in the example to seperate because the post wasn't recognizing my spacing, each item is in a different column.

ID: Last: First: Grade: Mark:
123456: Doe: Jane: 10: X

In my form I would like to type an ID number and then in labels on the form pull ID, Last, First, Grade, Mark for display/editing if necessary. I would also like to be able to change this information if necessary and resubmit to the exiting worksheet/row.

The biggest problem I am having is searching/locating the existing record and knowing how to recall individual cell data to a label or text box, and then how to send information from these boxes back to specific rows.

I am assuming I need to "store" the row # I am pulling from in my search so I know where to put info back...?

Any help much appreciated, just getting into this Excel stuff, it's fun but more different than I thought it would be from VB6 which I used years ago.

Thanks all.

SD

 

[tlowry]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]-->
Create a form with:

TextBox1
TextBox2
TextBox3
TextBox4
TextBox5
CommandButton1
CommandButton1

Name the cell at the top of the data FirstID (this is the one with the label “ID”)

Copy the code below into the form code


Show the form





'-------------------------------------------
Option Explicit
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
Public irow
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
Private Sub commandbutton1_Click()
irow = findRow(Me.TextBox1.Text)
Select Case irow
Case -1
MsgBox "Couldn't find " & Me.TextBox1.Text
Case Else
fillTB
End Select
End Sub
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
Sub fillTB()
With Range("FirstID")
Me.TextBox2 = .Offset(irow, 1).Text
Me.TextBox3 = .Offset(irow, 2).Text
Me.TextBox4 = .Offset(irow, 3).Text
Me.TextBox5 = .Offset(irow, 4).Text
End With
End Sub
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
Function findRow(sfind) As Integer
Dim x As Object
Dim srange As String
Set x = Range("FirstID").EntireColumn
Set x = Range("FirstID").EntireColumn.Find(What:=sfind, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
Select Case IsNothing(x)
Case True
findRow = -1
Case Else
findRow = x.Row - 1
End Select
Set x = Nothing
End Function
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
Public Function IsNothing(pvarToTest As Variant) As Boolean
On Error Resume Next
IsNothing = (pvarToTest Is Nothing)
Err.Clear
On Error GoTo 0
End Function 'IsNothing
Private Sub CommandButton2_Click()
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
With Range("FirstID")
.Offset(irow, 1).Value = Me.TextBox2
.Offset(irow, 2).Value = Me.TextBox3
.Offset(irow, 3).Value = Me.TextBox4
.Offset(irow, 4).Value = Me.TextBox5
End With
End Sub
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------
'-------------------------------------------