VBA - search function in different sheet

[lomer]

Hi guys,

I've been start recently to work with VBA and i was able to create a userform that will populate "Orders" based on what the users will write in the userform. Then i had to make a search function based on order ref number and account name that will populate the userform with the data from each column in the "orders" sheet that the users added because someone else needs to approve the order.
The problem is that i don't know how to create this part of the code.
Can someone please help me with this?
Please find attached the sample where you can see what i need.
Many thanks in advance.

 

[Luke M]

Hi lomer. I'm not sure what you want to do once you find the cell/record, but here's a way to get started/ Changing the code for the search button to this:

Private Sub CommandButton5_Click()
Dim lngNum As String
Dim strName As String
Dim fCell As Range
Dim rngSearch As Range
Dim firstAdd As String

'Which controls are we looking at?
lngNum = Me.TextBox12.Value
strName = Me.ComboBox12.Value

'Do some validation checks
If lngNum = 0 Then
    MsgBox "Please enter a value", vbExclamation
    TextBox12.SetFocus
    Exit Sub
ElseIf strName = "" Then
    MsgBox "Choose your account", vbExclamation
    ComboBox12.SetFocus
    Exit Sub
End If

'Go find our data
'''''''
'Where will we search?
Set rngSearch = ThisWorkbook.Worksheets("orders").Range("A:A")

