Underline a cell with VBA

[Hercule22]

I have been trying for hours to underline a cell (last line)with no success. I am always getting an error. what do I do wrong....

With objExcel
.sheets("Tableau de bord").Range("a1").Offset(2, 6).FormulaLocal = "=SOMME(données!G" & SCum & ":G" & ECum & ")"
.sheets("Tableau de bord").Range("a1").Offset(2, 7).FormulaLocal = "=SOMME(données!G2:G24)"
.sheets("Tableau de bord").Range("a1").Offset(2, 6).Interior.ColorIndex = 5
.sheets("Tableau de bord").Range("a1").Offset(2, 7).Borders(xlEdgeBottom).LineStyle = xlContinuous


End With

 

[macropod]

Hi Hercule,

Provided 'objExcel' is properly defined as a workbook, this works for me:

With objExcel
  With .Sheets("Tableau de bord").Range("a1")
    .Offset(2, 6).FormulaLocal = "=SOMME(données!G" & SCum & ":G" & ECum & ")"
    .Offset(2, 7).FormulaLocal = "=SOMME(données!G2:G24)"
    .Offset(2, 6).Interior.ColorIndex = 5
    .Offset(2, 7).Borders(xlEdgeBottom).LineStyle = xlContinuous
  End With
End With

Cheers
Paul Edstein
[MS MVP - Word]

 

[Hercule22]

Thanks for the answer.

ObjExcel is defined as follow
Set objExcel = CreateObject("Excel.application")

My question is why is it working with the other instruction and not with the underline instruction?

 

[SkipVought]

What application are you programming in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

UNDERLINE is a FONT object property.

You are working with a CELL BORDER property.

So which is it?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hercule22]

I am running this code from Access.

and I working with Cell Border property.

 

[SkipVought]

I would go even further...

With objExcel
  With .Sheets("Tableau de bord").Range("a1")
    With .Offset(2, 6)
        .FormulaLocal = "=SOMME(données!G" & SCum & ":G" & ECum & ")"
        .Offset(2, 6).Interior.ColorIndex = 5
    End With
    With .Offset(2, 7)
        .FormulaLocal = "=SOMME(données!G2:G24)"
        .Offset(2, 7).Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
  End With
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Provided 'objExcel' is properly defined as a workbook

ObjExcel is defined as follow
Set objExcel = CreateObject("Excel.application")

In that case, ObjExcel is not a workbook - it's an application.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Hercule22]

I realize that now, but how do I reference a workbook. I have been trying...again without success. Bujt he funny thing is that the line befoire that were I insert a formula in a cell works.

 

[SkipVought]

How did you either OPEN your workbook or ADD a workbook in your code?

CODE, CODE, CODE. We can't help you without your posting your CODE!

If you had posted your complete code to begin with, you would have had your answer by now!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hercule22]

there is a simplyfied verison of my code.

Sub Module4()
' code sample
Dim ExcelApp As Object
Set objExcel = CreateObject("Excel.application")

'open file
With objExcel
.Workbooks.Open ("D:\105.xls")
.sheets("Tableau de bord").Activate
.Range("a1").Offset(2, 6).FormulaLocal = "=SOMME(données!G2:G25)"
.Range("a1").Offset(2, 6).Borders(xlEdgeBottom).LineStyle = xlDoubleInterior

End With

' save file

objExcel.ActiveWorkbook.Save


objExcel.Application.Quit

End Sub

 

[SkipVought]

With objExcel.Workbooks.Open ("D:\105.xls")

    with .sheets("Tableau de bord")

       .Range("a1").Offset(2, 6).FormulaLocal = "=SOMME(données!G2:G25)"
       .Range("a1").Offset(2, 6).Borders(xlEdgeBottom).LineStyle = xlDoubleInterior
    end with

' save file
            
     .Save
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hercule22]

I tried your version and it still does not work. I just realized that I am running a french version of Excel, can that have anything to do with my problem.

"This programmimng is a lot more complicated that RPG and Cobol....

 

[SkipVought]

[quote...and it still does not work[/quote]

meaning WHAT, exactly?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Hercule,

You need a reference to the workbook before you can work with it.

Try:

Sub Module4()
' code sample
Dim objExcel As Object
Dim ExcleWb As Object
Set objExcel = CreateObject("Excel.application")
'open file
Set ExcleWb = objExcel.Workbooks.Open("D:\105.xls")
With ExcleWb.Sheets("Tableau de bord")
  .Range("a1").Offset(2, 6).FormulaLocal = "=SOMME(données!G2:G25)"
  .Range("a1").Offset(2, 6).Borders(xlEdgeBottom).LineStyle = xlDoubleInterior
  ' save file
  .Save
End With
objExcel.Application.Quit
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[Hercule22]

I tried your last suggestion and it still does not work. the error msg I am getting is
Error number: 1004
Description : Error definied by the application or the Object

Note that my Excel is in French so I am translating the error description

 

[SkipVought]

Please be clear, concise and complete.

On exactly what statement did the error occur?

Please post, AGAIN, the relevent code in context, that defines the manner in which object have been set and variables have been assigned.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Hercule,

I am unable to generate a '1004' error with the code I posted unless I have a fairly obvious error:
• the file/filepath does not exist. But even then, it's a completely different error message; or
• the offset points to an invalid range. This will cause an 'Application defined or Object defined' error.
So, whatever error you're getting, is because you are not using the code I posted - it's because you're doing something else.

Also, with the code you posted, I don't understand why you're bothering with Offsets, since you could address the target ranges directly.

One thing I have noticed is that the 'xlDoubleInterior' property causes 'Variable not defined' errors. There is no such LineStyle property in Excel 2007 & earlier.

Cheers
Paul Edstein
[MS MVP - Word]

 

[combo]

Make excel wisible for debugging:
[tt]objExcel.Visible = True[/tt].
Do you have a reference to Excel library set? If no, all excel named constants are default variants, they should be replaced in code by their values, for instance xlEdgeBottom ==> 9.


combo

 

[Hercule22]

Now it is working I tried what Combo suggested and it works. See code

thank you for your help.

Sub Module4()
' code sample
Dim objExcel As Object
Dim ExcleWb As Object
Set objExcel = CreateObject("Excel.application")
'open file
objExcel.Visible = True
xlEdgeBottom = 9
xlDoubleInterior = -4119

Set ExcleWb = objExcel.Workbooks.Open("D:\105.xls")
With ExcleWb.Sheets("Tableau de bord")
.Range("a1").Offset(2, 6).FormulaLocal = "=SOMME(données!G2:G25)"
.Range("a1").Offset(2, 2).Borders(xlEdgeBottom).LineStyle = xlDoubleInterior
' save file
.Save
End With
objExcel.Application.Quit
End Sub