User form to Search for and Update Data

[dschenk832]

Hello All! Please forgive my complete lack of inexperience. I have never taken a class on writing excel macros and have so far been able to piece through what I have needed to do by intensive google searching, but I can not seem to get this code to work. When I hit play it doesn't actually give me an error message, but neither code is doing what I want. Any ideas? Also, I am not sure this method is the best for me. The search is by name, but it is possible for the name to be in the database multiple times (I work in public health and each record is an interaction therefore there could be multiple interactions), I was hoping to write code for a next button so the user can toggle through all of the different entries for that person and update as needed. Will that work?

Code for searching:

Private Sub Search_Click()


row_number = 0
Do
DoEvents
row_number = row_number + 1


item_in_review = Sheets("GoalTracking").Range("B" & row_number)
If item_in_review = TextBox1.Value Then
TextBox5.Text = Sheets("GoalTracking").Range("A" & row_number)
TextBox2.Text = Sheets("GoalTracking").Range("C" & row_number)
ComboBox1.Text = Sheets("GoalTracking").Range("D" & row_number)
TextBox3.Text = Sheets("GoalTracking").Range("E" & row_number)
TextBox4.Text = Sheets("GoalTracking").Range("F" & row_number)

End If

Loop Until item_in_review = ""


End Sub

Code for updating:
Private Sub UpdateData_Click()


row_number = 0
Do
DoEvents
row_number = row_number + 1


item_in_review = Sheets("GoalTracking").Range("B" & row_number)
If item_in_review = TextBox1.Text Then
Sheets("GoalTracking").Range("A" & row_number) = TextBox5.Text
Sheets("GoalTracking").Range("C" & row_number) = TextBox2.Text
Sheets("GoalTracking").Range("D" & row_number) = ComboBox1.Text
Sheets("GoalTracking").Range("E" & row_number) = TextBox3.Text
Sheets("GoalTracking").Range("F" & row_number) = TextBox4.Text

End If

Loop Until item_in_review = ""


End Sub


Any help will be GREATLY appreciated. my level of frustration is VERY high. Let me know if I can provide more information!

 

[dmt32]

Hi
Welcome to forum.
My wife was, until retirement, a nurse for 40 years so knowing what you guys put up with, I will have a go & see if I can assist with your project.

Code you have posted looks familiar & suspect it’s one I contributed some assistance in past for OP - whilst a loop code is ok for small data sets, using Range.Find method would be faster.

Following codes are largely updates from projects I have developed in past for others here + I have borrowed an approach from another (apologies but cannot find link to give credit) which provides a neat solution to finding next matched record using same code.

Hopefully, what I have done will do what you want but should be adjusted as required

On your userform you will need commandbuttons named

• Search
• UpdateData

Place ALL following code in your userforms code page

Dim wsGoalTracking As Worksheet
Dim FirstAddress As String, strSearch As String
Dim FoundRecord As Range
Dim RecordNo As Integer
Const SearchCol As Integer = 2


Private Sub Search_Click()
    Dim MatchCount As Integer
    
    With wsGoalTracking
'search range for strSearch match
        Set FoundRecord = .Columns(SearchCol).Find(strSearch, After:=FoundRecord, Lookat:=xlWhole, LookIn:=xlValues)
        If Not FoundRecord Is Nothing Then
'count number matches in range
        MatchCount = Application.CountIf(.Columns(SearchCol), strSearch)
        
            If FirstAddress <> FoundRecord.Address Then
                RecordNo = RecordNo + 1
'update caption
                Me.Caption = "Search Match " & RecordNo & " of " & MatchCount
'display found record
                GetRecord Me, FoundRecord
'if more than one match in range change Search Button caption
                If MatchCount > 1 Then Me.Search.Caption = "Find Next"
'enable update button
                Me.UpdateData.Enabled = True
'mark first matched record address
                If Len(FirstAddress) = 0 Then FirstAddress = FoundRecord.Address
                
            Else
