VBA works alone, but not as part of a larger macro


Posted by Paul R on August 09, 2001 3:09 AM

Hi,
I have written a small bit of code that formats 4 cells on a sheet in excel 97. When I run it on its own in a sub as follows it works fine:

Sub_formatcells()

Dim chkrw, chkcl As Integer
Dim sht As String

chkrw = 1
chkcl = 1
sht = "Sheet1"

With Worksheets(sht).Range(Cells(chkrw, chkcl), Cells(chkrw, chkcl + 2))
.Font.Bold = True
.Font.Color = RGB(0, 0, 255)
End With

With Worksheets(sht).Range(Cells(chkrw, chkcl + 3), Cells(chkrw, chkcl + 4))
.Font.Bold = True
.HorizontalAlignment = xlRight
End With

End Sub


Now, the problem comes when I take part of this and use it in a larger macro like this:

...
With Worksheets(sht).Range(Cells(chkrw, chkcl), Cells(chkrw, chkcl + 2))
.Font.Bold = True
.Font.Color = RGB(0, 0, 255)
End With

With Worksheets(sht).Range(Cells(chkrw, chkcl + 3), Cells(chkrw, chkcl + 4))
.Font.Bold = True
.HorizontalAlignment = xlRight
End With
...

I have checked that the variables are being correctly set and defined, and that is not the problem, but it still gives me a
"Runtime error 1004.
Application or object-defined error"

Any ideas as to why this might be happening?

Cheers,
Paul.



Posted by Joe Was on August 09, 2001 6:59 AM

Need to see your code! As a few things could:

If the code above your format code is a Selection based code and you use the "With" style coding you may get this error. If the object is open and you introduce a competing object Excel tries to use both but can't and gives you this error. If Excel goes into another object within your macro keeping the last one open you get a runtime error as the first object is not finalized. Without the full code can't tell for sure? JSW