Hi everyone,

Look at the picture to know what I'm talking about.

In cells F13-16 people can select products from a dropdownlist. These
products are divided into two groups (BP or C).

The dropdown list is made of the values in the blue fields in column F.
In column C you see "BP" or "C". These are as I mentioned the groups a
product can be assigned to.

Now when they fill in F13-16 it will always be products from one group.
Now comes my question.

[image: http://i54.photobucket.com/albums/g1...P/Energol.jpg]

In the upper right corner are buttons with print macro's attached to
them. When the group of products they filled in are "BP" products they
should press the BP Receipt Note. What I would want is that when they
press the CASTROL RECEIPT, a pop-up message will appear saying "These
are products for a BP Receipt Note". Also the CASTROL RECEIPT macro
should not be activated.

Anyone that knows how to do this?

Thanks in advance


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=564230

Hi there,

I would suggest combining your codes and products, but separating them
with a pipe ' | ', for example:

BP | ENERGOL IC-HF (X) 403
C | CDX 30

You'll then be able to put this code into your buttons:

Private Sub cmdBPReceiptNote_Click()
'change the range("D1") to wherever you link cell is.
If Left(Range("D1").Value, InStr(1, Range("D1"), "|")) 0 Then
If Left(Range("D1").Value, InStr(1, Range("D1"), "|") - 2) < "BP"
Then MsgBox "These are products for a BP Receipt Note", vbInformation
Exit Sub
End If
End Sub


TomBP wrote:
Hi everyone,

Look at the picture to know what I'm talking about.

In cells F13-16 people can select products from a dropdownlist. These
products are divided into two groups (BP or C).

The dropdown list is made of the values in the blue fields in column F.
In column C you see "BP" or "C". These are as I mentioned the groups a
product can be assigned to.

Now when they fill in F13-16 it will always be products from one group.
Now comes my question.

[image: http://i54.photobucket.com/albums/g1...P/Energol.jpg]

In the upper right corner are buttons with print macro's attached to
them. When the group of products they filled in are "BP" products they
should press the BP Receipt Note. What I would want is that when they
press the CASTROL RECEIPT, a pop-up message will appear saying "These
are products for a BP Receipt Note". Also the CASTROL RECEIPT macro
should not be activated.

Anyone that knows how to do this?

Thanks in advance


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=564230

FunkySquid,

That is not an option cause when I print it would display BP | ..
productname. The BP | shouldn't be seen.


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=564230

No problems Tom,

I would suggest having a lookup table then which would need to be in
the following format:

Product Product Code
CDX 30 C
Energol ic-hf (x) 403 BP
....

This has to be sorted into acending order by Product.

You can then do a vlookup on your link cell (which returns the Product)
to return the Product code.

=VLOOKUP(C2,A:B, 2, FALSE)
where C2 is the Link Cell
where A:B is the table above


If you name this cell say 'ProductCodeReturned' (insert/name/define)
you can then insert the following code into your buttons:

Private Sub cmdBPReceiptNote_Click()
If Application.Names("ProductCodeReturned").RefersToR ange < "BP"
Then
MsgBox "These are products for a BP Receipt Note",
vbInformation
Exit Sub
End If
End Sub

Any probs, let me know.

Cheers

FunkySquid

TomBP wrote:
FunkySquid,

That is not an option cause when I print it would display BP | ..
productname. The BP | shouldn't be seen.


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=564230

FunkySquid,

My knowledge of Excel is pretty poor when it comes to formulas.

I've made a new .xls page so I can try to make a lookup table. I do
this because I ain't familiar with the words that you use. If you can
help me a little with this that would be a big help.

As you can see I made 2 columns and sorted them into ascending order. I
also copy pasted the vlookup code but it doesn't match the values in the
two columns. I tried to match the code to the values but no success.


[image: http://i54.photobucket.com/albums/g1...BP/lookup.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=564230

TomBP,

When a user chooses a product from your dropdown box you can assign a
'Linked Cell' to pick up what the user chose. To do this, set the
Linked Cell property on your dropdown box to say C2. If you need to
know how to do this, let me know.

In your example, the Vlookup will change to:
=VLOOKUP(C2, D:E, 2, False)

So the C2 relates to the cell you want to lookup (this is from the
Linked Cell
D:E relates to where you want to look for it
2 relates to the column number you want to return once you've found it
(1 would return Product, 2 will return Product Code)
FALSE ensures that there is an exact match.

Any problems,

Let me know.

FunkySquid

TomBP wrote:
FunkySquid,

My knowledge of Excel is pretty poor when it comes to formulas.

I've made a new .xls page so I can try to make a lookup table. I do
this because I ain't familiar with the words that you use. If you can
help me a little with this that would be a big help.

As you can see I made 2 columns and sorted them into ascending order. I
also copy pasted the vlookup code but it doesn't match the values in the
two columns. I tried to match the code to the values but no success.


[image: http://i54.photobucket.com/albums/g1...BP/lookup.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=564230