Trying to format a cell with a border
 

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

Trying to format a cell with a border
 

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.

"Stephen Plotnick" wrote:

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

Trying to format a cell with a border
 

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" wrote:

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

Trying to format a cell with a border
 

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
"JLGWhiz" wrote in message
...
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.

"Stephen Plotnick" wrote:

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

Trying to format a cell with a border
 

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" wrote in message
...
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" wrote:

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

Trying to format a cell with a border
 

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" wrote:

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" wrote in message
...
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" wrote:

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

Trying to format a cell with a border
 

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
"JLGWhiz" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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

Trying to format a cell with a border
 

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
"Stephen Plotnick" wrote in message
...
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
"JLGWhiz" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

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