Excel range selection problem

[idono]

Hello all,

I have about lost all my hair! I'm trying to format cells in excel from vb. All was going well until I tried to add a border to a selection.

First line works! It selects the row as expected. The other lines I got out of an excel macro(don't work). I get an error message 91bject variable or with block variable not set. The problem doesn't stem from no with statement on the first two lines. You take those out and it still doesn't work.

Any suggestions?


objNewXLBook.ActiveSheet.Range("A" & X + 1 & ":" & "F" & X + 1).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlDiagonalUp)
.LineStyle = xlNone
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[TTThio]

Hi,

I tried to write just:

ActiveSheet.Range("A" & X + 1 & ":" & "F" & X + 1).Select
and the rest

It does work fine.

So, I guess the problem is the var 'objNewXLBook' has not been declared/set yet.

Tin Tin

 

[idono]

Thanks for the reply TTThio.
I think the var is set and declared. Here is the code.

Dim objNewXLBook As Excel.Workbook

Set objNewXLBook = GetObject(XLS_FMC_Location & "Flowline Backlog.xls&quot

This sets it and it will open. Answer me this, is the Selection variable set with the .Select call? Or should I dimension a variable for it.

Thoroughly confused

 

[idono]

TTThio,
One thing I forgot to mention was that I am formatting from an Access module. My original message implies that I am using an Excel module within Excel. This is an application to send a query to an Excel file from Access.

 

[TTThio]

Let me experiment with it. I'll reply to you later on.
About 'Selection', you don't need to set a variable for it.
It's a built-in.

 

[jebry]

Hi!

Try it this way:

With objNewXLBook.ActiveSheet.Range("A" & X + 1 & ":" & "F" & X + 1)

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStlye = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
etc.

One note, any place where you have LineStyle = xlNone can be left out, since that is the default.

hth

Jeff Bridgham
bridgham@purdue.edu

 

[idono]

jebry,
I would have never thought of it that way, but it works. Thanks a mill for the advice.

Dugger

 

[SkipVought]

Hey guys...

Range definitions work ALOT better in code with the Cells(row, col) format so that

Range("A" & X + 1 & ":" & "F" & X + 1)

becomes

Range(Cells(X + 1, 1), Cells(X + 1, 6))

Skip,
metzgsk@voughtaircraft.com