Excel vba put borders

The borders were created using the code below:. Borders xlEdgeBottom.

excel vba put borders

Sub main Range "C1:E1". There are 3 different border line thicknesses available:. The code outlook not sending pdf attachments was used to generate these lines:.

excel vba put borders

In this example the user selects the line style and line weight using drop down lists in cells A2 and B2. Upon selecting a new value from the drop down lists, the border on the bottom edge of the cells C4:F4 is updated:. The drop down list is created using data validation. The event handler executes when the user selects a new value from the drop down lists:. End Sub. The first part of the code checks which value was chosen from the drop down list in cell A2 and adjusts the border style of the bottom edge of the range C4:F4 accordingly:.

End If. You can download the code and file related to this article here. If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me.

Your email address will not be published. Leave a Reply Cancel reply Your email address will not be published.Borders are the boxes formed by lines in the cell in Excel. By keeping border we can frame any data and provide them a proper define limit. To distinguish specific values, outline summarized values or separate data in ranges of cells, you can add a border around cells.

Start Your Free Excel Course. Adding borders in Excel is very easy and useful to outline or separate specific data or highlight some values in a worksheet. Option for Borders is available in Home menu, under font section with the icon of Borders.

We can also use shortcuts to apply borders in Excel. For accessing Borders, go to Home and select the option as shown in below screenshot under Font section. Once we click on the option of Borders shown above, we will have a list of all kind of borders provided by Microsoft in its drop-down option as shown below.

Here we have categorized Borders in 4 sections. This would be easy to understand because all sections are different from each other. By this, we can only create one side border only for one cell or multiple cells. By this, we can create a full border which can cover a cell and a table as well. We can also remove the border as well from the selected cell or table by clicking on No Border. The Fourth section has a customized border section, where we can draw or create border as per our need or we can make changes in existing border type as well.

We can change the color, thickness of the border as well in this section of Borders. Once we click on More Borders Option at the bottom, we will see a new dialog box named with Format Cellswhich is a more advanced option for creating Borders. It is shown below.

We have seen the basic function and use for all kind of borders and how to create those as well in the previous example. Now we will see an example where we will choose and implement different Borders and see how those Borders in any table or data can be framed.

We have a data table, which has details of Owner, Block, and Apartment No. Now we will frame the Borders and see how the data would look like. As row 2, is the headline, so we will prefer a double bottom line, which can be selected from the third section.

And all cell will be covered with a box of All Borders which can be selected from the second section.Borders are a necessary part of every worksheet or in any word file. Borders separate data from one another it shows which part of data is referred to which area to avoid any confusion. Also, it looks good to have borders in our datasheet. In excel worksheet we have options for inserting borders manually, but how we do it in VBA is what we will learn in this article.

Borders in Excel

To apply borders in VBA we need to access the properties of a cell and in the current case, the property we need to access is borders.

Now we need to keep in mind that there are various types of options available in borders properties. Such as diagonal vertical up down etc. We will cover some of them here. Watch our Demo Courses and Videos. Let us use the basic enumerations what VBA provides us when we type the keywords to see the borders.

We will put borders in cell A1 and see the result. Step 2: Insert a new module from the insert tab provided. Click on the module we just inserted which will open a code window for us. Step 4: Activate the worksheet first in order to use its properties by the following code below.

Subscribe to RSS

Step 5: Now let us try to change the border of cell A1. Use the range method as below. Step 6: Select the borders properties which will give us an option to select the border style as follows.

Step 8: Now we need to use the line style for borders. After dot. Step Let us run the above code by pressing F5 and see the result in sheet 1 as follows.

Step 1: We already have our module inserted, Name a macro in it with another sub-function as follows. Step 3: Now let use the range method to activate the border properties such as shown below.

Let us try a few more of the border and line styles in another cell. This time we will use it in a cell range C5:E6. Now in this example, we will use borders around the cell covering the whole cell. Earlier what we did was border only one portion of the cell. Consider the following data we have in sheet 2 as follows.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

The dark mode beta is finally here. Change your preferences any time. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. I'm struggling with a macro that places thick borders around certain ranges. I've included an example that illustrates the problem.

