| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude sheets from code
I have a bit of code thats automatically sets borders for new sheets.
When the code runs, it changes the borders on all sheets in the workbook. I would like to have two sheets excluded from this code. The sheet names are "All" and "Tally" Here is my code: Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets With sht.Range("A1:An53") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With With sht.Range("A1:An1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With Next sht Sheets("All").Activate End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exclude sheets from code
You can use if then but I prefer select case for tis kind of thing...
Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets select case sht.name case "All", "Tally" case else With sht.Range("A1:An53") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With With sht.Range("A1:An1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With end select Next sht Sheets("All").Activate End Sub -- HTH... Jim Thomlinson "Homer" wrote: I have a bit of code thats automatically sets borders for new sheets. When the code runs, it changes the borders on all sheets in the workbook. I would like to have two sheets excluded from this code. The sheet names are "All" and "Tally" Here is my code: Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets With sht.Range("A1:An53") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With With sht.Range("A1:An1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With Next sht Sheets("All").Activate End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exclude sheets from code
try this:
Option Explicit Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name = "All" Or sht.Name = "Tally" Then 'do nothing Else With sht.Range("A1:An53") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With With sht.Range("A1:An1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With End If Next sht Sheets("All").Activate End Sub worked for me. :) susan On Aug 7, 12:13*pm, Homer wrote: I have a bit of code thats automatically sets borders for new sheets. * When the code runs, it changes the borders on all sheets in the workbook. *I would like to have two sheets excluded from this code. * The sheet names are "All" and "Tally" Here is my code: Sub Set_Borders() * * Dim sht As Worksheet * * For Each sht In ActiveWorkbook.Sheets * * * * With sht.Range("A1:An53") * * * * .Borders(xlDiagonalDown).LineStyle = xlNone * * * * .Borders(xlDiagonalUp).LineStyle = xlNone * * * * .Borders.LineStyle = xlContinuous * * * * .Borders.Weight = xlMedium * * * * .Borders.ColorIndex = xlAutomatic * * * * .Borders(xlInsideVertical).Weight = xlHairline * * * * .Borders(xlInsideHorizontal).Weight = xlHairline * * * * End With * * * * With sht.Range("A1:An1") * * * * .Borders(xlDiagonalDown).LineStyle = xlNone * * * * .Borders(xlDiagonalUp).LineStyle = xlNone * * * * .Borders.LineStyle = xlContinuous * * * * .Borders.Weight = xlMedium * * * * .Borders.ColorIndex = xlAutomatic * * * * .Borders(xlEdgeBottom).Weight = xlThin * * * * .Borders(xlInsideVertical).Weight = xlHairline * * * * .Borders(xlInsideHorizontal).Weight = xlHairline * * * * End With * * * * Next sht * * Sheets("All").Activate End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Exclude sheets from code
Thanks Jim. Code now works as needed.
Don "Jim Thomlinson" wrote: You can use if then but I prefer select case for tis kind of thing... Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets select case sht.name case "All", "Tally" case else With sht.Range("A1:An53") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With With sht.Range("A1:An1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With end select Next sht Sheets("All").Activate End Sub -- HTH... Jim Thomlinson "Homer" wrote: I have a bit of code thats automatically sets borders for new sheets. When the code runs, it changes the borders on all sheets in the workbook. I would like to have two sheets excluded from this code. The sheet names are "All" and "Tally" Here is my code: Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets With sht.Range("A1:An53") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With With sht.Range("A1:An1") .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders.LineStyle = xlContinuous .Borders.Weight = xlMedium .Borders.ColorIndex = xlAutomatic .Borders(xlEdgeBottom).Weight = xlThin .Borders(xlInsideVertical).Weight = xlHairline .Borders(xlInsideHorizontal).Weight = xlHairline End With Next sht Sheets("All").Activate End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Exclude hidden sheets from formatting | Excel Programming | |||
| Exclude HTML code | Excel Discussion (Misc queries) | |||
| Step through sheets, but exclude one | Excel Programming | |||
| Exclude Sheets from Macro | Excel Programming | |||
| Edit code - exclude sheets | Excel Programming | |||
Linear Mode
