Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using vba to set Border style

0

I've set up vba to show different border line styles and border weights.   See routine named "Borders". The result of this routine is shown in Sheet1.

The first set of borders shown in columns C-D, rows 4-8, are line style xlContinuous with three different Weights, xlThin, XlMedium and XlThick.

The second set of borders shown in columns F-G, rows 4-8, are line style xlDashDot with three different Weights, xlThin, XlMedium and XlThick.

The third set of borders shown in columns I-J, rows 4-8, are line style xlSlantDashDot with three different Weights, xlThin, XlMedium and XlThick.

The results do not make sense as shown in Sheet1 of the attached file, my textboxes point to the ones that do not result in what one would expect.

What is the difference between Border Weight and Border Line Style in vba?

In Excel, I see a selection of 14 border styles including "none".  I was trying to figure out which one was which in vba.

-----

My next question after I get this resolved is to figure out the color of the lines.  Is there a way to put in RGB values or a HEX code for line colors and fill colors?

Here's the code

======================================

CODE

Sub Borders()

Range("C1:D1").Borders(xlEdgeBottom).LineStyle = xlContinuous

Range("C1:D1").Borders(xlEdgeBottom).Weight = xlThin

Range("C1") = "Bottom Border Weight xlThin"

Range("C3:D3").Borders(xlEdgeBottom).LineStyle = xlContinuous

Range("C3:D3").Borders(xlEdgeBottom).Weight = xlMedium

Range("C3") = " Bottom Border Weight xlMedium"

Range("C5:D5").Borders(xlEdgeBottom).LineStyle = xlContinuous

Range("C5:D5").Borders(xlEdgeBottom).Weight = xlThick

Range("C5") = " Bottom Border Weight xThick"

' Let's do DashDot

Range("F1:G1").Borders(xlEdgeBottom).LineStyle = xlDashDot

Range("F1:G1").Borders(xlEdgeBottom).Weight = xlThin

Range("F1") = "Bottom Border Weight xlThin"

Range("F3:G3").Borders(xlEdgeBottom).LineStyle = xlDashDot

Range("F3:G3").Borders(xlEdgeBottom).Weight = xlMedium

Range("F3") = "Bottom Border Weight xlMedium"

Range("F5:G5").Borders(xlEdgeBottom).LineStyle = xlDashDot

Range("F5:G5").Borders(xlEdgeBottom).Weight = xlThick

Range("F5") = "Bottom Border Weight xlThick"

' Let's do SlantDashDot

Range("I1:J1").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot

Range("I1:J1").Borders(xlEdgeBottom).Weight = xlThin

Range("I1") = "Bottom Border Weight xlThin"

Range("I3:J3").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot

Range("I3:J3").Borders(xlEdgeBottom).Weight = xlMedium

Range("I3") = "Bottom Border Weight xlMedium"

Range("I5:J5").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot

Range("I5:J5").Borders(xlEdgeBottom).Weight = xlThick

Range("I5") = "Bottom Border Weight xlThick"

Range("A1").Select

    Selection.EntireRow.Insert

    Selection.EntireRow.Insert

    Selection.EntireRow.Insert

Range("C2") = "Line Style xlContinuous"

Range("F2") = "Line Style xlDashDot"

Range("I2") = "Line Style xlSlantDashDot"

Columns("C:C").ColumnWidth = 24

Columns("F:F").ColumnWidth = 24

Columns("I:I").ColumnWidth = 24

Columns("E:E").ColumnWidth = 6

Columns("H:H").ColumnWidth = 6

End Sub

CODE

excel
MS Office Version: Home and Business 2021
posted by: SusanUser (rep: 16) on Thu Jul 20, 2023 at 6:49 pm
Answer
Discuss

Answers

0

Hello SusanUser and welcome,

You haven't uploaded a file but you have explained things quite well. I used the code you provided to get a better understanding.

