VBA Userform Search Function

[aswestley]

Hi All,

I'm brand new to VBA and due to my new job, i'm required to create a spreadsheet with userforms so personnel can't tamper with the initial data once i've entered it.

I am looking to create a userform where people can search via asset number, the form then autofills with the rest of the data in set textboxes.

I can't find anywhere details on how to create this. I can send the file to anyone that wants to take a look.

i want txtID to be the search box looking at column B on my spreadsheet.

TIA

 

[gallen]

That's a little harsh to expect someone new to come up with that solution (unless you put in your CV that you had VBA skills )

While it's straight forward enough for anyone with vba knowledge it's not a quick thing to learn.

Apologies but I don't have time to write that as although it's relatively simple, it's not quick. If time is of the essence, Mr Excel has a (paid)consultancy service that I'm sure can help professionals achieve their solutions.

 

[aswestley]

That's a little harsh to expect someone new to come up with that solution (unless you put in your CV that you had VBA skills )

While it's straight forward enough for anyone with vba knowledge it's not a quick thing to learn.

Apologies but I don't have time to write that as although it's relatively simple, it's not quick. If time is of the essence, Mr Excel has a (paid)consultancy service that I'm sure can help professionals achieve their solutions.

gallen, thanks for the advise something i will seriously be looking into. It is a little harsh but the company i work for, we are incorporated into a Communications department and i have spent the last 5 years working on telecomms systems only to be put in here temporarily haha.

 

[dmt32]

Hi,
at a basic level perhaps something like following will help you

Private Sub FindRecord_Click()
    Dim Search As String
    Dim FoundCell As Range, SearchRange As Range
    
'search sheet change name as required
    Set SearchRange = Worksheets("Sheet1").Columns(2)
    
    Search = Me.TxtID.Text
    
    If Len(Search) = 0 Then Exit Sub
   
        Set FoundCell = SearchRange.Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole _
        , SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not FoundCell Is Nothing Then
        Me.TextBox1.Value = FoundCell.Value
        Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
        Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
    Else
    
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
End Sub

You will need to adjust code to meet specific project need but should get you started.

Dave

 

[aswestley]

Hi,
at a basic level perhaps something like following will help you

Private Sub FindRecord_Click()
    Dim Search As String
    Dim FoundCell As Range, SearchRange As Range
    
'search sheet change name as required
    Set SearchRange = Worksheets("Sheet1").Columns(2)
    
    Search = Me.TxtID.Text
    
    If Len(Search) = 0 Then Exit Sub
   
        Set FoundCell = SearchRange.Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole _
        , SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not FoundCell Is Nothing Then
        Me.TextBox1.Value = FoundCell.Value
        Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
        Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
    Else
    
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
End Sub

You will need to adjust code to meet specific project need but should get you started.

Dave

Dave, thats great thank you so much i'll give it a go.