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

FunkySquid Wrote:
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.


I tried to use the magical F1 to search how to do this but no luck :)


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

Good old F1 huh! :o)

Ok, on your spreadsheet, go to View / Toolbars / Control Toolbox. Click
the Design Mode button then click on your dropdown box. Right click on
the dropdown box and goto Properties. There should be a LinkedCell
option. Set this to C2. Close the properties box and press the Exit
design mode (same button as before).

This should do it. Any probs, let me know.

TomBP wrote:
FunkySquid Wrote:
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.


I tried to use the magical F1 to search how to do this but no luck :)


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

I've added a dropdown list by using Data - Validation and selected the
list setting. I selected the 6 products in the products column.

Then I went to the control box as you said and clicked on the design
button. When I select the dropdown list in Cell C2 and right click it
doesn't display an option to select properties.


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

Sorry TomBP

I assumed you were using a Combo Box rather than a validation list box.
Not a problem.

Goto Insert / Name / Define and type in 'ProductChosen' then at the
bottom where it says RefersTo, select your dropdown list box cell and
then click Add.

Now add another Name called 'ProductRangeLookup' which refers to your
lookup table you created then Close.

Change your Vlookup to:
=VLOOKUP(ProductChosen, ProductRangeLookup, 2, FALSE)

Give that a go...


TomBP wrote:
I've added a dropdown list by using Data - Validation and selected the
list setting. I selected the 6 products in the products column.

Then I went to the control box as you said and clicked on the design
button. When I select the dropdown list in Cell C2 and right click it
doesn't display an option to select properties.


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

FunkySquid Wrote:
Sorry TomBP

I assumed you were using a Combo Box rather than a validation list
box.
Not a problem.

Goto Insert / Name / Define and type in 'ProductChosen' then at the
bottom where it says RefersTo, select your dropdown list box cell and
then click Add.

Now add another Name called 'ProductRangeLookup' which refers to your
lookup table you created then Close.

Change your Vlookup to:
=VLOOKUP(ProductChosen, ProductRangeLookup, 2, FALSE)

Give that a go...


As you can see I followed your directions or at least I think I did ;)


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

When I add the VLOOKUP formula it displays an error saying that there
is something wrong with the formula.


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

So close, you need to change the 'ProductRangeLookup' to refer to
D10:E15


TomBP wrote:
FunkySquid Wrote:
Sorry TomBP

I assumed you were using a Combo Box rather than a validation list
box.
Not a problem.

Goto Insert / Name / Define and type in 'ProductChosen' then at the
bottom where it says RefersTo, select your dropdown list box cell and
then click Add.

Now add another Name called 'ProductRangeLookup' which refers to your
lookup table you created then Close.

Change your Vlookup to:
=VLOOKUP(ProductChosen, ProductRangeLookup, 2, FALSE)

Give that a go...


As you can see I followed your directions or at least I think I did ;)


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

When I add the VLOOKUP formula it displays an error saying that there
is something wrong with the formula.


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

FunkySquid

I changed it to D10:E15 now but the VLOOKUP formula still displays an
error.


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

What does the error say?

TomBP wrote:
FunkySquid

I changed it to D10:E15 now but the VLOOKUP formula still displays an
error.


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

Pure madness.

I tried to implement the vlookup thingy in my original .xls file but i
doesn't work there. Instead of displaying BP or C it says #N/A.

Very strange since I followed the same steps

--
TomB
-----------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...fo&userid=3611
View this thread: http://www.excelforum.com/showthread.php?threadid=56423

Never mind FunkySquid. I just didn't select anything out of the box.
That's why it showed #N/A.

Changed my post to Double post cause it's a silly mistake. It's been a
long day ;)

Anyways, can you guide me thru the next step. I've got it working in my
original .xls file now.


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

Ok, good stuff. Now the cell where the Vlookup is, you need to name
that too. So Insert / Name / Define again and this time call it
'ProductCodeReturned'. You'll then be able to put this code into your
BP button:

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

FunkySquid

TomBP wrote:
Never mind FunkySquid. I just didn't select anything out of the box.
That's why it showed #N/A.

Changed my post to Double post cause it's a silly mistake. It's been a
long day ;)

Anyways, can you guide me thru the next step. I've got it working in my
original .xls file now.


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

FunkySquid,

Sorry to bother you one more time ;)

The code you posted isn't suited for me. Let me explain into detai
what I would want to do.

You see I selected ENERGOL CL-DX 405 and that the vlookup formul
displays it as a BP product. Now when it is a BP product and I press o
the CASTROL RECEIPT button it should display an error.

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

Here you see the code that is behind the CASTROL RECEIPT button. It i
a print macro.


Code
-------------------
Sub Print_CASTROL_RECEIPT()
'
' Print_CASTROL_RECEIPT Macro
' Macro recorded 13/07/2006 by BPPassPort User
'
' Keyboard Shortcut: Ctrl+l
'
Range("I6:I7").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.ActivePrinter = "printer MRN castrol on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=4, ActivePrinter:= _
"printer MRN castrol on Ne00:", Collate:=True
ActiveWindow.Close
End Su
-------------------


Here is your code again:


Code
-------------------
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 Su
-------------------


As I can see you are referring to BPReceiptNote but I want it to refe
to the CASTROL RECEIPT button.

I hope you understand what I mean. If not, ask away

--
TomB
-----------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...fo&userid=3611
View this thread: http://www.excelforum.com/showthread.php?threadid=56423

Going to give this thread a bump seeing as the problem isn't solved yet
and it's in the final stage of completion.


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

Sorry, wasn't about yesterday.

If it's not too late, you just need to change the code to this:

Sub Print_CASTROL_RECEIPT()
'
' Print_CASTROL_RECEIPT Macro
' Macro recorded 13/07/2006 by BPPassPort User
'
' Keyboard Shortcut: Ctrl+l
'
If Application.Names("ProductCodeReturned").RefersToR ange < "C"
Then
MsgBox "These are products for a Castrol Receipt Note",
vbInformation
Exit Sub
else
Range("I6:I7").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Application.ActivePrinter = "printer MRN castrol on Ne00:"
ActiveWindow.SelectedSheets.PrintOut Copies:=4, ActivePrinter:= _
"printer MRN castrol on Ne00:", Collate:=True
ActiveWindow.Close
End If

End Sub


TomBP wrote:
Going to give this thread a bump seeing as the problem isn't solved yet
and it's in the final stage of completion.


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

FunkySquid,

I changed the original code behind CASTROL RECEIPT button with your
code. When I press it it gives the following error:

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


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

The 'Then' needs to be on the same line like this:

If Application.Names("ProductCodeReturned").RefersToR ange < "C"
Then

FunkySquid

TomBP wrote:
FunkySquid,

I changed the original code behind CASTROL RECEIPT button with your
code. When I press it it gives the following error:

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


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

That did post correctly. Anyway, just make sure it's on the same line.


FunkySquid wrote:
The 'Then' needs to be on the same line like this:

If Application.Names("ProductCodeReturned").RefersToR ange < "C"
Then

FunkySquid

TomBP wrote:
FunkySquid,

I changed the original code behind CASTROL RECEIPT button with your
code. When I press it it gives the following error:

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


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