With rngSearch
    Set fCell = rngSearch.Find(what:=strName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
   
    If fCell Is Nothing Then
        MsgBox "Could not find any records with that account name"
        Exit Sub
    Else
        firstAdd = fCell.Address
    End If
   
   'Keep searching cells till either we find a match, or search all the cells
    Do
        If fCell.Offset(0, 4).Value = lngNum Then
            GoTo foundValue
        Else
            'Keep searching
            Set fCell = .FindNext(fCell)
        End If
    Loop Until fCell.Address = firstAdd
End With

'If value not found...
MsgBox "No Order Ref Number found for that account"
Exit Sub

'---------------

'If value was found
foundValue:
MsgBox "Your record is in row: " & fCell.Row
'WHAT DO WE DO NOW THAT WE FOUND IT??


End Sub

 

[lomer]

Thank you very much Luck for your help. I already tested the code and is working. What i want after the record was found is to populate the textboxes in the userform. For exemple: if the value was found on row 2 then textbox..= with Cell A2.
Do you think that will be possible?
Many thanks for your help. God bless you!

 

[Luke M]

I'll get it started for you. I'm not sure which all fields get populated from where, but if you look at the code, you should start to see the pattern. I took the liberty of renaming your controls to make them a little easier to keep track of...referring to something just as TextBox2 is a little hard to know what it is.

 

[lomer]

Dear Luke,

You're a genius.
I have modify the code and now all my textboxes are filled with the columns that i want. Now the person that need to approve the order can search and will find all the data with the order ref number and the account name.
In my form i have 2 drop list : SDM and Commercial approval that will have 2 option yes and no.
When the user submit the order this droplist will be locked because the order need to be approve by SDM and Commercial. So the user enter all the data, the userform will populate "Orders" sheet, the SDM and commercial will receive an email with the Order Ref number, they will enter the Order ref number in the form to see all the details (I have all the code until here)and approve(I don't know how to write this part). Now the dropdown boxes will be activate but how can I include this option in the form?
Meaning that when the SDM or Commercial will select Yes, then i need that this "True" to be added in the SDM approval in Orders because the default value is False or NO.

In my opinion, this code need to be on the Summit approval click.

Hope that i'm not asking to much but this userform will be the start for the program to be implemented so is very important for me to be 100% functional.

Many many thanks for your help. God bless you!

 

[Luke M]

I'm afraid I got a little lost. Is the user going to use the UserForm to change the dropdown to yes, and then you want to write to the sheet

OR

Does data get changed in sheet, we want UserForm to grab the data, but we don't want user to be able to change it?

 

[lomer]

Ok. Let me explain better.
So my userform has 2 different thing.
1. Add part
This part will be used only by the normal users when they want to submit the new order by filling the textboxes with the right information. After they will use the Add button to paste the information in "Orders".

The comboboxes for SDM approval and Commercial + checkboxes will be locked for them. When the information is pasted to excel for these will appear False like in the sample that I've uploaded.

2. Search and approve part
This part will be used only by the approval employee. They need to approve the new submitted order based on Order Ref Number and Account through the Search option that you helped me with.

The search will autofill the textboxes if the O. Ref. Numb and Account was found.

To be easy to understand set:
.ComboBox11 = fCell.Offset(0, 31).Value
in this way you will see that the search will populate the combobox11 (SDM approval) with Yes or No = True or False.

I need to be able to update this combobox because by default are FALSE.
- new order - doesnt have approval - combobox 11 = FALSE in Orders

 

[Luke M]

Ok, I think I understand better. We can start the UserForm with those two dropdowns visible, but disabled, so user can't change them. Did this by selecting control and in Propertied, changing the 'Enabled' property to false. Then in the code for Searching, we'll have it enable those fields if it finds a match.

Does that help?

 

[lomer]

Dear Luke,

Isn't this what i want but is very good because on the search tab i will lock the textboxes and let only the comboboxes enable.

What i want is to update the information in the row where the value was found when SUBMIT APPROVAL button is clicked. I don't know how to explain better. Only the text from the tbSDMAppr and tbComApp i need to be updated/edited in the row where the value was found because the rest will be lock. No other information need to be updated/edited, only this 2.



Many many thanks for your time and help! Have a splendid day!

 

[lomer]

Dear Luke,

Can you help me write the code for the Summit approval button?

fcell(strName, 29).Value = Me.tbSDMAppr.Value
fcell(strName, 31).Value = Me.tbComApp.Value

I mean, i want to be able to change the information in this 2 columns when the Summit approval is clicked.

The update or the change need to be done on the same row where the Order Ref Number and Account Name was found during the search.

I really need you help. thank you very much!

 

[Luke M]

Hi lomer,

It would be nearly identical code, need to search through and find the record, then export the info. Perhaps something like this?

Private Sub cbSubmit_Click()
Dim lngNum As String
Dim strName As String
Dim fCell As Range
Dim rngSearch As Range
Dim firstAdd As String

'Which controls are we looking at?
lngNum = Me.tbRefNumber.Value
strName = Me.tbAccName.Value

'Do some validation checks
If lngNum = 0 Then
    MsgBox "Please enter a value", vbExclamation
    Me.tbRefNumber.SetFocus
    Exit Sub
ElseIf strName = "" Then
    MsgBox "Choose your account", vbExclamation
    Me.tbAccName.SetFocus
    Exit Sub
End If

'Go find our data
'''''''
'Where will we search?
Set rngSearch = ThisWorkbook.Worksheets("orders").Range("A:A")

With rngSearch
    Set fCell = rngSearch.Find(what:=strName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
   
    If fCell Is Nothing Then
        MsgBox "Could not find any records with that account name"
        Exit Sub
    Else
        firstAdd = fCell.Address
    End If
   
   'Keep searching cells till either we find a match, or search all the cells
   Do
        If fCell.Offset(0, 4).Value = lngNum Then
            GoTo foundValue
        Else
            'Keep searching
           Set fCell = .FindNext(fCell)
        End If
    Loop Until fCell.Address = firstAdd
End With

'If value not found...
MsgBox "No Order Ref Number found for that account"
Exit Sub

'---------------

'If value was found
foundValue:

'WHAT DO WE DO NOW THAT WE FOUND IT??
With Me
    fCell.Offset(0, , 29).Value = .tbSDMAppr.Value
    fCell.Offset(0, 31).Value = .tbComApp.Value
End With
End Sub

 

[lomer]

Luke,
This is the same code as the cbSearch has.

I hope that this prtscreen to help you understand what i want.

You helped me with 1. 2. 3. 4. 5. - the code is working very well. But for Submit button cannot be the same code like Search has, cause will perform a second search and i don't want this. I just want to update:



Thank you for your big help. GOD bless you.

 

[Luke M]

Hi lomer.

I don't think you fully read through the code. The initial search is the same, because you'll need to find the record again. At the very end though, note that it's changing some cell values FROM the approver boxes. In the Search button's code, we read from cells TO the form.

 

[lomer]

Yes is my fault and you're 100% right. Sorry for my mistake.

Thank you very much for you help and support.

I wish you a great weekend ahead!