I will answer your second question first. Yes, you can use RGB values to set the colour. In the "Format Cells" window, on the "Border" tab, at the lower left click the drop-down arrow beside "Automatic" under "Color". This will open a new window where you select the desired colour. At the bottom of this window click on "More Colors". This will open another options window. At the top click on the "Custom" tab and at the bottom there are three boxes where the RGB values are entered. My attached file has screen grabs to illustrate this.

As for your first question, you already have most of the answer as shown by your code. On the border options tab, the choices in the right column are most of the left column choices except in medium or thick weight. In my file attached I have recreated your Sheet1 using your code. Below that are the 13 different choices for borders (left column top to bottom, right column top to bottom). These were created using their default values. In module 2 you can see the VBA code for each.

Hope this helps.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Fri Jul 21, 2023 at 11:04 am
Discuss

Discussion

@Willie

Nice answer but missing one point- I think Susan wanted to know how to set RGB or Hex colours using VBA...

As your screen grab shows, RGB values are shown in the Custom tab of the colour selector, If I set a greenish colour there, I might get the values:
Red: 64
Green: 162
Blue: 143
Hex: #40A28F
These can be used, for example, to set the fill of a cell e.g. B2:
Cells(2,2).interior.Color = RGB(64, 162, 143)
where the selector RGB values are in sequence within the RGB function.

To use the Hex value instead (not sure why you'd want to!), the three hex numbers (2 digits each after the "#H" bit) are used. To set that Hex #40A28F in D6, use:
Cells(6,4).interior.Color = &H8FA240
where the two bold numbers are swapped c.f.the string shown in the custom colour selector.
John_Ru (rep: 6152) Jul 21, '23 at 5:00 pm
Line Syle Weight One question remains in regard to Line Syles & Weights.  Using my code, it appears that some combinations of Line Styles & Weights give unexpected results.  Using Line Style xlSlantDashDot with Weight xlThick gives not a slanted dashed line but rather a solid extra thick line.  Refer to cell i8 in the last respondent’s workbook.    Same for LineStyle xlDashDot with Weight xLThick, t  See cell F8.   Conclusion: Excel supports only 14 LineStyle / Weight combinations as shown in the Border menu in Excel.   One would think that there were 7 x 3 = 21 combinations (7 Line styles x 3 Weights).     Colors Yes, wanted to know how to set cell fill color of a cell with VBA.   I want to use the Hex color code in VBA because I might have Hex code rather than the RGB values.  For example, I have a dye and need it displayed on a website so I choose to use the Pantone color conversion system to convert the dye color to Hex Color Code.  For websites using HTML, a color is specified using the hex color code in the form of #RRGGBB.      My question is that if my understanding is correct in VBA the RGB order is switched to BGR as in the example above?  You said the if a Hex code is #40A28F (Red = 40, Green = A2, Blue = 8F), then in VBA, the code to change a cell's color is   
 Cells (6,4).interior.Color = &H8FA240
  Please  confirm my understanding.  
SusanUser (rep: 16) Jul 24, '23 at 1:38 pm
In regard to using the Hex code with VBA I confirmed that  VBA uses  H BBGGRR.  How stupid is that, my opinion of course.  In the "color" world, we use RRGGBB.  

==============

I'd still like a confirmation regarding number of LineStyle and Weight combinations.
SusanUser (rep: 16) Jul 26, '23 at 1:43 pm
Susan. I don't know the possible "number of LineStyle and Weight combinations".

As far as I know the properties are independent so all combinations are possible BUT xlThick seems to overpower and make dotted LineStyles look continuous (so just aviod relying on combinations with that!). I can't see why you need more than 14 combinations in a document but if your all red, you get 28. Add a third colour and you get 32 etc. Point is you should use what looks good/ prints fine and differentiates sets of data.

Hope that's enough on this question- kindly mark Willie's Answer as Selected.
John_Ru (rep: 6152) Jul 26, '23 at 2:23 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login