Me increased row one and then we're gonna put our title in there we'll call this inventory manager and then now we'll go ahead and change the font to my rate probably it's a pretty clear font and we will take will probably increase the size as well my black that's pretty good one and then we'll center that and then maybe if forty-eight or so about that's pretty good size and then italicize and we're gonna give this a color I want a theme on this we'll make this one green so we'll call that a dark green then I'm gonna give this all the way a fade let's just highlight that and give this.
A fades to the background color so we're gonna for Matt those cells and we're going to color the background so when we go to fill then we're going to go to fill effects and we'll give it a slight probably a three step background so this will contain two and the will contain one so we'll go from the dark green to a medium green on this one darker to lighter and then on the row below it we're gonna go another fade and then we're gonna go to our main background color so format dis.
Row as well we're gonna go to those next green so we're gonna con the fill effects on this one we're gonna go from that medium green to the lightest green and that lightest green is going to be our main background color here so we'll go down one step below and then we'll go ahead and give our base color here we can raise this up a little bit in a moment so we'll give it that base color here and then that'll just be the light green which is our main base color which is that light green so now we get this kind of fade effect which is nice and so on this I'm gonna keep C column empty just.
As a space so you can increase your decrease and I'm going to start our our data here in column D there and then what I want to do is I want to create fields for our inventory manager the first is I want to create like let's say a customer or a vendor so we'll start out a customer and in this one I want to create two different types I want to create both the purchase and the sales I want to use the same screen for both of those so this field could be customer or could be vendor and I'll show you how that's gonna work later and we'll skip a row and then we'll just move to the.
Product number product number and then I want to add some product details in there so let's go ahead and add some product details let's go ahead and call it product product name and then let's see the standard purchase standard purchase quantity I want to know you know if we set a product so we want a standard purchase quantity and these are product details so that's gonna be very important for products so we're gonna have both sales purchases and products all in the same screen and I'll show you how that's going to work and then sales quantity here so that's.
Going to be our first column of data and let's just kind of make these why just so we can see that there they're gonna be feels we're users into that and then and then I want to do another column over in let's say G so we're gonna skip one have one space or column and then G we're gonna put in some more details so want to put it in let's say a transaction date here and then skip to and I also want to put in the sales price that's gonna be the item price there and then I also want for.
The product information these two rows are gonna be for product details right so we're gonna be able to hide that information or show it so I'll make that distinction very soon but let's go put in the product type because I want to know the product type and I also want to know a product description product description here so that's very important I want that and then I want to put the total here I want to have the total because it's gonna be the quantity times oh so this would be a formula and the idea is what I want to do is I want to have both sales and purchases here so but I want to alternate between purchase and sales so we can do that with a kind of a button.
Type of thing so let's go ahead and create that button now we're gonna use shapes on that so let's insert a shape here and let's go ahead and enter the background of that button we'll use an oval here and we'll create just a background of that and let me size that how about let's say about a height of point two two and then we'll give it a width of maybe point six five or so and then I want to also zoom in I want to round those I want to round it out so let's go ahead and create that kind of round shape there that's the kind of one I want I want to own a round shape let's code the.
Background our border similar to our theme so we'll use the shape outline the same of our our theme color which is the dark green and we'll give it a background maybe so of a grey or something like that because it's not going to be the main focal point the button itself will be and the button is going to be a circle so let's go ahead and insert that create a circle on that and let's go ahead and call it that and then we'll just give that circle let's say point let's create make sure it's even point one eight because I want it to fit within that and I'll show you how that works not eighteen point one eight and then also 0.18 on that and then we'll give it a shape color of let's.
Say green that'll be good and so border shape fill of green let's go let's go to filled green and die let's do then outline a bit a little bit darker color there okay so that'll look good and not so much of a not so much of an outline really almost no outline but just a very light outline there we go okay so what I want to do is basically I want to be able to click this button and have it move from here to here to here to here right and switching between purchase and sales so we have to.
Designate what is purchased and what is a sale and so let's go ahead and do that let's shrink this
Border we don't need this border it's so large we can shrink it down the outline of this can be also smaller it's a little bit big right now we'll set it to a weight of just let's say 3/4 a point that looks good okay so the idea is to use this as a switch so we can switch between purchase and sales so let's go ahead and make that distinction now and we'll go ahead and add in a text label here.So this is going to be for purchases and I'll do it all in caps purchase I want to show purchases on that there and we'll sender that and we don't need any background or any any fill or any border on that it's just gonna be for label purposes so no outline no fill purchase and we want it centered so that's gonna be for purchase and also let me go ahead and we don't need any padding on that so let's under size and properties move along to and we'll set that to the text box we.
Can we can set these to zero so because we have no border so it's no need for that alright so we've got that good let's go ahead and copy and paste that we can use it for sales this one's going to be sales on this so I want to switch between sales and purchases and if we move when we click here it's going to go to the right for sales if we click here it's going to go for purchases then we're gonna make changes based on that alright that'll be good so we'll use VBA to make that but right now I want to set up our admin area so the first is I want to know what transaction type so let's it Tran transaction type and we'll start it off at purchase purchase so we can set that up.
And we also want to know the selected row we're gonna have a table of transactions below here and I want to know what the row is for that so list is the selected row for the transaction so we'll put transaction row and we also want to know if it's a new transaction new trans and then that can be true or false so a market true for now we also want to know if it's the transactions going to be loading that's very important those three items if the transaction is loading or not when we select a transaction from the items from the table below we want all the data to appear here so we need to.
Know if it's loading or not so we will set its if it's a loading transaction load and that's gonna be true or false all right so we've got that let's size it up and let's color those differently so we know that their admin will give it a say an orange background so we know give it their admin color and also put some borders on it so it's clear that that those are going to be admin I also want to designate for the products we're gonna be adding new products and updating products so.
We need some product information on that so let's go ahead and add in some their product row we need to know that we also need to know if it's a new product and we also need to know if its products loading so kind of the same three types of fields product load and that's gonna be all true or false so let's just say you know well the product row here true this is gonna be true or false and this is gonna be true or false as well so those items are important so let's go ahead and color those give it the same information and let's say we're going to use what table is a tip will probably.
Start right on 17 so let's I'm going to use 16 for the mapping so let's call this a hydro and we're gonna have we're gonna use this for our table so our table we're gonna leave this blank C is gonna be 4 spacer so we're gonna start our table right here on 17 and our mapping will go in 16 so we'll color this gray all the way to say K and I want to call this gray because I'm gonna hide this we're gonna use a firm mapping and I'll show you how that works in just a little bit so let's go ahead and color this gray this this row is intended to be hidden our actual table headers were started on 17 so let's put in that information we want transaction type and we want a vendor or customer.
They're gonna go in the same row and then next up we want probably transaction date we all need all the information from the above fields into this table below product number we want that the price of that as well as the quantity and the total and then maybe some notes so that's going to be our table and then we're gonna have our data below here so let's clear that comes clear the colors out of that and we'll add in some information some data conditional formatting as well let's format.
This Center those and that will adjust the column Heights that's too much let's bring it over here the reason it's too much is because we have to manage the center this so we can do that merge and center now if we automatic and we need a little bit bigger now if we automatically click those there we go we're almost there we got to just increase some rows notes of course is going to be larger we'll need some for that the total so we're good we'll just well size them accordingly a little bit later on but they're okay for now well you will hi this is going to be our header.
Row so let's color that accordingly will format the cells that's off the screen here let's scroll
Up a little bit we will highlight this format the cells here and then we'll color those we'll give it a dark green border same with our theme color and we'll go a little thicker on the outsides and the insides we will keep the thin line here and here and then we'll give it a filter color here on the fill color here fill effects a little bit of a fade as well working with our theme all right.That'll good be good there you can see it there okay so we've got we've got our our make it bold and green color that green the dark green so now we've got our theme and our header covered we will add our border in here as well so right click that same border and I'm gonna format those cells sorry that was off the screen a little bit and then we use that same color but thicker borders on the side and we'll use the dotted line for our center dividers we won't use any horizontal I'll.
Use conditional formatting for that let's add in our conditional formatting right now for our table new rule we're gonna use mod and that's gonna be our formula that colors equal mod and this is going to color our even rows we'll give it a light green format that and I know there's quicker ways to format a table but I want to show you how it's done here we'll go with that light green but maybe a little bit lighter than that so that it's contrasting and we'll go with this little bit lighter there okay so now we've colored even rows using mod row 2 equals 0 and there we.
Go okay so our tables set and we have our let's go ahead and work on our information here this increased in size which we don't want because we need to set it point six five it's what we wanted that the reason is is we have to make sure that we size do not size cells so let's go ahead and set that up right click properties we always want to make sure that we do not size with the cells so that we don't have to continue changing them move the don't size with cells that's the setting.
We want so now we can reset the size back to what we want zoom in and shrink this down now that it won't now as we increase or decrease our column widths our shapes won't move around so that's good we can bring all this over a little bit so that it's within the area that we're focused on all right there we go and bring that over here this way everything centered over we're gonna use this area for our for our header row so put our buttons here we're gonna create add new new.
Transaction buttons delete transactions add photos and things like that this area will be for our title and our title is going to be based on our or types so for example if this says purchase it's gonna say purchase transaction so let's go ahead and write that in now I'll put it somewhere about here and let's call this equals because it's going to be based on the type so equals if and then say we say this equals purchase then we want this to equal purchase purchase transaction.
Otherwise sales transaction so that way we can have both okay good now we are set okay so now our title is going to let's increase that we want we want this I want it middle and I want it bold and I'm going to increase the font so that we can alright that's good and italicized bold and will underline it too there we go so now we know it's a purchase transaction but if this changes to sales then the title is going to change as well we can even make that a little bit bigger so it's clear I want to make sure it's very clear what type of transaction that user so when they change.
This when they change it it's going to change automatically in fact let's go ahead and write that macro right now so we can quickly change it back and forth we're gonna go into the VBA and the Visual Basic and you know what before we do that let's go ahead and save this project we don't want to lose any of the work we've been doing always save it all right inventory manager and then we'll give it an XLS or manager and then make sure it's got XLS then this will contain macro so we want to make sure we give it that there we go in alright so now we've done that and let's go create a module insert module and we will call this module let's give it some the.
Sheet macros because I want to create some macros based on the sheet so we will give it a name and the properties and sheet macros alright so those are macros that we're gonna focus on the sheep so that's gonna that's gonna be our starting point all right so let's now that we have that module we can create a button sub move but button we can use the same macro for both loops left or right so that's what we want to do now so we've got a macro before we continue with our macro.
We want a name our shape so it's always a good idea the part that we want to move let's give it a name back into that we'll call this little icon we'll call that move button so that we know okay now that's done we can set up with sheet 1 we're gonna be working with that so we can focus on that let's go ahead and say if we need to know if we're gonna be moving our shape back and forth we need to know what the current condition is in other words I need to know if it's purchase that I need to move it to sales if it's currently sales I need to move it back to purchase so from.
Left to right right to left we need to know that so let's go ahead and first determine what our current status is so we can write an if-then statement if dot range B b1 equals purchase then do something okay else do something else okay so if it's purchased what are we going to do well what we want to do is we want to move it to the right if it's purchased so let's go ahead and write that in dot shapes move button dot increment increment left 30 that's going to move it to the.
Right to the right left would be negative 30 and then we also want to set B 1 2 sales right if it's currently purchased we need to make it sale so dot range e 1 dot value equals sales all right so we've got that covered else what if it's not if it's else it's gonna be pretty much the opposite we're gonna move it negative 30 else negative 30 right the other direction and then we want to set this to purchase B 1 should be purchased so copy and paste that and set it to purchase that'll.
That'll set it up right there let's take a look at that and see how we have but there's more we need to add in this macro a little bit later on but for now we just want the simple button movement and we need to assign that macro to this button I'm gonna sign this macro to both dish shape and the shape to both of them so I'm holding down the control on both right click assign macro we only have one macro created so move button all right let's take a look all right let's set is currently on.
Purchases so let's set it to purchases and now we can click on it all right that's perfect now that's the one we want and we see at this name changes so good now we've got that let's move this over a little bit to the left just to line it up let's take a look now soo-min take a look so we can see how that looks zoomed in it's not quite centered as I like it but we'll fix that in a second all right let's Center everything highlight over it for a line middle there we.
Go now it looks good that looks just right okay perfect good now we've got our now we've got our macro that switches between purchase and sales and we're going to need that because there's gonna be a sales transaction or a purchase transaction okay so we're good with that let's continue on all right let's color our fields in white I will select on our fields here and we want to make sure that we have just those fields where values are gonna go in white so we can clearly see them apart okay and now also what I want to do is I want to make the distinction of what is.
A purchase and what is a sales not only by the title but I also want the colors so let's go in set these colors for sales in blue and purchases in red that's kind of a theme that I want to give it so that's gonna be important and let's set the product description a little bit wider do that and that's we will merge and center that where it's the center and then give it off to the left so it's got left out of justification note and we also need notes here too that's important notes.
We'll use the same thing for here I want it all alright good notes should also be merged merge and center and left accent merge Center and then left justified okay that's good that'll give us our fields as we know we're gonna put a picture in here and some buttons in here so that we can add pictures and I also want to put the quantity quantity on hand quantity on hand so we know how much we have and that'll go in here let's just put a spacer like five and that'll be a formula very soon all right quantity on hand all right so in order to differentiate between these the.
Purchase and the sales let's cut make some colors and then we'll use conditional formatting I'd like to color the font of these blue on sales and a darker red on purchase I look like color these right now blue let's give it a blue color and let's choose a color it's a little bit standing out maybe this blue here that kind of stands out okay we'll use that blue because I really want to differentiate and we're gonna use both sales and purchases we need to differentiate between those.
Colors now I'll also give it a border color which is also going to be different for both purchases and sales so let's create a border and the reason is I'm highlighting these fields in a way that it's independent so when we right-click and format those cells everyone's going to be formatted the same and let's give it a border use the same color which is this blue one here and I'll give it a all the way around plus a dotted line in the center which is going to be our divider that is our data.
I'll keep good that's what I want for sales and now for purchases I'm gonna have a darker red but I also want to color the background of it so let's give it this faded background I'm gonna do a slight fade of a blue so we're gonna go from green to blue I don't know if it's gonna be the most beautiful but I want to differentiate between sales and purchases and I want to make it very clear because just that the title is not enough so fill effects we're gonna start out with this green here which is what we already have and then we'll go on to a little bit of a blue or contrast maybe this blue here yeah that looks good okay so the rest is going to be with this blue and.
Then we can use conditional formatting to color it blue so here we have this blue and I'm gonna bring this blue all the way through all the way through here and then everything but the fields in which the user is going to enter data we're going to give it that blue that blue is going to change to red on purchases on purchases of that blue it's gonna change to red so keep that in mind and as I highlight all the cells that are going to be affected and I also will use fields in which there is a constant in which the user doesn't edit so that's the color I want to give let's go ahead and give it that blue that color let's go ahead and give it that blue which is this one.
Right here all right that's a good distinction now it's kind of looks odd with the color change here so I'm gonna give it a border around that I kind of a circular border so we'll insert the shapes and I'll use this rounded rectangle and I'll bring it all the way up here and I'll just surround that and then it will bring the corners in very tight so they're not very well rounded and we don't want to fill on that so no fill and what I want to do is I want to hug this and I'm going to use our.
Theme color which is that dark green and there therefore that would give it a nice blend there and it doesn't look good so let's go ahead and put that in green and we'll give us the outline of that dark green which is our theme color okay good so now we have we see what it's going to look like for our sales purchases are going to be red but we'll use conditional formatting to to do that I think we've got it covered now we can products products can or cannot be colored I'm not sure.
About that we can we'll see how it looks in other words these are specifically for products products and we're gonna be able to hide and show these let's give it a shape up we can start adding in buttons soon so we can see how this will come and insert some shapes for buttons we're gonna use our theme color and our theme shape I'm gonna round it rectangle that color is going to be a darker green we'll use maybe a dark green shape fill gonna use that dark green here we use that as our.
Theme color here we can create multiple buttons for that we don't want too many we're gonna add the photo we want to delete a transaction and we'll use a we want to delete it all right so let's go we'll just create these buttons there and we got a few too many there this will be add photo I can bring in our shapes and we can bring in our a difference you I want a different button to hide this is product information right here these rose nine to ten our product information so I want to do I want the ability if you hide those rows but I wanted to do is I want to do this I'm.
Gonna hide product information sometimes we want to see details on the project sometimes we don't so I want to hide actually it's gonna be four rows total so what we'll do probably also high 12 so the idea is is to just have your transaction information here and then when you want to see the product information you can enter the product you can into the sales price but when you want to see or update the product information you'll unhide rows nine through twelve so we're gonna do that and I'm gonna do that through VBA but I want to create some shapes that we can control the hiding and unhiding of that so let's enter a shape something like not an arrow but probably like this.
Will enter what this one one for hiding and one for showing so insert and then we'll do it just the opposite of that shape which is the up here which is here so these are our two these are the two shapes and let's size them and also color them we know the fill and we know the the style is here so let's fill that again alright so that's it when we duplicated that we don't need that all right okay so we have our shapes here let's go ahead and give it a theme that we like which is the.
Green and then the fill that we also are using so that's consistent and now we'll put it a size on that I'll selecting these and set them point six one and let's say point nine eight it'll give us a good they're at least the same so we can align them and what I want to do is I want to use one to hide and want to show and then we'll put them on top of those so we'll use this one to hide details and this one to show the details show details alright of course we're gonna set the text so.
That there's no padding around it so that all the text shows up go back into the format's here and under the text box we can set the write for a very little bit point zero one and then perhaps zero on the left I'll put the icon on the left the top and the bottom don't need it we will Center that vertically so that should be fine let's Center that all right and now let put it off to the right and exhale center it and then I'll use spaces cuz I want to move it over to the right but I want to keep basically the same format I just want to give some room for the icon that we're gonna add.