VBA for multi-criteria find using a UserForm?

[iAmPatch]

Hi

I've created an inventory tracker with the help of @My Awser Is This
The inventory tracker has 3 worksheets, Dashboard, Released Items and Purchase Orders
In the Dashboard worksheet, I already have 2 command buttons (Add Released Items and Add Purchase Order) which pops up UserForms respectively

I would like to ask for assistance in coding a third command button which when clicked on, would pop up another UserForm wherein the user would key in specific search criteria (Vendor and Item). Once done, the user would then click on the "Search" button and the code should search in Purchase Orders sheet. If the value of the searched item in Purchase Orders is "0" or there is no item match; user should be routed to the Purchase Order UserForm. But if there is an available item, the user should be routed to the Released Items UserForm.

I tried using the record-macro option and fooling around with the "Find" function of Excel; but Excel didn't record the "Find" steps I did ...

Is this possible to do?

Thanks

 

[My Aswer Is This]

Glad to see you know Vba to where you can modify script to your needs. In that I did not know all your needs my script was intended to show you how to do it but I knew you may have to modify it.

Multipages are another one of those things Vba provides and you learn more as you use then more and understand them more.

I'm glad you were able to sort it out. Some posters here just come back and say it did not work and have no ideal how to read the code and modify it. If you really want to use Vba a lot its best to try and understand the code best you can and that way you can do a lot of things on your own and maybe only need a little help. I'm impressed you did a lot of coding here. Looks like you know vba fairly well.

 

[iAmPatch]

Thank you for your kind words; but in all honesty I'm not quite familiar with VBA codes. I just Google-d most of those and tried to see how their samples worked and then tried to modify it in such a way that it would meet my needs/requirements. But of course, if they really don't work and I can't debug it; I already post here and try to ask for a solution :D

 

[iAmPatch]

Hi @My Aswer Is This

Regarding this line of code

Private Sub Command_Search_Click()

'code below is for Search function (Tab 1)

Dim SearchString As String
Dim SearchRange As Range
SearchString = TB_SearchValue.Value

Set SearchRange = Sheets("Purchase Orders").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)

If SearchRange Is Nothing Then
MyMessagethree = "I'm sorry, this item isn't available."
MsgBox MyMessagethree
MultiPage1.Value = MultiPage1.Pages("PurchaseOrder").Index
Exit Sub

End If
ans = SearchRange.Offset(, 5).Value
MyMessage = "Hey, there's still "
MyMessagetwo = " that you can giveaway!"
MsgBox MyMessage & ans & MyMessagetwo
MultiPage1.Value = MultiPage1.Pages("ReleaseForm").Index

End Sub

Is it possible to Search in two different Worksheets? Will the code look like this if in case it's possible?

Set SearchRange = Sheets("Purchase Orders").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole) OR Set SearchRange = Sheets("Released Items").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)

Thanks

 

[My Aswer Is This]

Your post number 20 where you said you had every thing working had a lot of code I do not understand. Such as where it appears as if your closing numerous Userforms. I thought you were now using Multipages and if so I do not understand why you need 4 userforms.
You have unload me 4 times. You do not unload multipages

So when you have code in your script that you found on other sites and it works for you then I say that's great. But then when you come back and say now I need the script to search another range so how do I do that I have a hard time dealing with this because now I have to figure out the code you got some place else. So I'm not really able to answer your last question about searching two sheets.

I always have a hard time helping people when they are using code they have found from other sites and then trying to pasting all this together.

 

[iAmPatch]

Your post number 20 where you said you had every thing working had a lot of code I do not understand. Such as where it appears as if your closing numerous Userforms. I thought you were now using Multipages and if so I do not understand why you need 4 userforms.
You have unload me 4 times. You do not unload multipages

I just copied the previous close function from the UserForm. If we are not to unload multipages; what would be the code to use for closing the multipage?

So when you have code in your script that you found on other sites and it works for you then I say that's great. But then when you come back and say now I need the script to search another range so how do I do that I have a hard time dealing with this because now I have to figure out the code you got some place else. So I'm not really able to answer your last question about searching two sheets.

I always have a hard time helping people when they are using code they have found from other sites and then trying to pasting all this together.

I'm sorry; I'll probably just create two search tabs; wherein each would search a particular worksheet?

 

[My Aswer Is This]

So you only have one Userform with 4 Multipages.

To close the userform you use something like this:
Unload Userform1.

We never unload the Multipages. when the Userform is closed those mutipages automatically are closed.
Just like we never say sheet1.hide

Have you created a userform with a multipage?

It's always best to learn one step at a time and see how things work before writing code to do all what you want.

 

[My Aswer Is This]

It may be easier if we could start back at the beginning and you tell me exactly what we are trying to do here.
I believe your trying to search for quanities of a certain item.

So I would think you would need a textbox to enter the name of the item. Like Milk.

The word milk would be in column B and the quanity would be in column D''

Now are you saying if we wanted to search for Apples the word Apples would be in another column like C and the quanity would be in column E?

Is this what your doing?

Well then how many different columns might we be searching.

I mean do we have 25 different type items with 25 different columns to look in.

And now I'm assuming if the quanity is less then 10 or some other number you then want the multipage for ordering more items to pop up if the quanity is above 10 then you want a message box to pop up telling you how many Apples you have.

Why do we have Apples in one column and Milk in a different column?

And if we have one multipage to enter search values in and another multipage to use for ordering more items. What are the other 2 multipages for?

And if Milk is in column B how does a user know what column Milk is in versus how does the user know what column Apples are in? Does the user just have to remember this in his head?

 

[My Aswer Is This]

Now that I read this again:
You said:

I'll probably just create two search tabs; wherein each would search a particular worksheet?​

So if we want to search for Apples we want to search sheet("Apples")
And if we want to search for Milk we want to search sheet ("Milk") is that what your wanting?

 

[iAmPatch]

I'm sorry, I guess to avoid confusion, we could focus on the "Search Code" you provided

Private Sub Command_Search_Click()

'code below is for Search function (Tab 1)

Dim SearchString As String
Dim SearchRange As Range
SearchString = TB_SearchValue.Value

Set SearchRange = Sheets("Purchase Orders").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)

If SearchRange Is Nothing Then
MyMessagethree = "I'm sorry, this item isn't available."
MsgBox MyMessagethree
MultiPage1.Value = MultiPage1.Pages("PurchaseOrder").Index
Exit Sub

End If
ans = SearchRange.Offset(, 5).Value
MyMessage = "Hey, there's still "
MyMessagetwo = " that you can giveaway!"
MsgBox MyMessage & ans & MyMessagetwo
MultiPage1.Value = MultiPage1.Pages("ReleaseForm").Index

End Sub

Everything was already ok at first, then the requirements of the team changed ... We still are to look for "Milk" in Column A of Purchase Orders. But, we are also the look for "Milk" in Column B of Inventory List. If "Milk" is in either Purchase Orders or Inventory List; then that's the only time the ReleaseForm MultiPage will be run. But if "Milk" is not in both; then PurchaseOrders MultiPage needs to run.

I know that this is too tedious to work on; and I'm thinking of splitting them into two instead.

 

[My Aswer Is This]

I don't know what this means.

that's the only time the ReleaseForm MultiPage will be run​

You have a multipage named "ReleaseForm" ?

And what does run mean.