Learn more. Asked 4 years, 11 months ago. Active 4 years, 10 months ago. Viewed 2k times. I will appreciate if you can help me with this issue. Please post the relevant part of your code, and try to say exactly what your problem is? Active Oldest Votes. If you don't want inside vertical or inside horizontal: Sheet1. Range "A1:B10". BorderAround xlContinuous, xlThick. Macro recorder gave me: Range "FG13".

Select Selection. Borders xlDiagonalDown. Borders xlDiagonalUp. Borders xlEdgeLeft. Borders xlEdgeTop. Borders xlEdgeBottom. Borders xlEdgeRight. Borders xlInsideVertical.

Borders xlInsideHorizontal. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.

Post as a guest Name.We use borders in excel to highlight our data, in any dashboard, it is very important skill to represent the important data and it is done by borders, borders are a property in VBA which can be accessed by using the range method and giving the appropriate border style as we know there are different border styles.

To make the reports looks appealing to the end user or readers formatting plays a vital role in it. I hope you are well aware of the formatting techniques in the regular worksheet. In this article, we will show you one of the most underrated formatting technique i. Creating a macro to apply excel VBA borders with different style making them available as an add-in to the excel ribbon makes the job easier whenever we want to apply VBA borders.

Every cell in a worksheet has borders and background colors. By default, every cell has no border and background color.

Excel VBA Macro: Add Gridlines/Borders To A Table (Dynamic Range)

In worksheet under HOME tab we have, we have border option and if you click on the drop-down list we have a variety of options. But in VBA first thing we need to do is to decide the cell or range of cells we are going to apply the excel VBA borders formatting styles.

For example, if you want to change the border of the cell B5 then you can write the code like this. After the border style has been selected we need to select the characteristic we want to work with. Once the line style property selected we need to set the line style type of line or type of borders in VBA we are going to apply. We have many options here. Now if I run the code it will apply the Double line to the bottom of the cell B5.

We can mention the line style, color of the line, border weight and many more things we can do with this method. You may also have a look at other articles related to Excel VBA —.

Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Login details for this Free course will be emailed to you.

Free Excel Course. Borders in VBA Excel We use borders in excel to highlight our data, in any dashboard, it is very important skill to represent the important data and it is done by borders, borders are a property in VBA which can be accessed by using the range method and giving the appropriate border style as we know there are different border styles.Adds a border to a range and sets the ColorLineStyleand Weight properties of the Border object for the new border.

You can specify either LineStyle or Weightbut not both. If you don't specify either argument, Microsoft Excel uses the default line style and weight. This method outlines the entire range without filling it in. To set the borders of all the cells, you must set the ColorLineStyleand Weight properties for the Borders collection. To clear the border, you must set the LineStyle property to xlLineStyleNone for all the cells in the range.

VBA Borders

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Skip to main content. Exit focus mode. Syntax expression. Example This example adds a thick red border around the range A1:D4 on Sheet1. Worksheets "Sheet1".

Range "A1:D4".

excel vba put borders

Related Articles Is this page helpful? Yes No. Any additional feedback? Skip Submit. Is this page helpful? One of the constants of XlLineStyle specifying the line style for the border. The border color, as an index into the current color palette or as an XlColorIndex constant. The theme color, as an index into the current color theme or as an XlThemeColor value.Each range of cells can accept up to 8 different types of borders :.

I have also provided a complete sample here. You can download the code and file for the sample here. Range "B2:D4". Borders xlEdgeLeft. The code below is equivilant to the code above:. Range cells 2, 2cells 4, 4.

Borders xlEdgeTop. Borders xlEdgeBottom. Borders xlEdgeRight. Borders xlInsideVertical. Borders xlDiagonalDown. Borders xlDiagonalUp. The user selects a border style from the drop down list in cell B1. The drop down list is created using data validation. The code used in this example can be seen below:. Borders xlInsideHorizontal. The event handler executes when ever the user selected a new value from the drop down list:.

End Sub. Range "G7:L15". The If statement below checks if the selected value from the drop list is the left edge:. You can download the file and code for this example here.