Excel Border Formatting 2

[flynbye]

Hookay... Skip had posted this section of code in the past and frankly it does pretty much everything I need it to do but strangely enough I'm getting an undeclared on the linestyle formatting (ie xlThin, xlContinuous). I am importing the microsoft.office.interop so not sure why I'm getting the error. Suggestions?

Sub UndTot2()
Set rTotal = Range("A:A").Find(What:="Grand Total", _
After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rTotal Is Nothing Then
With Range(rTotal, rTotal.Offset(0, 4))
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
End With
End With
End If
End Sub


I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"

 

[SkipVought]

When in doubt...

turn on your macro recorder and record doing exactly what you want.

then clean up the code.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[flynbye]

Well for crying out loud... I've programmed around Excel for years and I've never even thought of that Skip! lol...

Time for me to go over to the wall and bash my head some...

Thank you... AND... here's ur star
CF

I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"

 

[SkipVought]

objects, properties and methods that I rarely use...

either hit the record button, or read a help file.

I guess I'm lazy!

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[flynbye]

Ok back on this topic after I thought we were completely done. Just to be clear I'm using vb.NET and while I've seen repeated postings aobut Excel border changes etc. including the helpful macro listings when I port this code over to the .NET side what I'm getting is an undeclared variable error message on the xl declarations. For instance in the following code I will get errors on the xlContinuous and the xlThin variables. Suggestions?

Range = CurrSheet.Range("A1:A1")
With Range
.Borders(XlBordersIndex.xlEdgeBottom).LineStyle = xlContinuous
.Borders(XlBordersIndex.xlEdgeBottom).Weight = xlThin
End With



I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"

 

[PHV]

Have you tried this ?
.Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlBorderWeight.xlContinuous

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

you will not be able to use the VBA constants.

xlContinuous is 1

xlThin is 2

With [a1]
  .Borders(XlBordersIndex.xlEdgeBottom).LineStyle = 1
  .Borders(XlBordersIndex.xlEdgeBottom).Weight = 2
End With

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[PHV]

you will not be able to use the VBA constants

I thought that xlContinuous and xlThin weren't VBA constants but constants defined in the Excel object model (the Excel.XlBorderWeight Enum).

 

[flynbye]

Thanks PHV... basically I don't get a reference to the constants so while I know those exist they aren't readily available. Sooo... once again Skip appreciate the response and that was the direction that I ended up going. Just annoyed that I have all those fancy constants and I'm back to setting things "manually" lol... ah well, "Thank You!" guys and back to work with me



I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"

 

[flynbye]

Spoke too soon... here is what WILL work with constants... (more hoops to jump through)

Range = CurrSheet.Range("A1:A3")
With Range
.Borders(XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
.Borders(XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlHairline
End With


I always makes things much harder than they should be... that way later I can slap myself in the forhead after spending hours and hours on two lines of code and say to myself "DUH!"