Cannot set HorizontalAlignment Property of the Range Class

[Alan]

Hi All,

I may be having a dumb day here, but....

I am getting the following error:

"Cannot set HorizontalAlignment Property of the Range Class" from this
code:

Sub Test()

With Range("G4")

.Formula = "GP%"
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True

End With

End Sub


It makes no difference whether I use 'xlHAlignCenter' or -4108 (the
intrinsic value).

G4 is not protected.


Any help is much appreciated.

Thanks,

Alan.

 

[Dave Peterson]

But is the worksheet protected?

Hi All,

I may be having a dumb day here, but....

I am getting the following error:

"Cannot set HorizontalAlignment Property of the Range Class" from this
code:

Sub Test()

With Range("G4")

.Formula = "GP%"
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True

End With

End Sub

It makes no difference whether I use 'xlHAlignCenter' or -4108 (the
intrinsic value).

G4 is not protected.

Any help is much appreciated.

Thanks,

Alan.

 

[Alan]

But is the worksheet protected?

Hi Dave,

Thank you for replying.

No - the worksheet is definately not protected either.

In addition, the other commands (up to the point where it bombs out)
work fine:

For example:

Sub Test()

With Range("G4")

.Formula = "GP%"
.Font.Bold = True
.HorizontalAlignment = xlHAlignCenter


End With

End Sub


G4 is set to "GP%" okay, and the bold is set, but it then fails.

Not sure if it is relavent, but the workbook is created
programmatically, in a separate instance of excel from the originating
workbook.

Interestingly, the code works fine on another PC (both Excel 2000).
Perhaps I should do a detect and repair on office on my machine?

Thanks,

Alan.

 

[Tom Ogilvy]

Try
Sub Test()

With Range("G4")

.Formula = "GP%"
.Font.Bold = True
msgbox xlHAlignCenter
.HorizontalAlignment = xlHAlignCenter


End With

End Sub

Does it show -4108 ?

 

[Alan]

Try
Sub Test()

With Range("G4")

.Formula = "GP%"
.Font.Bold = True
msgbox xlHAlignCenter
.HorizontalAlignment = xlHAlignCenter


End With

End Sub

Does it show -4108 ?

Hi Tom,

Yep - it shows negative 4108.

I already tried using that intrinsic value just in case, but it
doesn't work either way.

It *does* work on another machine with same install of Excel 2000
(SP3) so I am thinking I should do a Detect & Repair on this machine.

If I do that, will I lose my toolbars, add-ins, personal.xls settings?
I have backups of them all, but it is just a time factor of whether to
do it now, or leave it till Xmas when I will be under less pressure.

Thanks for your help,

Alan.

 

[Dave Peterson]

I don't think a detect and repair will hurt any of your settings--but it can't
hurt making a backup.

But before you do...

Can you center this cell manually?

And I've never seen this type of error with a missing reference, but it
shouldn't take too long to check.

Inside the VBE with your project selected.
tools|References
scroll down that list and look for MISSING

If you see one, uncheck it and see if that helps.

When bad things happen that seem to make no sense, sometimes (not always)
running Rob Bovey's code cleaner can magically fix things:

Rob Bovey's codecleaner can be found here:
http://www.appspro.com/

But if the same workbook opened on another pc works, I wouldn't get my hopes up.

 

[Alan]

I don't think a detect and repair will hurt any of your settings--but
it can't hurt making a backup.

But before you do...

Can you center this cell manually?

And I've never seen this type of error with a missing reference, but
it shouldn't take too long to check.

Inside the VBE with your project selected.
tools|References
scroll down that list and look for MISSING

If you see one, uncheck it and see if that helps.

When bad things happen that seem to make no sense, sometimes (not
always) running Rob Bovey's code cleaner can magically fix things:

Rob Bovey's codecleaner can be found here:
http://www.appspro.com/

But if the same workbook opened on another pc works, I wouldn't get
my hopes up.

Hi Dave,

It centre's no problem manually.

I think I'll just ghost my machine overnight and do the D&R tomorrow -
no real risk that way.

There is nothing 'missing' in the list of references, so I really
think it is some type of corruption.

I'll post back tomorrow after the D&R.

Thanks,

Alan.

 

[Dave Peterson]

Don't forget to try Rob Bovey's code cleaner.

Hi Dave,

It centre's no problem manually.

I think I'll just ghost my machine overnight and do the D&R tomorrow -
no real risk that way.

There is nothing 'missing' in the list of references, so I really
think it is some type of corruption.

I'll post back tomorrow after the D&R.

Thanks,

Alan.

 

[Alan]

Don't forget to try Rob Bovey's code cleaner.

Hi Dave,

I just tried that, but to no avail. Given that the other machine runs
the code fine, that probably makes sense.

However, thank you for pointing me in that direction - it appears to
be a really useful utility that I will use often from now on.

I am going to ghost now, and then D&R in the morning to see if that
helps.

Thank you for your assistance - I really appreciate it.

Alan.

 

[Alan]

I am going to ghost now, and then D&R in the morning to see if that
helps.

That fixed it - I can set the property no problem now.

{Shrug}

Thank you for your assistance with this - very much appreciated.

Regards,

Alan.

 

[Dave Peterson]

Glad you got it fixed--no matter how drastic it was.