runtime error 91 trying to set color of cells 1

[vba317]

I am trying to write a module that changes the color of specific cells. I keep on getting this error 91. I have highlighted in blue where the error is. I do have a blank spreadsheet open currently. Any help would be appreciated.

Public Sub xLFmtFillColorRed(intTopRow As Integer, intBotRow As Integer, intLCol As Integer, intRCol As Integer)
' ****************************************************************************************************
' *** THIS SUB FILLS IN THE COLOR RED FOR A RANGE OF CELLS ON ACTIVE OBJECT ***
' ****************************************************************************************************
'Call xLFmtFillColorRed(intTopRow, intBotRow, intLCol, intRCol)
    Dim strLeftLetter As String
    Dim strRightLetter As String
    Dim intRow As Integer
    strLeftLetter = ConvColLet(intLCol)
    strRightLetter = ConvColLet(intRCol)
    For intRow = intTopRow To intBotRow
 [blue]    goXL.ActiveSheet.Range("" & (strLeftLetter) & (intRow) & ":" & (strRightLetter) & (intRow) & "").Select [/blue]
        With Selection.Interior
            .ColorIndex = 4
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Next intRow
End Sub

 

[Andrzejek]

Is that a VBA in Excel?
If so, what is [blue][tt]goXL.[/tt][/blue] ?

And what are the values of: strLeftLetter, strRightLetter, intRow ?

Have fun.

---- Andy

 

[vba317]

Public goXL As Excel.Application ' The Excel Object variable
intTopRow = 3
intBotRow = 5
intLCol = 4
intRCol = 7


Call xLFmtFillColorRed(intTopRow, intBotRow, intLCol, intRCol)
So intLCol is D and intRCol is G

Tom

 

[Andrzejek]

So you call it from Access, that's why you have goXL As Excel.Application
So I assume you set the goXL prior to calling this Sub.

This works for me, the cells do change to Green:

Dim intRow As Integer
Dim strLeftLetter As String
Dim strRightLetter As String

intRow = 7
strLeftLetter = "B"
strRightLetter = "D"

With goXL
  .ActiveSheet.Range(strLeftLetter & intRow & ":" & strRightLetter & intRow).Select
  With .Selection.Interior
    .ColorIndex = 4
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
  End With
End With

Have fun.

---- Andy

 

[vba317]

What do you mean by setting goXL? I am still getting the error so I must not be. How do you set it?

Tom

 

[Andrzejek]

You stated: "I keep on getting this error 91" - do you have any description of this error? What does it say?

You declared:[tt]
Public goXL As Excel.Application [/tt]

What do you do with [tt]goXL[/tt] before you get to the (blue) line of code you error on?

Have fun.

---- Andy

 

[vba317]

The error I am getting is Runtime error 91. Object variable or with block variable not set. Just a thought. This is a newly created database. Is there a reference that might need to be set? I am not doing anything with goXL other that what I have in the code you see.

Tom

 

[vba317]

Andy,
I have found the error of my ways thanks to your question. Under normal circumstances I call a routine called XL open which does this command - Set dBase = CurrentDb. I have added this routine back in and everything works.

Thanks
Tom