Fact today I want to show you how to generate a QR code from data you have in Excel not only that I want to you know give you the ability to actually set its own back color attic to set its own front color customize the size customize the size on the table and also create an entire list of QR codes based on some data that you have not only that I want to do that all in about 30 minutes so let's see if we can do that that's a tall order I gotta get started right away on yeah I just want to make sure that you've already subscribed subscribing liking commenting that's gonna help.
Us to make sure we create these videos each and every week for you I want to bring these to you and I want to make sure you get alerted right when they I create them each and every Tuesday so make sure you click that notifications icon valve these trainings are always free go ahead and download this application it's free in the description below if you want to support us I've got some amazing courses including the mentorship program in that metric program I'm gonna teach you how to define design develop and deploy your own excel applications basically taking your Excel skills and bringing them to the forefront allowing you to make a passive income that you.
Can use for the rest of your life by developing your own applications I've done that in the past I wanted to show you how to do that that's what we're doing in the mentorship program while I develop an incredible accounting application so if you like that check out my excel mentor.com all the details there I'll also put the links down below alright let's get started so basically what I want to wanna be able to click this button and generate tons of QR codes I want to also create a button here and put a QR code for this particular project product and what I want to do is I want to customize that I want to give the user or you the developer or your end user the ability to change.
The size and the change the color like to have a back color or a front core and I want to do that using a pop-up color picker I want to be able to set the size on the form and the size of that QR code in the table so that's what we want to do to choose the codes we're gonna be using a code picker I believe I've got one saved here called the color palette here that's gonna help us choose the color so you'll be able to select a color and have that color appear and customize it it's a lot to show you also this item information if this looks familiar this application was created in a prior training called mobile inventory manager we're not gonna create this form today we're gonna use its functionality but we won't be creating it if you want to know how I created it all you got to do is find the mobile inventory manager on my youtube channel and I'll go through it in detail in that training video but we're just gonna use it as a backdrop today because I want.
To develop a code let's close that out okay so we've got this so we've got a setup we've got a palette of colors we want to be able to select both the back color and the front color and in the item I want to create an area that we can put that QR code so let's do that right now I'm just gonna put a border around it we're gonna do very very basic here because I really want to show you what's behind this particular API and how we're gonna create this amazing barcode and I'm just gonna put a button here and then we can actually duplicate this button using control D and I'm gonna delete the macro that's associated with it an ungroup it so when we assign the Mac or just click backspace and that's gonna clear all the macros out that has been assigned not only to the.
Sufficient okay so that's nice and now we'll just sew just a small button this can help us generate the QR code and okay that should be sufficient now I'm going to call the control and group those and just create a button called generate QR I already have one under the other one so that's fine for now so basically I want to put that QR right here alright so that's all we're gonna do it's pretty simple to end it what do I want to do I wasn't generated based on some day tonight you can use any data you want okay I'm just gonna use the item name it's the simplest so I want to generate data.
A QR code based on this item name and in the item list I'm gonna do the same thing I've got a button here already set up so I want to generate based on this name I don't want to run through for any QR code that doesn't isn't here or doesn't exist I wanted to generate you can generate it based on pretty much anything a link a description there's about 900 character limits or so you want to keep it within that limit but anything less this should be just fine and then the setup screen when user selects the back color of accurate color I want this thing to pop up otherwise I want it to hide so let's just write some code for that and this is called color palettes just to shape and it's.
Made up of individual rectangles of color and all I'm gonna do is take the background color whatever each of these shapes are I'm going to bring it in to this I'm gonna deter put the color here and I'm gonna determine what number doesn't put the number here so how do we do that well that's in the developers if you don't have the developers tab open of course you can find it located here in the options and then you just click on the customize urban and then the developers right here make sure that is selected there's also a shortcut to get you into the vigil basic that's alt f11 okay so what I've got here I've got item information we've got item macros these are all.
The macros that are associated with the item those are already here that was written a part of a prior training so like I said if you want to check out that prior training it's the mobile inventory manager there's a lot of part QR codes that's what we're gonna focus on today we're basically gonna write three different macros it's gonna be really simple one macro is gonna run our colors one macro is going to generate arc to generate this single QR code and the other macro is gonna run is gonna generate QR codes for all of these okay so what is behind this macro what is the engine that's gonna run this and that's a really great API from a website that I'll use and it's called QR code.
Generator QR code generator and it's a great API it's relatively simple and that's why I love it so much this this one uses a website now there are ways to generate code within the application but it's a lot of coding obviously not something I'm what I'm gonna do is I'm gonna find that and I'm gonna include it inside this workbook so that means you're gonna have more than one way of generating QR codes you can do it inside Excel but it's very detailed very complicated macro so it's not something we want to go over in the training but I'll include it it does exist but there's it's less limited this one is great because you can customize it however the only drawback it takes.
A little bit of time to generate because it's actually making a call to the Internet and then it's coming back so it does depend on your speed but what you can do is once you generate you can
Save the picture and then you don't need to use it you don't need to generate it only one time so that you know it's not something you do very often only maybe only when you create a new item or you update the item name ok so what's behind this basically what we're gonna be doing is we're gonna be making call to this web this particular API and that's what it looks like a QR code and so what we're going to be doing is we're going to be using this link right here and then we're gonna put the question mark and then I'm gonna put the size and our sizes variable then I want to put the data the data would be just whatever text you know that we're going in it the data of course in our case is going to be this item name right here that's our data we're gonna add a little bit more.Onto that we can customize even more that would be the gist for a very basic QR but of course this is excel for freelance as we go beyond that so what I want to do i want to add colors i want to add sizes i want to give lots of details so how can we do that we can even do colors so we can add colors with this this is kind of a basic screen here there's more documentation called here api command if we take a look at that we'll get into a lot of parameters if you really want to get into this you can there's a nice little explains a lot QuickStart guide what parameters like what kind of limits the the minimum character count is 1 the maximum is route about 900 characters or so it's not specific to that some best practices certain sizes obviously minimum would be 10 by.
10 maximum would be 10000 I think I don't care all right so it's a lot let's see what is that one that's actually 1 million maximum is 1 million by 1 million depends on that but we're gonna keep it way lower than that we're gonna just create basic here's some good examples of that and so basically you can read this sheet I don't want to read it all to you but you get the idea there's tons I'll include this link somewhere in the description down below or in the application itself download the application it's free so just make sure you use the links in the description below whether you're Facebook or email ok great so that's what we're getting it from that's how we're.
Gonna generate it so what I want to do first is I really want to give the user the ability to set those colors up right I want to I want to be able to customize the colors because the API allows us to change the background color it allows us to change the key our color how can we do that well let's do it let's give them the choice to show this so I'm going to write a macro for that and it's we're gonna call this simply set color so sub set color and I'm gonna sign this entire macro to every single shape in that that means I'm gonna it's all grouped together as you see it's all grouping there's individual shapes inside this each shape so basically when I assign a single macro to this group that macro then gets assigned to every single shape inside that group and that's.
Really what I wanted so first of all what I want to do is I want to know with the active cell value we can use active cell or of course we can use sheet 3 but I'll use active cell because that way it's easier for you to you don't have to rewrite the code too much active in case you put on a different sheet active cell dot value it's equal to this is gonna be really easy active sheet dot shapes right what we want is I want to know what is calling it what is the color right application dot collar that means that's the shape this is the name of the shape that's calling it the name.
Of the shape right so what do I want to do I don't want to know the name of the shape I want to know actually the fill and the four color I want to know the color that's it notice that every shape had a different color so I want the fill and then I want the four color RGB and I want to put that what do I want to put that I want to put that in the active cell so if I select it and now I think I actually in a prior simple I've already assigned this macro so let's just copy this take this right click assign the macro and it's already been assigned you set color click OK and now if I.
Click this and click this there it's 255 or 154 automatically obviously we need to do more we need to close this I need to write a little bit more we don't need this this color palette open but that's gonna get us our color automatically so see how easy that is all we're taking is the all it's taking is whatever we click it's gonna take that and it's going to put whatever the fill color is of that shape and it's gonna put that number right in here but I want to do more I also want to color Dee's I want a visual in other words we don't know what by looking at this number we don't know what color it is but it was nice I want to put that color right in D so let's do that let's do that let's continue on with our macro so active cell dot offset means I don't want the exact cell.
On an adjacent cell offset dot and then I'll go off said I want the same row so we're gonna keep that as zero and I want the column offset one to the right so it's gonna be one left would be negative 1 dot interior color I want to color the cell interior dot color equals what is it equal it equals the same thing we just did it equals it right here this so all I need to do is copy this and I'll fix that and then paste it here ok so active cell offset there's no dot there.
Ok there's no dot after offset ok offset there we go ok so add to sell-offs that equals the active shape same thing all I'm gonna do is I'm going to take in that same color that we put the number in and I'm gonna put it right in the interior color of this I'm just gonna change it to the interior color that's it and the last thing I really want to do is just hide that shape so active sheet dot shapes and then what is it color palette PA la tte I'll probably spell it wrong dot visible equals MSO false okay let's give it a try so basically that's what I want to do now let's give it a try.
And so it's gonna hide it and there's a little bit all we have to do but I want to select it and then red perfect and then okay so great so that worked but now we've hidden it so what I want to
Do is I want to display that color palette but I all want to display it if the user selects c3 or c4 now that's on sheet selection change so how do we do that we're in the setup screen that's setup she and we wrong selection change so we want to go worksheet selection change now if not if the user makes it change to some fields what fields of we just discussed we just discussed either.It's gonna be c3 or c4 so let's write that in c3 or c4 if they make a change to one of those make a selection change to one of those Ben what do I want to do I want to display that color palette so then shapes again color palette in this case the dot visible equals mmm so true right if they selected else what is it else they it's gonna be false right we want to hide it if they've selected anything else I want to hide it so else based and this is gonna be false I'll hide it okay that's.
Always deer let's try that now okay so we selected something got this green nice that's what I want okay the back color that's at the back color is white perfect now let's set the front color is maybe something like a blue here dark blue that's good with a theme okay great our QR form we're gonna set that to 150 150 pixels that means right in here I want to put it right around 150 by 150 in our queue our table size I want to make it much smaller we have a list I want to put those queue.
Ours 50 pixels by 50 pixels so when I generate those QRS okay I can generate them here great all right wow it's already 15 minutes out I've got a rush I don't think I'm gonna complete this in 30 minutes I talked too much okay not let's go I had QR code macros we've got the set color now all we need to do is write the macro that's gonna generate our QR code so let's do that sub generate single QR code just the single one is all I want to do on this one okay and I wanted.
I wanted to mention a few items because we have to dimension there it's going to be a picture that we're creating so we're gonna dimension the QR pick as string the cue our URL as string and the data is a string cue our data cue our data as string okay and also I want to make sure the for coloring back color strings for color as string and the back color back color as string okay great so the for color matte color has a string now that's all the strings but I also want to know what the size is let's dimension the cue our sides cue our size that's gonna be long as.
Long as we remember we have those variables set up already in our setup screen so we have the cue our variable has a lot the size and now what I want to do is with sheet 1 we're gonna focus on sheet 1 the first thing I want to do is I want to delete any cue our item that might be there we're gonna give it a specific name so dots shapes and then cue our item pick that's the name we're gonna use automatically for that your honor pick I want to delete that because if we're generating a new one I want to delete Guilderland but if it doesn't exist it's gonna create an error so we're gonna do on-air resume next remember I use autohotkey to automate my typing it's quick it's.
Easy I'll provide a training on that very soon I think it's been suggested I think it's a good idea to show you exactly how I do that okay an on-air resume zero so that's gonna do that that will delete the picture the current one if there is any okay so continuing on we also want to know what is the data what data do I want inside that I'm gonna choose this data here of course you can use anything you want up to nine I'm just gonna choose east six so we know that it's dot range e six so I'm gonna call it Q our data equals dot range e six that's gonna be our item name also known as.
QR data okay so we've got our QR data what else do we need we need the size and that's gonna be on a different sheet size is gonna be equal to and that's gonna be in our setup screen sheet 3 dot range and that's gonna be in C 5 C 5 remember C 6 is the smaller one that's going to go see 5 dot value that is gonna be a large QR sighs okay than that that was set to 150 okay so we've got our key our size now all we need is our 4 color and our back color so let's set that up so our 4 color is.
Equal to okay let's just copy and paste this and in this case our 4 color is going to be located in C 4 C 4 is our 4 color for color is in our Q B we also know that we just call it Q artist called for call a little bit more detail so it's but Q our four color and the back color Q our black color call that a little bit more descriptive okay so we know that that's located in C 4 and we know that our back codes look at it and see through so let's just write that up C 4 for color and back color we're gonna need more than that though I'm gonna change this to back and then we're gonna change this to C 3 okay but in actuality our QR code cannot read you can only mean hex values and this.
Is not a hex form so we need to convert it into a hex form in order for our URL to read it in for it so in its current form in this current number form it's not gonna be able to we need to convert it to a hex so how do we convert it to a hex well we can use write and then hacks to do that so it's just a two-step process so let's do that inside that okay so the 4 color I'm gonna make sure we're going to use the right of in fact we're gonna use the write up it just goes zero zero zero because I want five zeros and that's gonna set us out the right five zero two three four five because I want.
The right of and and the hex right well I add the hex of what this in this case hex of c4 and then what I want is the hex comma right and what is the length six I want six six characters okay that's it okay let's continue on so that's gonna get us partially but now let's continue on to give us the full hex value for color equals right again but in this case what do we want I want the current for color in its current form after it's been converted for color to I just wanted to write.
The two last ones on the right and what a lot I also want the mid form because we get to break it up into three different parts the left the right and the middle so how do we do that and the mid of the four color in this case starting it three and the length is going to be two characters and what are we at in this case left the left of the four color and then the length is two so it's gonna separate into three different parts and give us that hex value we're gonna do the same exact thing for back color same exact thing so all we need to do is copy this and paste it and of course we're.
Going to get rid of that change this to back color for these two back in fact here's a quick little way back and then back again and also back here and then in this case well you another way you can do is you can just do find we're looking for for color and we're gonna replace it with back color and that's only in the selected text back color using control f and replace all okay so it's replaced three that's another way to do it as opposed to retyping it and I just want to update this remember this is not C for this one C three okay so basically what it's going to do.
Is going to take that number convert it to a hex value using the right functions the mid functions and left functions can get us our exact values that we need and put it into hex form because it is the hex form that it's gonna be read by the API now we're ready to build our link and what is that let's take a look we know we were looking inside the API we know what is the basis of it we know it's gonna be this right here let's copy this and then build out the rest okay so back into our code our cue our URL is equal to then we must put the parentheses and but if we're next we're gonna put.
In data I want data I want data after that data is equal to what's data equal to and of course the QR data cue our date it was already defined that and what else and then I want the size right so then and right sees in this case and inside the link right and inside the URL not only and this and combines combines our variable with our tax string this and is inside the string we need that insight to come to let the API know it this is a different part ok so then in this case size is equal to n.
Quotation and of course the cue our size Q our size and of course it's Thai Kieu our site sizes to remember it's x times so we need you and again this case X which means the times like vertical times horizontal and again QR sighs one more time QR sighs okay great what else let's go in additional and let's go to the next line here and so now that we have the size size by size we can I want to add the character set right we want to specify a character set that's gonna help to make sure it's the right so and the character set is - the source character set - source is recommended.
By the API equals the UTF so it's in there recommendation so I'm following those utf-8 okay cuz that's the character set and also one more the target UTF we need to specify that as an as stated by the API and the character set that's target - equals UTF - 8 e CC equals now okay we're done with that that's going to cover the character code just to make sure there's no heirs and what are we now already for the color and the color what is the color is gonna be the for color so color is equal to and for color and that's already been converted to hex and of course the next one is the.
Background color and and the quotation marks again inside and we're separating it and this time it's B G color equals what of course it equals the back color back gotta have the an there and back color okay what next and we have more to do one nice I also want let's say we just add the margins the basic margins here so let's do that the margin and margin because that's also an option inside the FBI equals just put a zero and the Qzone whatever that means so and because I could look it.
Up equals l equals one and the format equal to PNG all right that's it for our link now we've created everything and next up what we're gonna be doing is we're going to insert the URL into the sheet now that we have all when you do it's insert so again let's do this with sheet 1.6 dot insert and now we just need to insert to cue our URL okay so now with that what do we need to do with that we need to sign it a name that way we know we can delete it so the dot name is equal to again the.
Same name that we just deleted up here it's good it's gonna be right here called QR item pick right here and I'll just copy paste this in here so now we have our QR name and are there and so once we have that we can then position it where do we want to position it well basically I want to position right in here in this box here so it's gonna start out and let's just say m8 and then all the way down here m8 is probably a good good or and let's put it at m7 and then we'll drop it and a 7-up drop down a little bit m7 is probably better so let's position it right there so the name Pig is equal but I want to put it in the middle I want to put it I want to Center didn't M so what I want to.
Do is I want to get the width of M and then I want to get the width of our QR which is 150 and then I want to divide the space between the two of them by two so how do we do that that way it's going to centered automatically so dot left is equal to sheet 1 range m7 dot left + right + cuz we're moving it over a little bit plus in this case print sees sheet 1 I want to know dot range and we'll just use m1 basically I want the width of column Emma's really what I want width of calling it - the width of the particular picture dot width take all that and divide it by 2 so that's we're.
Gonna we're gonna add it we're basically going to take the width of the column subtract out the width of the shape and then divide that by 2 that's going to create a equal spacing on either side okay great so now all we do is put the top position and then we're done dot top is equal to sheet 1 range m7 again m7 dot top okay and then a little bit lower so plus let's say 5 plus 5 okay so that's pretty much it now if case there's an air sometimes there's heirs or or we do want to add on air go-to but not correct not just yet let's test our code first before we add on air.
Go to next so before we do that all right so let's save it as we always want to do and then let's go ahead and take this macro and assign it to our button that we've already created it's a group button here this button here right click assign macro paste that in okay very good now let's run our code check for errors in a probably pictured all right we don't your space here we don't need this space that's not gonna help us let's reset this try to get and run it again application take a look all right it's generated there perfect just got to get that left possession right but that's exactly what I want now our code is generated okay let me just update the left there's probably a some math in there plus this should be - not equal so I messed that up all the time okay that's now.
Let's run it again all right that looks great now we're done with that and now all we need to do is ready to run our put our QR codes in our item list all we need to do is pretty much copy and paste this and then we run a loop so let's do that let's create a brand new macro I'm gonna copy this I'm gonna go down here I'm gonna call this sub batch QR code okay sub batch QR code that's good enough for me I'm gonna copy and paste everything in just in a paste everything in just as I did but now what we're going to do is we're just gonna add a little bit more too long so we need to run the last row I need to know basically what the first row is I need to know the last row of the items I need to check if a QR codes been generated if it has not put it generated and if it has then just.
Skip it so first thing we need to go is last row and we also need to loop through each row so let's dimension those rows as long so we have the QR so if we also need the last row as long the item row specific row ID and bro as long okay and that's it and then I want to actually dimension the QR shape because I want to see if it's there QR shape has a shape because Morgan is you're gonna run a test because what we're gonna do is we're gonna run a test to see if it's there okay so with sheet one of course this time we're gonna focus on sheet 2 so we'll get rid of that and we just need to.
Update any of the sheet 1 issues so our QR data is actually not going to be in East 6 is going to be in hey what do I want to put in here I'm gonna put in whatever is an A in the row that we're on so I'm gonna put that I'm gonna update that a and the item row of course we're gonna run the loop that Road doesn't exist we don't need this booth right now so we don't need that anymore so let's first of all let's get our last our last row is gonna be what equal to I just use autohotkey for that equals got range a and the last row that's gonna give us the last item row last item row and just one caveat if last row is it's less than four then exits sub there's no data than nothing we can.
Do okay for our item row equals it's gonna start off with four and our first item Row is located right here in four so for two last row okay and now we have to close our lip we're gonna close it all the way down here next item bro so the first thing what I want to do is check does the qr-code exist so what were winners we're going to assign a specific name to the QR code and it's gonna be very easy I'm gonna make it the same so each one's gonna have a different name so it's gonna be called item QR and then the item row so that's what it's gonna be so let's write.
Some code to see if it exists first so we can do with set the QR shape equal to dot shapes item QR and the item row okay we're gonna set it but if that doesn't exist it's going to create an error so we need to do on air resume next and then on air go to zero here and that's going to just in case so if there's doesn't exist so now what we can do is we can check to see if it exists if not QR shape is nothing then then that means it exists right then it already exists we don't need to create it if it already exists then go to next QR and all we need to do is just skip all the way.
Down here and right under the next one just put in next Q R and then with a column there that's gonna skip all that if it already exists no need to create one if it doesn't exist if it exists already okay so now we haven't we have our data or did isn't going to be in column one we know the QR sighs cure site actually is going to be changed right we want the smaller size and our smaller size is located right here in six so it's C six we just need to update that c6 okay so the color the background color that's not gonna change the URL is not gonna change everything the pic the name is going to change those so the name and we're not with sheet one we don't want to insert on sheet 1 in this case we want to insert it in sheet 2 so we've already so let's just put sheet.