Results 1 to 22 of 22

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

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

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

    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:
    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?

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,835

    Re: Placing a border around a range of cells in Excel file from VB6

    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.
    Please remember next time...elections matter!

  3. #3
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,912

    Re: Placing a border around a range of cells in Excel file from VB6

    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.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    Code:
     '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
    Sam I am (as well as Confused at times).

  5. #5
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    As far as the merge...I've got to go see one of my examples
    Sam I am (as well as Confused at times).

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

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

  7. #7
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    How are you using Excel...early or late bound?
    Sam I am (as well as Confused at times).

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    As far as centering on two or more adjacent cells:

    Code:
    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)
    Sam I am (as well as Confused at times).

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

    Quote Originally Posted by SamOscarBrown View Post
    Code:
     '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.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

    Quote Originally Posted by SamOscarBrown View Post
    As far as centering on two or more adjacent cells:

    Code:
    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.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

    Quote Originally Posted by SamOscarBrown View Post
    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.
    Code:
        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

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    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

    Code:
    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
    Sam I am (as well as Confused at times).

  13. #13
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    Quote Originally Posted by Bezzie View Post
    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.
    Sam I am (as well as Confused at times).

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

    I'm going to try it. Thanks.

  15. #15
    New Member
    Join Date
    Dec 2020
    Posts
    5

    Re: Placing a border around a range of cells in Excel file from VB6

    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

  16. #16
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    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! :-)
    Sam I am (as well as Confused at times).

  17. #17
    New Member
    Join Date
    Dec 2020
    Posts
    5

    Re: Placing a border around a range of cells in Excel file from VB6

    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

  18. #18
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

    as did i, bc
    Sam I am (as well as Confused at times).

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

    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'?

  20. #20
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

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

    Code:
    oSheet(1).Range("c3:d3").NumberFormat = "$#.##"
    Sam I am (as well as Confused at times).

  21. #21
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,173

    Re: Placing a border around a range of cells in Excel file from VB6

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

    Code:
    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:

    Code:
    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.)
    Sam I am (as well as Confused at times).

  22. #22

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Placing a border around a range of cells in Excel file from VB6

    Thanks guys. Got it sorted now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width