PDA

View Full Version : VBA to trigger input box when criteria in data found



Kubas
06-08-2016, 05:09 PM
Hello,

This is a bit complicated and I am trying my best to explain it.

I am trying to find a VBA code which triggers input box when specific criteria is found in data.

The data has a transaction codes and when criteria from columns A,B,C match it means it is 1 delivery.
Criteria for a delivery is (Part 1,Identify, PERM-CRE-LOC).



Column A
Column B
Column C


Part number
Activity code
Operation code


Part1
Piece pick
PERM-CRE-LOC


Part1
Identify
PERM-CRE-LOC


Part1
General movement
PERM-ADJ-LOC


Part1
Identify
PERM-CRE-LOC




By using this formula it returns a value of 2: COUNTIFS($A:$A,""Part 1"",$B:$B,""Identify"",$C:$C,""PERM-CRE-LOC"") . Which means I had two deliveries.
Is there a way a VBA can do this search and when it finds the matched criteria(delivery) then it triggers the input box?

I would like input box to give two options:
1st type in the quantity and 2nd to type in the quantity. Then the typed quantity placed in a different worksheet in specific range? i.e "Worksheet1" range A2 for 1st input and range B2 for 2nd input.
Can input box also display the number of total found criterias (Deliveries)?. Please help.

Many thanks

SamT
06-08-2016, 06:20 PM
You could use the Worksheet Change event to trigger a Sub that looks for a match. Then if that sub finds a match, it triggers a VBA UserForm that does all the rest.

The worksheet Change Event occurs every time changes to the sheet are made by a User, so there would only ever be one match. The Change Event also happens at certain other Changes.

Assigning a certain Cell that triggers a Sub when Double-clicked or adding a button to the sheet or adding a menu Item to the Workbook are other ways to trigger a sub.

Using a Cell formula won't work.


$A:$A,""Part 1"",$B:$B,""Identify"",$C:$C,""PERM-CRE-LOC"") . Which means I had two deliveries.
Is that the only combination of values possible that you need to have the Input UserForm run on?

Kubas
06-09-2016, 02:13 AM
Hello,

Thank you for a quick reply. There are going to be more part numbers to look at only.

SamT
06-09-2016, 05:02 AM
So any part number + "Identify" + "PERM-CRE-LOC"?

Kubas
06-09-2016, 07:34 AM
This is correct

SamT
06-09-2016, 11:18 AM
What about any part number + "Identify" + something besides "PERM-CRE-LOC"?