Pop-up Message on error

[TomBP]

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/g115/TomBP/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

 

[funkysquid]

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]

FunkySquid,

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

 

[FunkySquid]

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").RefersToRange <> "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]

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/g115/TomBP/lookup.jpg]

 

[FunkySquid]

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]

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

 

[FunkySquid]

Good old F1 huh! )

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]

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.

 

[FunkySquid]

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]

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/g115/TomBP/lookylook.jpg]

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

 

[FunkySquid]

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

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/g115/TomBP/lookylook.jpg]

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

 

[TomBP]

FunkySquid

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

 

[FunkySquid]

What does the error say?

 

[TomBP]

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

 

[TomBP]

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.

 

[FunkySquid]

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").RefersToRange <> "BP"
Then
MsgBox "These are products for a BP Receipt Note",
vbInformation
Exit Sub
End If
End Sub

FunkySquid

 

[TomBP]

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/g115/TomBP/PrintingBP.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

 

[TomBP]

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

 

[FunkySquid]

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