'no more matches
                MsgBox strSearch & Chr(10) & Space(20) & Chr(10) & "End Of File", 48, "End Of File"
            End If
        Else
'no match found
            MsgBox strSearch & Chr(10) & " Record Not Found", 48, "Not Found"
        End If
    End With
End Sub


Private Sub TextBox1_Change()
    SearchReset Me.TextBox1.Text
End Sub


Private Sub UpdateData_Click()
'update record
    GetRecord Me, FoundRecord, xlAdd
'inform user
    MsgBox strSearch & Chr(10) & " Record Updated", 48, "Record Updated"
End Sub
Sub GetRecord(ByVal Form As Object, ByVal Target As Range, Optional ByVal Action As Integer)
    Dim FormControl As Variant
    Dim i As Integer
    With Form
    For Each FormControl In Array(.TextBox5, .TextBox2, .ComboBox1, .TextBox3, .TextBox4)
    i = i + 1
    If Action = xlAdd Then
'add record to worksheet
        Target.Offset(0, Choose(i, -1, 1, 2, 3, 4)).Value = FormControl.Value
    Else
'get record from worksheet
        FormControl.Value = Target.Offset(0, Choose(i, -1, 1, 2, 3, 4)).Value
    End If
    Next
    End With
End Sub


Sub SearchReset(Optional ByVal Text As String)
'reset commnandbutton controls
    With Me.Search
        .Caption = "Find"
        .Enabled = CBool(Len(Text) > 0)
    End With
    
    With Me.UpdateData
        .Caption = "Update"
        .Enabled = False
    End With
    
'reset variables
    strSearch = Text
    FirstAddress = ""
    RecordNo = 0
    Set FoundRecord = Nothing
    Set FoundRecord = wsGoalTracking.Cells(1, SearchCol)
'reset caption
    Me.Caption = "Search"
End Sub


Private Sub UserForm_Initialize()
    Set wsGoalTracking = ThisWorkbook.Worksheets("GoalTracking")
    SearchReset
End Sub

Enter search criteria in TextBox1 & press Find button.

The first match will display the record in your textboxes & combobox. Also, the userforms Caption will show how many matches exist in your data. E.g. Record 1 of 10
If there is more than one match, the Search button caption will change to “Find Next”
Pressing the button again will take you to next record & Caption will display the match record number.
When you get to the end of all matches, a msgbox will inform end of file.

Finally, I trust you are aware that excel is not a very secure platform for storing sensitive data as it security features are really only designed to stop general users accessing a workbook & changing things – more advanced users can access a locked workbook in very short time.

Hope Helpful

Dave

 

[dschenk832]

Thank you thank you! This is GREATLY appreciated. Extra thanks for explaining what each part of your code does!

It very well could have been a code you suggested to someone else I have used SAS before, but do not have any experience with VBA so I have been relying heavily on forums and how-to videos. We don't have money for SAS or for a CRM so I am just doing me best to get excel to fit our needs. Thanks again.

 

[dmt32]

Thank you thank you! This is GREATLY appreciated. Extra thanks for explaining what each part of your code does! .

You are most welcome & hope suggested code is of help to your project.
I do try and comment code I post but being full time granddad, sometimes play distractions don't allow me time to.

It very well could have been a code you suggested to someone else

not my code just code an OP posted for assistance - I do try and just modify actual code OP posted rather than re-write it in an attempt to help them understand what they were trying to do.

I have used SAS before, but do not have any experience with VBA so I have been relying heavily on forums and how-to videos. We don't have money for SAS or for a CRM so I am just doing me best to get excel to fit our needs. Thanks again.

Hopefully, you will find VBA straightforward - there are a number on sites you can visit that provide free guidance & sample workbooks but to repeat, beware of the security limitations of holding personal data in a workbook. Excel is not secure - Very easy for those who know how, to copy even when workbook is locked.


Dave

 

[Yrock]

Hi , sir your code helpful for me also in my VBA, but after get data on user form find next button available, how create find previous code ?