Syntax to center an Excel Column Text using VB5?

Question

I am using VB5 to write an Excel spreadsheet. It all works, except I can't get the syntax to get the columns to center the text.

Like for the font:

with..
.selection.font="arial"
end with

There has to be verbage to center the text. For example, how would I set range "A:D" to center the text in the column? Thanks.

Answer 1

You need to set the Horizontal align property of the range object. To do this you need to DIM a variable that will expose the Horizontal Alignment Enumerated settings . With DIM horz as Dim horz As Excel.XlHAlign.
first assign on of the enumerated values to the HORZ variable then assign the HORZ variable to the HorizontalAlignment property of teh range object

The following code placed in a VB2005 form1 load event. Remember you will need to reference the MS excell object


Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rng As Excel.Range
Dim horz As Excel.XlHAlign

xl.Application.Visible = True
wb = xl.Workbooks.Add
sht = wb.Sheets.Item(1)
sht.Name = "TEST"
rng = sht.Cells(1, 1)
rng.Value = "My Text"
horz = Excel.XlHAlign.xlHAlignCenter
rng.HorizontalAlignment = horz