Beyond Excel Formulas: Build an Effortless Quotation System [Tutorial].

^{Prompt: An image of over-the-top mountain that inspires that it’s more beyond the top. Image should irradiate the happiness of success and as a point of beyond you can show a morning star or a boreal aurora this image should be crystal clear, well detailed and hyper realistic.}

^{Source: Generated on Bing.com}

^{Editing: May require minor adjustments in Adobe Express (express.adobe.com)}

Have you ever built a useful Excel spreadsheet at work that does mostly all of your activities in your daily basis but some times you feel shame on you because that file doesn’t have any format or is a file where you have some work arounds to copy your messy data some how format and paste in a nice and clean file or where you have some simple formulas like additions or subtractions and that’s it!! Of course, you’re the only one who can understand and work on that messy file, but that file is your baby your blank file where your imaginations flies?

But also, you want to do something amazing for yourself like put in that file a banded rows format and as always something came out and you just leave it for later or work in progress…

But today it’s your lucky day. How many times have you try to do a simple but complex quotation system? How many times have you search some but there is nothing or you have to pay for you. Good news at the final of the article I’ll provide to you the final version of the file.

So this time we’re going to work with the followings Excel features in order to make a user friendly file and even for yourself:

·      Filter Formula.

·      Named ranges.

-           Offset Formula

-           CountA formula

·      Data Validation.

·      Controls.

-           Combobox

·      Active X

-           Button.

·      Simple and powerful Macro.

·      TextJoin Formula.

·      Index Formula.

·      Match Formula.

·      Unique Formula.

·      Conditional Format.

Let’s get to work. Let’s say we have a file with five sheets:

·      Quotation.

·      Clients.

·      Materials.

·      Products.

·      Issuer, and

·      Control.

Well, the Quotation sheet will be our dashboard and main sheet. We’re going to use column B to F. So since it is a Quotation sheet we’re going to skip the quotation parts like company address, date and so on sine it is not a rocket science. So, it should look like this:

              I created the Control sheet to act as a bridge or helper sheet to get data that users should not have to manipulate but it is necessary for us to make the data consistent.

              So, let’s say that we have the following data on each sheet:

·      Clients

·      Materials

·      Products.

·      Issuer

We are going to make this top to bottom in the quotation sheet, so, the first formula I used was on cell F5 which is TODAY() that bring the current date based on your systems date. This formula doesn’t take any arguments, so we don’t have to make any further explanation.

The next feature I used in Excel was the Data Validation (You can learn more here Excel Data Validation) to find this feature go the Excel Ribbon Data > Data Tools > Data Validation.

But previous to Data Validation we’re going to the Control Sheet and type on the following formula: UNIQUE(OFFSET(Clients!$A$2,0,0,COUNTA(Clients!$A:$A)-1)) where (from inside out):

·      COUNTA(Clients!$A:$A)-1: This counts all non-empty cells in the Column A of the Clients sheet, then subtract 1. So for example, if we don’t have the minus -1 in the array we’ll get for example 37, but since our data has a header the last value will be an empty cell which can bring us an error, so to avoid that we subtract 1 to get in the mentioned example just 36. That way we’re subtracting the header value.

·      OFFSET(Clients!$A$2,0,0…): The OFFSET formula is a useful one when you’re using dynamic ranges so that formula means:

-           Start on Client!$A$2 cell of the Client sheet.

-           0 rows down. First 0.

-           0 rows columns right. Second 0.

-           Height is determined by the COUNT formula. You can learn more about Dynamic Ranges here.

·      Unique: From the range inside tis parentheses returns the unique values.

In modern Excel versions we have the spill formulas (you can learn more about Excel Spill Formulas here) that means that if array has more of one value it will “spill” in the adjacent (down and right) cell. To avoid this behavior, you can simple add the at symbol “@” after the equals sign.

Now we are going to do the Named Range, to do it on the Excel Ribbon let’s go to Formulas > Defined Names > Name Manager.

Hit in the New button:

It will show a windows where you have to put create the new Defined name in this case Clients.

·      Name: In this case Clients.

·      Scope: Refers if you want to make this the whole worksheet or just a sheet, in this case we select the Worksheet.

·      Refers to: Is where we put the formula: OFFSET(Control!$C$2,0,0,COUNTA(Control!$C:$C)-1) if you see with some variations it’s the same logic that we use when bring the unique data, with the exception that we won’t need to put inside the Unique formula.

