Trying to format a cell with a border

[Stephen Plotnick]

I want to have a border around some cells. I 'm using VB 2005 and Excel
2007. I went into VBA within Excel to trap a macro to assist me.

Here is the code I transferred to VB 2005:

With osheet.Range(MultiLetter & "1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With osheet.Range(MultiLetter & "1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With osheet.Range(MultiLetter & "1").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With osheet.Range(MultiLetter & "1").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

All the "xl" commands are getting not declared errors. For a note I'm using
"xl" commands all over the place without any issues in the program except
here, and these types. For example I'm using xliconsets, xl3arrows, etc
without any problems.

Thanks in advance
Steve

 

[JLGWhiz]

I don't believe the TintAndShade property applies to the Borders object. You
could probably use it with the fill method. Everything else should work ok.

 

[JLGWhiz]

By the way, You could use the BorderAround method and reduce the amount of
code you are writing.

With osheet.Range(MultiLetter & "1").BorderAround
.LineStyle = xlContinuous
.Weight = xlMedium
End With

Does all four sides at once.

 

[Stephen Plotnick]

I guess I do not know what the fill method. My problem is I'm getting a not
declared error. I'm not sure that would go away. Hopefully it will.

Thanks
Steve

 

[Stephen Plotnick]

Thanks for this code; it is very cool.

I did cut and paste into my program and I'm getting the not declared error
on xlContinuous and xlMedium.

Is it possiblt that I'm missing something else?? Like I said I have "xl"
assignments in other places in my program without any problems.

Thanks
Steve

 

[JLGWhiz]

When the error message pops up, did you click the help button on the error
dialog box to see what it says is causing the error. It sounds like one of
your variables needs to be declared if you are using Option Explicit.

 

[Stephen Plotnick]

I'm not getting an error at runtime. THe error is from within the VBE. I'm
copying an entire sub rountine. The xlContinuus, xlMedium, and xlEdgeBottom
give me the not declared error, yet the xl3Arrow is not and works fine in
the final output of the spreadsheet. Everything I've read and trapping a VBA
macro would seem I'm doing everyting ok.

Is there a possibility that something needs to go before the xl statements
that are failing? Such as .Borders(??????.xlEdgeBottom)

Thanks,
Steve

Sub Generate_LastYTD_Sales()


Dim dept_no As Integer
Dim testtext As String
Dim TotalForRow As Double

InputNumber = 1
For dept_no = 0 To 1000
testtext = Mid(line, dept_no * 32 + 1, 32)

If testtext = " " Then
dept_no = 1000
Else
InputNumber = InputNumber + 1
Convert_to_Column_Letter()
osheet.Range(MultiLetter & "4").Value = Mid(line, dept_no *
32 + 1, 32).ToString
osheet.Range(MultiLetter & "4").EntireColumn.AutoFit()
osheet.Range(MultiLetter & "5").Value = Mid(line, dept_no *
32 + 1, 32).ToString
osheet.Range(MultiLetter & "5").EntireColumn.AutoFit()
osheet.Range(MultiLetter & "3:" & MultiLetter &
"4").Select()
TotalForRow = TotalForRow + osheet.Range(MultiLetter &
"4").Value
With osheet.Range(MultiLetter & "5").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

'Create an icon set conditional format for the created
sample data range
cfIconSet = osheet.Range(MultiLetter & "3", MultiLetter &
"4").FormatConditions.AddIconSetCondition

'Change the icon set to a three-arrow icon set
cfIconSet.IconSet = obook.IconSets(XlIconSet.xl3Arrows)

End If
Next dept_no

InputNumber = InputNumber + 1
Convert_to_Column_Letter()
osheet.Range(MultiLetter & "4").Value = TotalForRow
osheet.Range(MultiLetter & "4").NumberFormat = "###,###,###,##0"
osheet.Range(MultiLetter & "5").Value = TotalForRow
osheet.Range(MultiLetter & "5").NumberFormat = "###,###,###,##0"
osheet.Range(MultiLetter & "3:" & MultiLetter & "4").Select()

'Create an icon set conditional format for the created sample data
range
cfIconSet = osheet.Range(MultiLetter & "3", MultiLetter &
"4").FormatConditions.AddIconSetCondition

'Change the icon set to a three-arrow icon set
cfIconSet.IconSet = obook.IconSets(XlIconSet.xl3Arrows)

End Sub

 

[Stephen Plotnick]

I answered my own question. With a little research here is what ended up
working.

With osheet.Range(MultiLetter &
"5").Borders(XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
End With

I hope this helps the next person that runs into this problem.

Steve