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

 

[iAmPatch]

Yes I have a MultiPage named "ReleaseForm"

And run for me would mean, "Activate"?

 

[My Aswer Is This]

Like I mentioned several days ago.
A userform is like a worksheet.

You can put buttons on a sheet.
When you click on a button a script will run.

But a sheet does not run a script.
A button on the sheet normally runs the script.
Now there are sheet activation scripts where if the sheet is activated a certain script is run.
But I have never heard of a way where if a Userform or Multipage is activated a certain script should automatically run.

 

[My Aswer Is This]

Now I see in your last post:

And run for me would mean, "Activate"?​

I would have never thought run would mean activate.

 

[iAmPatch]

but how about if we click on a button, the scrip to be run is a search script?

the search script then searches 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 a ReleaseForm MultiPage / UserForm will be available to the user to fill out

But if "Milk" is not in both; then PurchaseOrders MultiPage / UserForm will be the one available to the user to fill out

 

[My Aswer Is This]

You never did say why you need four mutipages which I asked earlier.

So now it sounds like your trying to stop users from using certain multipages.

Well this gets very difficult when you want to restrict users from doing certain things.

So that would mean all other mutipages would have to be hidden.

Then when would they be visible again.

I see lots of people always wanting to hide sheets and lock cells and want to restrict copy paste and restrict all sorts of things from happening. I'm not very good at providing all those restrictions. And some times the list of do not allow this or that can go on and on. And there are always ways a very knowledgeable user can get around these restrictions.

That's why I asked what these other two multipages are for but you never answered that question.

 

[iAmPatch]

oh sorry I missed the question about the MultiPages

MultiPage1 is named as "Search" - this is where the user inputs the item that they are searching for in the invetory
MultiPage 2 is named as "PurchaseOrder" - this is where a user can order a particular item if it's not yet part of the invetory
MultiPage 3 is named as "ReleaseForm" - this is where the user inputs the item that they would like to pull out from the inventory

 

[My Aswer Is This]

Try this:

Private Sub CommandButton5_Click()
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 Set SearchRange = Sheets("Released Items").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

 

[iAmPatch]

oh, so instead of using an "OR" function in searching; we're actually using multiple IF criteria?

 

[My Aswer Is This]

If for some reason you want to hide a multipage tab use something like this:

MultiPage1.Pages("ReleasedItems").Visible = False