Call Macro Based on a non empty Cell Value.

[Purdydog]

Hi,

I think this has been answered for a specific cell, but I need help on expanding this to run a macro from multiple cells.

I have a speadsheet which creates barcodes based on the value in the cell below it, by calling a Macro called "Barcode"

If the cell is empty, then I do't want the Macro to run and the cell containign the MaMcro command should be left blank.

I know that this can't be done from an if statement and needs to be coded into a Macro somehow.

Once working on one cell, this need to be copied to a number of cells throughout teh spreadsheet to create a 7col by 25 row grid.

Can anyone help??

Thanks

[JasperD]

Please upload sample sheet

[AndyLitch]

Put this code in your worksheet module and then ask me your next question...

Please Login or Register  to view this content.

[MrShorty]

by calling a Macro called "Barcode"

How is this macro called? button? macro menu? event code?

Once working on one cell, this need to be copied to a number of cells throughout teh spreadsheet to create a 7col by 25 row grid.

Without an example of exactly what you want, it is hard to say what the best approach is. Based on this, it seems to me that, instead of a macro, a UDF (function procedure that you can call from a spreadsheet cell) might be the easiest way to acheive what you want. If the UDF is structured correctly, you could easily call it from within an IF function, contrary to what you have said in your post. =IF(isblank(A2),"",BARCODEUDF(A2))

Please Login or Register  to view this content.

[Purdydog]

Hi all,

Thanks for the replys, I appreciate my first post may have been a bit vague

Attached is workbook I've been working on. This includes the Barcode Macro actually called Barcode_Creator.



It is used to create multiple barcodes from the data input in the yellow cells at the top of the sheets.

As it's setup, the Macro is run from each cell, looking at the data in the cell below it to create the given barcode.

An if statement sorts out whether to populate the data in the Red cells or not and adds a sequential number (I guess this could also be automated as well).

But even if empty the Macro runs in the cell above as in Column N titled Bank 7.

If I can stop the Macro running when the red cell is empty, then I can make the spreadsheet more flexible

I hope this is a bit clearer.

Thanks again..

[AndyLitch]

I've changed the formula in row 8
You'll need to paste it through all the other barcode calculation rows 11,14 etc


Barcode.xlsm

[JasperD]

I hate to break this to you, but it's a bit of a mess and for some reason I feel this could be done in a much easier fashion....
Are you using a barcode font (doesn't look like it on my machine, but I'm probably missing the font).

Is it safe to say that you're basically doing this :
In merged cells B4, F4, J4 and N4 you might input data, which is then copied to the red cells below with a suffix added in the form of -01, -02, -03 etc until -49, -50, which you then just want converted into the barcodes of that number?
If so, it seems like we should just redesign your sheet to make it easier & quicker, without the need for fancy macros like there is now.

Either that, or I'm way off in my assumptions and I should just shut up :-)

[Purdydog]

Hi Jasper,

Thanks for the reply.

In essence you are correct, but as always there's a bit more to it.

Yes you will be missing the font to display and print the barcode properly, there is a specific one needed to go along with the macro.
The barcode is coded to EAN 128, you can download a font for Excel, but the coding has to be done correctly to ensure it scans properly - that's what the macro does somehow - I'm no barcode expert either.

I agree the sheet is not very pretty as it is designed to print the barcodes in blocks of 1-25 25-50 etc.

It's formatted so that is will print out on Avery Labels No 6008 via a laser printer - so I'm pretty much stuck with the layout, but might be able to pretty it up a bit now it works.

Regards...P

[AndyLitch]

I think what Jasper is saying is that it always pays to separate your real data and calculations (in a standard table form) and your print data which can be extracted from your main table ......... But that's for another day

[Purdydog]

Hi Andy,

Looks like you're the man, your code works a treat !!

I was led to believe that you couldn't run a Macro from within an excel function

Thanks again...

[Purdydog]

I think what Jasper is saying is that it always pays to separate your real data and calculations (in a standard table form) and your print data which can be extracted from your main table ......... But that's for another day

I guess you're right.

As other users might have to use this, my intention was to password protect all the cells bar the input ones, with the print area set for the data / labels to be printed.

Thanks again....P