How to center cell values in vba?

[akika]

Hi,

I have a vba function module in access that output a query to an excel type (97-2003 Worksheet).

Ive tried to center the cell contents from B2 To D10 using:

With sh.Range("B2:D10")
.HorizontalAlignment = xlCenter
End With

but am getting runtime error 1004: Unable to set HorizontalAlignment property of the Range class.
Any idea?

 

[RoryA]

If you don't have a reference set to the Excel library, add:

Const xlCenter as long = -4108

 

[akika]

Const xlCenter as long = -4108

U Mean to say

With sh.Range("B2:D10")
Const xlCenter As Long = -4108
End With

content dont get center

 

[RoryA]

No, I mean:

Const xlCenter as long = -4108
With sh.Range("B2:D10")
.HorizontalAlignment = xlCenter
End With

If you don't have a reference set to Excel, the xlCenter constant has no meaning in your code.

 

[akika]

No, I mean:

Const xlCenter as long = -4108
With sh.Range("B2:D10")
.HorizontalAlignment = xlCenter
End With

If you don't have a reference set to Excel, the xlCenter constant has no meaning in your code.

how u got the -4108 ? is it readily available with other codes?
which reference to use to make the font as white color within this range?

 

[anand3dinesh]

Try This

Sub test()

Dim sh As Worksheet
Set sh = ActiveSheet


With sh.Range("B2:D10")
.HorizontalAlignment = xlCenter
End With

End Sub

Hi,

I have a vba function module in access that output a query to an excel type (97-2003 Worksheet).

Ive tried to center the cell contents from B2 To D10 using:

With sh.Range("B2:D10")
.HorizontalAlignment = xlCenter
End With

but am getting runtime error 1004: Unable to set HorizontalAlignment property of the Range class.
Any idea?

 

[RoryA]

how u got the -4108

Using the object browser in Excel.

You can set the font using an RGB value like: .Font.Color = RGB(255,255,255)