Now we can select B9 cell then go to Data Validation. In data validation:

·      In Allow select List (Check the Ignore Blank and In-cell dropdown).

·      In Source use the formula = Clients.

·      Hit Ok.

 In the B10 cell we have a TEXTJOIN with INDEX and MATCH formulas that brings the data, stree, city, state and ZIP form Clients sheet: TEXTJOIN(". ", TRUE, INDEX(Clients_DB, MATCH($B$9, Clients, 0), {2,3})) & ", " & INDEX(Clients_DB, MATCH($B$9, Clients, 0), 4) & " " & INDEX(Clients_DB, MATCH($B$9, Clients, 0), 5) let’s break down this complex formula in smaller parts and explain it:

·      The MATCH formulas:

-           Looks up the value on B9 cell in the clients table (MATCH($B$9, Clients, 0) that Clients table it’s the same we have been created previously.

-           Then return the corresponding row number which ensures we bring the right data of every client.

·      The INDEX formulas:

-           Uses the row number we got on the MATCH function: INDEX(Clients_DB, MATCH($B$9, Clients, 0), {2,3}))

o  Client_DB is another Defined Name I have been created (OFFSET(Clients!$A$2,0,0,COUNTA(Clients!$A:$A)-1,COUNTA(Clients!$1:$1)) the difference between the Defined Names Clients and Clients_DB is the second COUNTA (COUNTA(Clients!$1:$1)) which represents the Width of the dynamic range while the first represents the height.

-           The {2,3} part brings us the second and third column from the Client_DB (Street and City)

-           Later it brings us the fourth and fifth columns (4 and 5) from the Client_DB which are state and ZIP.

·      The TEXTJOIN formula :

-           First Parameter (. ) adds period and space between elements (between Street and City)

-           Second Parameter (TRUE) ignores empty cells if Any.

-           Third Parameter. The array of values to join (in this case the INDEX/MATCH formulas) You can lear more about INDEX MATCH by clicking here.

·      The ampersands (&):

-           Concatenates (Join) parts together.

-           Add appropriate punctuations and spaces.

·      In resume this formula joins:

-           The period is added between street and city.

-           State is added after comma and space.

-           ZIP code is added after space.

On B11 cell we write the formula ="Ph. "&INDEX(Clients_DB,MATCH($B$9,Clients,0),6)&"  Email: "&INDEX(Clients_DB,MATCH($B$9,Clients,0),7) which is just a concatenation of two values, the phone number and the email address of the client.

In cell F9 we just put a simple addition formula F5 + 30 where F5  is the current date and the + 30 parts makes one month later valid until the Quotation.

On cell F10 I did another Data Validation for the issuer or the one who prepares the quotation.

·      Data Validation:

-           Allow: List

-           Source: PreparedBy

o  Defined Name: PreparedBy: OFFSET(Issuer!$A$2,0,0,COUNTA(Issuer!$A:$A)-1) this formula takes the information from the Issuer sheet.

Now the thins in this worksheet become more interesting and as the title says, Beyond the Excel Formulas. So let’s break it down!

·      Go to Excel Ribbon Developer > Controls hit Design Mode that way Excel allows you to manipulate the controls.

* If you don’t have the Developer Tab hit the File tab.

  Then Options

  In the Excel Options menu go to Customize Ribbon then Check the Developer     check-box.

·      Then hit the Inset > Combo Box (Form Control)

·      Then adjust the Combo Box at the desired size (Preferred to make it fit into rows 14 and 15 on the B Column.

·      While selecting or after selecting the Combo-box go to the Developer tab and hit Properties.

·      It will show you a Format Control there you will:

-           Input Range: Products (This is a Named Range similar to the Client we already create).

o  First of all, go to Control Sheet. On A2 cell type the formula: UNIQUE(OFFSET(Products!$A$2,0,0,COUNTA(Products!$A:$A)-1)) Similar to the Clients one we already have created.

We made this UNIQUE since in the Products sheet we have an Item and Material related to it. Since in the Quotation we related the Combo-Box to this Item and it will be a lot of ITEMS we need to make this unique in order to only show 1 item and not all of them because it won’t make any sense to show repeated values in a Combo-Box. It loses its essence.

o  Then go Formulas > Name Manager then create a new Products Defined Name with the formula: OFFSET(Control!$A$2,0,0,COUNTA(Control!$A:$A)-1) again very similar to the Clients one we already have made on Control!C2 cell.

-           Cell Link: Here you’ll link the $B$15 cell which will change depending on the number of the row (We will explain this in the following lines)

-           Drop Down Lines: Is the number of lines you want to shoe in the dropdon, in this case 5.

-           3-D Shading. Chek this checkbox if you want to make the 3D effect.

·      Since Cell $B$15 will change to 1, 2, 3 and so on depending on the selecting value and that real number doesn’t mean anything we have to give it a meaning.

-           To give it a meaning in the B14 cell type the formula: INDEX(Products,B15) The breakdown of the componets:

o  Products. This is the dynamic range we have created in Control sheet to bring the unique values from the Items we have on the Products sheet.

o  B15. This is the cell that contains the number of the row.

o  INDEX Function. Is the formula that help us to look on the array/range Products and help us to find the row corresponding to B15 number. Very clever uh?

-           That way we can search the products by Item. This will be very handy when using the filter formula.

·      To hidden the number related to the Combo-Box on cell B15 select that cell, then hit CTRL + 1 on the keyboard to show the Format Cells menu.

-           In Category select Custom.

-           In Type change to three semi-colons ;;;

We are almost done until here but there is more magic inside this Excel Quotation file just let me show you it. 

On cell B18 we use the formula FILTER(Materials, Products_List = $B$14, "Product not Found") So let’s explain the formula step by step.

Remember the Syntax its FILTER(array, include, [if_empty]), but remember that we are using Defined Names so Materials comes from: OFFSET(Materials!$A$2,0,0,COUNTA(Materials!$A:$A)-1) if you haven’t done it, it’s time you did it, and Products_List which comes from OFFSET(Products!$A$2,0,0,COUNTA(Products!$A:$A)-1) now let’s break the FILTER formula:

·      Array: This comes from the Materials Named Range, from the sheet Materials which its where user captures the material and its unit price. This is the range or array we want to filter. In this Case Materials.

·      Include: From Products_List Defined Name, those are the items that contains the material we want to filter. I other words those are conditions that we have to met. In this case all the Products_List = $B$14

·      [IF Empty]: The valie we want to return if there is no results. This is optional. In our example I type: Product Not Found.

As you can se every type we update the Combo-box (Very important to de select the Design Mode button.

Search the IMG to do not recapture the same image.

Next formula its on cel C18 which is: IF(Quote_Materials="","",INDEX(Products_DB,MATCH(Quote_Materials,Products_Mat,0),3)*$D$14). As you can see there are several Named Ranges:

·      Quote_Materials: OFFSET(Quotation!$B$18,0,0,COUNTA(Quotation!$B:$B)-18,1). For me this is kind of elastic formula since depending of the size of the range it will be smaller or larger.

·      Products_DB: OFFSET(Products!$A$2,0,0,COUNTA(Products!$A:$A)-1,COUNTA(Products!$1:$1)). This Named Range takes the length of the rows COUNTA(Products!$A:$A)-1 and the width of the columns COUNTA(Products!$1:$1) of the Products sheet (Where we capture the Item, Material, Quantity per unit and Unit.

·      Products_Mat: OFFSET(Products!$B$2,0,0,COUNTA(Products!$B:$B)-1) of the Products sheet.

Now let’s break down the formula for easy understanding.

·      IF statement. IF(Quote_Materials="", "",[Rest of the formula]

-           Checks if the array Quote_Materials is empty.

-           If empty returns nothing, I mean “”

-           If not empty, continues with the formula.

·      MATCH statement. MATCH(Quote_Materials, Products_Mat, 0)

-           This looks for the value in Quote_Materials, since it is array we don’t have to put specific value since it takes the one in the current row.

-           Then searches it in the Product_Mat range.

-           The 0 means to search the exact match.

-           Finally returns the position (row number) where it found the match.

·      INDEX part. INDEX(Products_DB, [row from MATCH], 3)

-           Takes the range from Products_DB range.

-           Uses the row number we found from MATCH.

-           Gets the value from column number 3 of that row.

·      The Multiplication part.

-           Takes the value we found.

-           Multiplies it by whatever we have in cell D14 (user defines that value)

 * This is a Spill formula. It means that in Excel autofills al the values found in the array. Microsoft implement this on version 2002 of Office 365 and later on Office 2021.

On cell D18 we are going to put the formula: IF(Quote_Materials="","",INDEX(Products_DB,MATCH(Quote_Materials,Products_Mat,0),4)) as you can see it pretty similar to the C18 they’re two changes, the first one we bring the column 4 instead of 3 in the INDEX part. Since we need to bring the unit (pieces, gallons, onces, etc). The other key change is that we won’t multiply by cell D14 anymore.

In the Cell E18 we type the formula: IF(Quote_Materials="","",INDEX(Materials_Price,MATCH(Quote_Materials,Materials,0))) which is another INDEX/MATCH function. The key changes are:

·      Materials_Price Named Range that comes from the sheet Materials. Here we’re using the dynamic formula OFFSET(Materials!$B$2,0,0,COUNTA(Materials!$A:$A)-1) for the Named Range.

·      Materials. This Named Range we already have used it on B18 cell.

On F18 cell we put the formula IF(Quote_Materials="","",Quote_Qty*Quote_Price) which is the easiest on this set of formulas. We’re using the dynamic ranges:

·      Quote_Qty Named Range is defined using the following dynamic OFFSET formula: OFFSET(Quotation!$C$18,0,0,COUNTA(Quotation!$B:$B)-18). Let's break down why the -18 adjustment is crucial in this formula:

-           The range starts at cell C18 on the Quotation sheet.

-           The height of our dynamic range is calculated by counting cells in column B

-           Without the -18 adjustment, the formula would create an unnecessarily large range:

o  If column B has 46 cells with data, the range would be C18:C46.

o  However, our actual data only needs to span C18:C28.

-           By subtracting 18 from the total count, we adjust for the starting position at row 18, ensuring the range captures only the relevant data

This adjustment is necessary because COUNTA returns the total number of cells with data in column B, but we only want to count rows starting from row 18 downward.

·      Quote_Price Named Range. Similar to the Quote_QTY, but starting from E18 on the OFFSET formula: OFFSET(Quotation!$E$18,0,0,COUNTA(Quotation!$B:$B)-18)

From here we’re practically finishing our Quotation System, but there are some points that enhance the experience with this spreadsheet, and we can achieve this with macros to automate it and avoid manual procedures.

Now the things come more interesting. Let’s open the Visual Basic editor by hitting ALT + F11 or by going to Ribbon Developer > Code > Visual Basic.

You should see a window like this:

Whatd does this macro do? In simple terms it does:

·      Updated a quotation template with fresh data. That why I told early that the Namd range Quote_Materials is “elastic”

·      Applies professional formatting.

·      Calculates totals and taxes.

·      Makes everything looks clean and consistent.

Now let’s break down the code:

1.      Disable Screen Updating. This line disables the screen updating temporally. It speeds up the process and prevents the user from seeing each step being executed in real time:

Application.ScreenUpdating = False

2.      Declare Variables.

Dim ws As Worksheet ‘ws: To store wor 
Dim lastRow As Long ‘ lastRow: to store last row data.
Dim usedRows As Long ‘usedRows: calculates the number of rows used excluded headers.

3.      Set the worksheet.

Set ws = ThisWorkbook.Sheets("Quotation") ‘Assigns the Quotation worksheet to WS.

4.      Count the number of rows.

usedRows = Application.WorksheetFunction.CountA(ws.Range("B:B")) – 18 ‘Counts the number of rows in column B. The -18 adjusts the headers rows, meaning we only account for actual data rows.

5.      Calculates the last row.

lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row + 6 ‘Determines the last row with data in column F and adds 6 rows to account for the totals and formatting sections.

6.      Clear Previous Data.

ws.Range("B18:F" & lastRow + 6).Clear ‘Clears the old data from rows 18 through lastRow + 6 in columns B through F to make way for new information.

7.      Since we deleted all the information Insert product formulas.

-           ws.Range("B18").Formula = "=FILTER(Materials, Products_List = $B$14, ""Product not Found"")" ‘Inserts a Filter formula in cell B18 to display products matching the value selected on B14. If not match is found, is shows “Product not found”
- ws.Range("C18").Formula = "=IF(Quote_Materials="""","""",INDEX(Products_DB,MATCH(Quote_Materials,Products_Mat,0),3)*$D$14)" ‘Brings the material and multiplies by the quantity on cell D14
-           ws.Range("D18").Formula = "=IF(Quote_Materials="""","""",INDEX(Products_DB,MATCH(Quote_Materials,Products_Mat,0),4))" ‘Brings the kind of unit.
-           ws.Range("E18").FormulaR1C1 = "=IF(Quote_Materials="""","""",INDEX(Materials_Price,MATCH(Quote_Materials,Materials,0)))" ‘Brings the price
-           ws.Range("F18").Formula = "=IF(Quote_Materials="""","""",Quote_Qty*Quote_Price)" ‘Calculates the subtotal.

8.      Replace the @ symbols. Since this spreadsheet was made in a recent Excel version we have the spill formulas as we already explained we have to take off the @ to let excel spills the value otherwise only brings the first one.

ws.Range("B18:F" & lastRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

9.      Update the lastRow After formula insertion.

lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ‘Recalculates the lastRow after formulas have been added to the range updating based on the actual data.

10. Format the Data Range.

-           ws.Range("C18:F" & lastRow).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" ‘Format the range with accounting format.
-           ws.Range("C18:F" & lastRow).FormatConditions.Delete ‘Format the previous conditional format.
-           ws.Range("C18:F" & lastRow).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0" ‘If number is a negative put it in red.
-           ws.Range("C18:F" & lastRow).FormatConditions(1).Font.Color = RGB(255, 0, 0)

11. Insert Totals and Tax Calculations.

-           Subtotal.

ws.Cells(lastRow + 2, "E").Value = "Subtotal" 
ws.Cells(lastRow + 2, "F").Formula = "=SUM(F18:F" & lastRow & ")" ‘Sums the total cost from column F and displays it as the Subtotal.

-           Tax Rate.

ws.Cells(lastRow + 3, "E").Value = "Tax Rate"
ws.Cells(lastRow + 3, "F").Value = 0.0875 ‘Assumes an 8.75% tax rate, calculates the tax based on the subtotal and applies percentage formatting to the tax rate.

-           Note about Questions concerning.

ws.Cells(lastRow + 3, "B").Value = "If you have any questions concerning this quotation, please contact: "

-           Sales Tax Calculation.

ws.Cells(lastRow + 4, "E").Value = "Sales Tax"
ws.Cells(lastRow + 4, "F").Formula = "=F" & lastRow + 2 & "*F" & lastRow + 3 ‘Multiplies the Subtotal by the Tax Rate.

-           Issur Data.

ws.Cells(lastRow + 4, "B").FormulaLocal = "=F10 & "" at "" & INDEX(PreparedBy_DB,MATCH(F10,PreparedBy,0),2)" 

-           Other Data.

ws.Cells(lastRow + 5, "E").Value = "Other"
ws.Cells(lastRow + 5, "F").Value = 0

-           Total Information of the Quotation.

ws.Cells(lastRow + 6, "E").Value = "Total"
ws.Cells(lastRow + 6, "F").Formula = "=F" & lastRow + 2 & "+F" & lastRow + 4 & "+F" & lastRow + 5 ‘Adds Subtotal, Sales Tax and Other.

-           Thank you for your business note.

ws.Cells(lastRow + 6, "B").Value = "Thank you for your business!"

12. Bold Important Notes.

ws.Range("D" & lastRow + 2 & ":E" & lastRow + 6).Font.Bold = True ‘Makes Subtotal, Tax Rate, Sales Tax, Other and Total labels bold for emphasis.

13. Applies account format.

ws.Range("F" & lastRow + 2 & ":F" & lastRow + 6).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)".

14. Salas Tax Percentaje format.

ws.Cells(lastRow + 3, "F").NumberFormat = "0.00%"

15. Clear previous Conditional Formatting to make a smooth transition.

ws.Cells.FormatConditions.Delete

16. Applying format to Subtotal, Tax Rate, Sales Tax, Other and Total formatting.

-           Bold orange bottom border”
With ws.Range("F" & lastRow + 6).Borders(xlEdgeBottom)
       .LineStyle = xlContinuous
       .Color = RGB(255, 165, 0) ' Orange bottom border
       .Weight = xlThick ' Thicker bottom border (approx. 3 times thicker)
   End With
-           Apply black borders to the top, left, right and bottom cells:
With ws.Range("F" & lastRow + 6).Borders(xlEdgeLeft)
       .LineStyle = xlContinuous
       .Color = RGB(0, 0, 0) ' Black left border
       .Weight = xlThin ' Normal thickness for left border
   End With
   With ws.Range("F" & lastRow + 6).Borders(xlEdgeTop)
       .LineStyle = xlContinuous
       .Color = RGB(0, 0, 0) ' Black top border
       .Weight = xlThin ' Normal thickness for top border
   End With
   With ws.Range("F" & lastRow + 6).Borders(xlEdgeRight)
       .LineStyle = xlContinuous
       .Color = RGB(0, 0, 0) ' Black right border
       .Weight = xlThin ' Normal thickness for right border
   End With

-           Fill the cells with color:

ws.Range("F" & lastRow + 6).Interior.Color = RGB(15, 158, 213) ' Fill with #0F9ED5
ws.Range("F" & lastRow + 6).Font.Color = RGB(255, 255, 255) ' White text
-           Make the text bold.
ws.Range("F" & lastRow + 6).Font.Bold = True

17. Conditional Formatting Zebbra Style.

-           Define the variables.
Dim rng1 As Range, rng2 As Range
Set rng1 = ws.Range("B18:F" & lastRow)
Set rng2 = ws.Range("F" & lastRow + 2 & ":F" & lastRow + 5)
-           Even Rows Conditional Formatting.
ws.Range(rng1.Address & "," & rng2.Address).FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW()-16,2)=0"
ws.Range(rng1.Address & "," & rng2.Address).FormatConditions(1).Borders.LineStyle = xlContinuous ‘Applies continuous border to even-numbered rows.

-           Odd rows Conditional Formatting.

ws.Range(rng1.Address & "," & rng2.Address).FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW()-16,2)=1"
ws.Range(rng1.Address & "," & rng2.Address).FormatConditions(2).Borders.LineStyle = xlContinuous
ws.Range(rng1.Address & "," & rng2.Address).FormatConditions(2).Interior.Color = RGB(202, 237, 251) ' #CAEDFB ‘Applies a light blue background to odd-numbered rows, giving the table a zebra stripe effect for better readability.

18. Re-enable Screen Updating.

Application.ScreenUpdating = True ‘Turns screen updating back on after all the changes are made, allowing the user to see the updated content.

19. Completation Message.

MsgBox "Quotation has been updated successfully!" ‘Displays to the user a message box informing the user that the quotation update is completed.

If you’re still here. Pretty awesome hu? Until here we are done with our quotation system, or we do? Well, I have the cherry on the top to make the users experience more enjoyable. The thing we are going to do is to create buttons so when user adds more Clients or Materials or Products or even an Issuer that button lead the user to its corresponding place in the right cell just for the user start typing.

·      Go to developer tab Developer > Controls > Insert

·      Then in ActiveX Controls > Button. In this control we select Active X because ActiveX allows us a huge amount of customization also we need to add an Event, in this case a mouse event.

You should look a button like this:

·      Now hit the right mouse button and select properties.

·      In the properties we will change:

-           BackColor: (Optional) I put color &H00D5E2FB& but you can pick wathever you like.

-           Caption: In this Button type Add Client.

-           Font: You can pick whatever you like I pick Tahoma.

-           FontColor: I picked &H00808080&

-           Height: You can draw it in the size you like.

-           Width: You can draw it in the size you like.

·      In the same way we are going to add three more buttons:

-           CommandButton2: Add Material.

-           CommandButton3: Add Product.

-           CommandButton4: Add Preparator.

Should look like this:

·      It is time to add some code, to do it hit with the right click and select View Code

-           Create a New Module if there’s any. Right click on the VBA project and insert Module.

-           First part. Make the modular, program-oriented design which focuses on Code Rehusabilty. Instead of repeating the same logic across multiple macros, the main functionality is in just ono rehusable single macro called GoToLastRow this macro can be called from another subrutines with different arguments to make code easy to maintain and efficient.

   Sub GoToLastRow(sheetName As String)
       ' This macro selects the last row in column A of the specified sheet
       Dim lastRow As Long
       Dim ws As Worksheet
       Set ws = Sheets(sheetName)
       
       ws.Activate
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
       ws.Cells(lastRow, 1).Select
   End Sub

Quick overview.

o  GoToLastRow macro takes argument sheetName (string) this is the name where the macro will operate.

o  This makes the macro reusable for different sheets

o  By using Sheets(sheetName), the macro dynamically selects any worksheet based on the sheetName parameter rather than hard coring the sheet.

o  The ws.Activate line ensueres that the specified sheet becomes the active sheet before any further action is taken.

o  lastRow = ws.Cells(ws.Rows.count, “A”).End(xlUp).Row + 1 Dinamically finds the last row with data in column A. Remember that the + 1 is to select the first empty row. That ensures that the macro can adapt to sheets with varying amounts of data.

o  ws.Cells(lastRow, 1).Select Picks the first empty cell row in column A.

This macro is reusable for its modularity since focuses in one simple task, navigating to the last empty row in column A. This logic is encapsulated, so it can be reused with different inputs, reducing redundancy.

This macro is also scalable for example if you want to find last row in different column, you only need to modify this macro.

-           Now we can use the Macro to go to the last row from the different sheets:

Sub GoToLastRowClient()

GoToLastRow "Clients"

End Sub

-           Same with Materials, you only have to change “Clients” to “Materials” instead rewrite the whole macro.

Sub GoToLastRowMaterials() 
	GoToLastRow "Materials" 
End Sub

-           Same way with Products.

Sub GoToLastRowProduct() 
	GoToLastRow "Products" 
	End Sub

-           And of course Issuer.

Sub GoToLastRowIssuer() 
	GoToLastRow "Issuer" 
	End Sub

              This macro uses the fours principles of Program-Oriented Design.

-           Reusability. Avoids repeat same logic in multiple subroutines making the code easy to maintain.

-           Modularity. Each subroutine is small and specific, its purpose its to call the main macro and apply the specific sheet name.

-           Efficiency. Avoids code duplication and writing nearly identical code that way also avoids potential errors and redundancy.

-           Scalability. Macro can easily scale if you need to add more sheets or modify the logic.

·      Now we have to link our click events to call the macros with our ActiveX buttons. In the Visual Basic select the Sheet you want to add the click event.

-           For Clients:

Private Sub CommandButton1_Click()
   ' Call the macro that takes you to the last row of column A in "Client"
   GoToLastRowClient
End Sub

-           For Materials:

Private Sub CommandButton2_Click()
   ' Call the macro that takes you to the last row of column A in "Materials"
   GoToLastRowMaterials
End Sub

-           For Product:

Private Sub CommandButton3_Click()
   ' Call the macro that takes you to the last row of column A in "Products"
   GoToLastRowProduct
End Sub

-           For Issuer.

Private Sub CommandButton4_Click()
   ' Call the macro that takes you to the last row of column A in "Issuer"
   GoToLastRowIssuer
End Sub

              At this point our Quotation system should see like this:

              You can create controls like the buttons we already have made to the rest of the sheets except for the control one, that sheet also you can hide in order to avoid final user manipulation and messing up.

              If you do so, don’t forget to create a Macro to return from the other sheets (Clients, Materials, Product or Issuer) to the Quotation sheet.

Sub GoToQuotation()
   ' This macro selects cell B9 in the "Quotation" sheet
   Dim ws As Worksheet
   Set ws = Sheets("Quotation") '<--- Directly referencing the specific sheet name
   
   ws.Activate
   ws.Range("B9").Select '<--- Selects cell B9
End Sub

As you can see this macro is pretty similar to the GoToLastRow one but in this case we need to go to a specific sheet (Quotation) and specific cell (B9).

And similar we did for the Quotation sheet we need to add in the Visual Basic shets the click event in order to make the ActiveX button work:

	Private Sub CommandButton4_Click()
	   ' Call the macro that takes you to the last row of column A in "Quotation"
	   GoToQuotation
	End Sub

You can create buttons through the sheet to navigate between them but don’t forget to put the click event in every single sheet to make them work, in the file I attached is already set so you can compare or adapt it to your own needs.

As you can see in this pretty extensive post with simpler thinks you can create a very customizable Quotation system that its intuitive, scalable but over all is visual attractive to the user and intuitive.

I’ll give you a copy of this spreadsheet for free so you can start practicing, make your modifications. Any question you can subscribe and add your comment to the post.

Just click here to download it.

Stay curious, stay focus keep learning with us in Excel SolutionsV.

See you in the next post!