Sub CreateWorkbook()
. . . No Change
Rem Merge the cells H15, I15, H16, and I16 Range("H15:I16").MergeCells = True
Rem Align the merged text to the left
Range("H15:H16").VerticalAlignment = xlCenter
Rem Hide the gridlines
ActiveWindow.DisplayGridlines = False End Sub
Cells Content Indentation
To programmatically indent the content of a cell or the contents of various cells, refer to that cell or to the group of cells and access its IndentLevel property. Then assign the desired value. Here is an example:
Range("A1").IndentLevel = 5
Cells Borders
The Line Style of a Border
A cell appears as a rectangular box with borders and a background. To programmatically control the borders of a cell or a group of cells, refer to the cell or the group of cells and access its Borders object. This object is accessed as an indexed property. Here is an example:
Range("B2").Borders()
In the parentheses of the Borders property, specify the border you want to change. The primary available values are: xlEdgeBottom, xlEdgeTop, xlEdgeLeft, and xlEdgeRight. Sometimes you may have selected a group of cells and you want to take an action on the line(s) between (among) them. To support this, the Borders property can take an index named xlInsideVertical for a vertical border between two cells or an index named
xlInsideHorizontal for a horizontal border between the cells.
After specifying the border you want to work on, you must specify the type of characteristic you want to change. For example, you can specify the type of line you want the border to show. To support this, the Borders object is equipped with a property named LineStyle. To specify the type of line you want the border to display, you can assign a value to the
LineStyle property. The available values are xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlSlantDashDot, and xlLineStyleNone. Therefore, you
can assign any of these values to the property. To assist you with this, you can type
LineStyle followed by a period and select the desired value from the list that appears:
Practical Learning: Specifying the Styles of Cells Border
Change the code as follows:Sub CreateWorkbook() ' CreateWorkbook Macro
' This macro is used to create a workbook for the ' Georgetown Dry Cleaning Services
' Keyboard Shortcut: Ctrl+Shift+W
Rem Just in case there is anything on the Rem worksheet, delete everything
Range("A:K").Delete Range("1:20").Delete
Rem Create the sections and headings of the worksheet Range("B2") = "Georgetown Dry Cleaning Services" Range("B2").Font.Name = "Rockwell Condensed" Range("B2").Font.Size = 24
Range("B2").Font.Bold = True Range("B2").Font.Color = vbBlue
Range("B5") = "Order Identification" Range("B5").Font.Name = "Cambria" Range("B5").Font.Size = 14 Range("B5").Font.Bold = True Range("B5").Font.ThemeColor = 5
Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5
Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B6") = "Receipt #:"
Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("G6") = "Order Status:"
Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B7") = "Customer Name:"
Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("G7") = "Customer Phone:"
Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous
Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5
Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B9") = "Date Left:"
Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("G9") = "Time Left:"
Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B10") = "Date Expected:"
Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("G10") = "Time Expected:"
Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B11") = "Date Picked Up:"
Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("G11") = "Time Picked Up:"
Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous
Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5
Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B13") = "Items to Clean" Range("B13").Font.Name = "Cambria" Range("B13").Font.Size = 14 Range("B13").Font.Bold = True
Range("B14") = "Item" Range("D14") = "Unit Price" Range("E14") = "Qty" Range("F14") = "Sub-Total" Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B15") = "Shirts" Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("H15") = "Order Summary" Range("H15").Font.Name = "Cambria" Range("H15").Font.Size = 14 Range("H15").Font.Bold = True Range("B16") = "Pants" Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B17") = "None"
136
Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("H17") = "Cleaning Total:"
Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("B18") = "None"
Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("H18") = "Tax Rate:"
Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I18") = "5.75" Range("J18") = "%" Range("B19") = "None" Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("H19") = "Tax Amount:"
Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B20") = "None" Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("H20") = "Order Total:"
Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous
Rem Change the widths and heights of some columns and rows Rem In previous lessons, we learned all these things Range("E:E, G:G").ColumnWidth = 4 Columns("H").ColumnWidth = 14 Columns("J").ColumnWidth = 1.75 Rows("3").RowHeight = 2 Range("8:8, 12:12").RowHeight = 8
Rem Merge the cells H15, I15, H16, and I16 Range("H15:I16").MergeCells = True
Rem Align the merged text to the left
Range("H15:H16").VerticalAlignment = xlCenter
Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
Rem Hide the gridlines
ActiveWindow.DisplayGridlines = False End Sub