[RESOLVED] Placing a border around a range of cells in Excel file from VB6

[Bezzie]

How do one get a border around a range of cells, and not the individual cells in the range, in Excel worksheet created from VB6?

Everything works when creating the worksheet but for the life of me I can't get it to make a border around a range of cells.

The closest I can get is with the following code:

oXLBook.worksheets("Selected Birds").range("G" & _
        nRowGrandTotal & ":M" & nRowGrandTotal).Borders.LineStyle = -4119

But that draws borders around each cell in the range.

I tried merging the cells and then draw a border but that causes the currency symbol to be at the left of the cell and the amount at the right of the cell and that looks really stupid.

Alternatively, How do I get the amount centered in the merge cell with the currency symbol just in front of the amount?

[TysonLPrice]

Sometimes it helps to do all the changes manually in Excel while recording a macro. Many times, even though it is VBA, it will give you clues how to do it.

[Elroy]

Yeah, I too would record a macro, and then patch up that macro from my VB6 automation code. Typically, the macro recorder puts a lot of "fluff" in the code which can be deleted. Also, it doesn't always fully specify objects, which, in VB6 must be fully specified to the Excel application object. But all of that is far easier than figuring out the object-tree and doing it manually.

[SamOscarBrown]

 'draw borders
        With oSheet(1).Range("c3:d3").Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:d3").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:d3").Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("d3:d3").Borders(xlRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With

will draw a border around cells C3 to D3

[SamOscarBrown]

As far as the merge...I've got to go see one of my examples

[Bezzie]

Where are the xlLeft, xlRight etc. defined? Compiler tells me variable not defined.

[SamOscarBrown]

How are you using Excel...early or late bound?

[SamOscarBrown]

As far as centering on two or more adjacent cells:

with ows1   'where ows1 is the declared worksheet
With .Range("c30:d30")
            .HorizontalAlignment = xlCenter
end with
end with

centers whatever text you put in that cell (those cells)

[Bezzie]

 'draw borders
        With oSheet(1).Range("c3:d3").Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:d3").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:d3").Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("d3:d3").Borders(xlRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With

will draw a border around cells C3 to D3

The last one (xlRight) you have d3:d3 where the others are all c3:d3

I tried with my range specified instead of c3:d3 but it still draws lined around each cell in the range.

[Bezzie]

As far as centering on two or more adjacent cells:

with ows1   'where ows1 is the declared worksheet
With .Range("c30:d30")
            .HorizontalAlignment = xlCenter
end with
end with

centers whatever text you put in that cell (those cells)

It's an accounting (currency) value I want to put in the merged cells but it puts the currency symbol to the left of the cell and the amount to the right of the cell. Even selecting the merged cell(s) in Excel and choosing to center doesn't do anything. I suspect it's the format of the cell that is locking the format.

[Bezzie]

How are you using Excel...early or late bound?

Don't really know. Got examples from internet and tried the worksheet creation from my code. Have no experience doing this before.

    Dim oXLApp As Object
    Dim oXLBook As Object
    Dim oXLSheet As Object

    'create an excel object
    Set oXLApp = CreateObject("Excel.Application") 'this should error out if excel is not installed
    
    'open the template file
    Set oXLBook = oXLApp.Workbooks.Add 'create a new file
    
    Do While oXLBook.Sheets.Count < 2 'add worksheets until there are 2
        oXLBook.Sheets.Add 'add a worksheet
    Loop

[SamOscarBrown]

Ah...try this!

Create a new TEST project.

Add Reference to Excel (you know how, right?)

And then use this code...that will border the merged cells in C33

Option Explicit
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oRange As Excel.Range
Dim oSheet(3) As Excel.Worksheet


Private Sub Command1_Click()
        Dim i As Integer
        Set oExcel = New Excel.Application
        Set oExcel = CreateObject("Excel.Application")
        Set oBook = oExcel.Workbooks.Add
        Set oSheet(1) = oBook.Worksheets("Sheet1")
         oSheet(1).Cells(3, 3).Interior.Color = RGB(250, 244, 183) ' light yellow
        oSheet(1).Range("b3", "b3").Font.Size = 12
        With oSheet(1)
            .Cells(1, 3) = "44"
            .Cells(2, 3) = "55"
            .Cells(3, 3) = CStr(CInt(.Cells(1, 3)) + CInt(.Cells(2, 3)))
        End With
        For i = 1 To 3
                oExcel.Cells(i, 3).HorizontalAlignment = xlCenter
        Next i
        'draw borders
        With oSheet(1).Range("c3:d3").Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:d3").Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:c3").Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("d3:d3").Borders(xlRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With oSheet(1).Range("c3:d3")
            .HorizontalAlignment = xlCenter
            .Merge
        End With
        saveExcel
End Sub
Private Sub saveExcel()
        oExcel.Visible = True
        Dim oActiveSheet As Excel.Worksheet, oActiveBook As Excel.Workbook
        Set oActiveSheet = oExcel.ActiveSheet
        oBook.Worksheets(1).Select
        Set oActiveBook = oExcel.ActiveWorkbook
        '      oExcel.DisplayAlerts = False
        On Error GoTo MYERROR
        Const ssfDESKTOP = 0
        Dim DesktopPath As String
        DesktopPath = CreateObject("Shell.Application").Namespace(ssfDESKTOP).Self.Path
        Dim myFilename As String
        myFilename = "testtest.xlsx"
        oActiveBook.SaveAs FileName:=DesktopPath & "\" & myFilename
MYERROR:
        Exit Sub
End Sub

[SamOscarBrown]

It's an accounting (currency) value I want to put in the merged cells but it puts the currency symbol to the left of the cell and the amount to the right of the cell. Even selecting the merged cell(s) in Excel and choosing to center doesn't do anything. I suspect it's the format of the cell that is locking the format.

Sorry...I left out the .Merge in that code snippet....look at my last post...believe it will give you the idea on how to implement in your project...I'm using early binding.

[Bezzie]

I'm going to try it. Thanks.

[bcelestia]

Another option for borders around a range would be to use the BorderAround method

expression.BorderAround (LineStyle, Weight, ColorIndex, Color, ThemeColor)

expression A variable that represents a Range object.

Example: Worksheets("Sheet1").Range("A1:F4").BorderAround ColorIndex:=3, Weight:=xlThick

All Parameters are optional

[SamOscarBrown]

Well, I learned something new today....thx bcelestia! (So much easier than the method I used for bordering.

(And as I used to say when I was in the work force..."I learned a new thing today, hence, I'm going home!) Oh wait, I AM home....never mind! :-)

[bcelestia]

I've tested this - It seems to work OK

Option Explicit

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim I As Variant

Private Sub Form_Load()
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets("Sheet1")
oExcel.Visible = True

I = oSheet.Range("A1:F4").BorderAround(xlContinuous, xlMedium)
End Sub

[SamOscarBrown]

as did i, bc

[Bezzie]

Tried both methods and got both to work.

With the method of SamOscarBrown I noticed that one should only select the specific cells that will be effected by the line i.e. to draw the left line only select that cell and to draw the top and bottom lines all the cells need to be selected (if it's only one row in the range) otherwise only the top row or bottom row needs to be selected etc.

Now I have only one problem remaining. Setting the .NumberFormat of the merged cell.

I need it to be in the local currency of the computer. If I don't set a number format it seems Excel decides the cell number format should be 'Accounting', probably due to the subtotals being summed that is set to Accounting', and that shows the currency symbol to the left of the cell and the amount to the right of the cell. If I then open the worksheet created and manually change the format to 'Currency' it displays the amount in the middle of the cell with the currency symbol and the amount next to each other as I need it to be.

How do I set the .NumberFormat of the merged range to use the computers local 'Currency'?

[SamOscarBrown]

Just play around with it like this (in my example, the "99" will appear as "$99."):

oSheet(1).Range("c3:d3").NumberFormat = "$#.##"

[SamOscarBrown]

or (as in my example.)...make these changes to see decimals:

With oSheet(1).Cells(1, 3) = "22.33"
            .Cells(2, 3) = "55.54"
            .Cells(3, 3) = CStr(CDbl(.Cells(1, 3)) + CDbl(.Cells(2, 3)))
End With

and then:

oSheet(1).Range("c3:d3").BorderAround ColorIndex:=3, Weight:=xlThick
oSheet(1).Range("c3:d3").NumberFormat = "$#,###.##"

(Removing, in my example, all those lines about individual lines making up borders.)

[Bezzie]

Thanks guys. Got it sorted now.