excel vba - problems protecting sheet if changes made

C

chief

Here is what happens:

1. user opens program
2. userform pops up asking retail, trade, or account
3. depending on which is chosen, certain cells/formats get reformatte
specific ways
4. user clicks on command button for clearing the page, causes certain
cells to become unprotected and then protects the sheet
5. user goes through and inputs info into non protected cells and the
clicks on save/print command button

This is where the problem is arising, I don't know where and when
should put codes to disable the protection in order for certaing cell
to change. For example, I have a code set up so that if the user ha
originally clicked account, then when they save/print it will print
copies saying yard slip, then reformat some cells, switch yard to pac
slip and print 1 copy. The biggest problem is that I want certain
cells to be protected from manual input because there are improtan
formulas in the cell.
Thanks

My code is too long to post this thread, so if you want to help me tr
to solve it, i can send the file via email
 
C

chief

i was able to solve a command button dilemma involving th
protect/unprotect, however when i try to use my command button t
save/print, an error pops up saying "Unable to set the Linestyl
property of the Border class"

This is the codes i have for that save/print cb. I am trying t
unprotect it so that changes can be made before and after pritning.

Private Sub CommandButton1_Click()


Activeworkbook.Unprotect
If Range("H8").Value = "On Account Customer" Then
Range("I22:L40,K41:L47").Select
Selection.Font.ColorIndex = 2
Range("J5:L5").Select
Selection.Font.ColorIndex = 2
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
Range("I1").Value = "PACK SLIP"
Sheet1.Range("A1:M56").PrintOut Copies:=2
Range("I1").Value = "YARD SLIP"
Sheet1.Range("A1:M56").PrintOut Copies:=1
End If
If Range("H8").Value = "" Then
Range("I22:L40,K41:L47").Select
Selection.Font.ColorIndex = 2
Range("B17:L19").Select
Selection.Font.ColorIndex = 2
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 2
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 2
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 2
End With
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton1").Visible = False
End If
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton2").Visible = False
End If
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton3").Visible = False
End If
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton4").Visible = False
End If
Range("J5:L5").Select
Selection.Font.ColorIndex = 2
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
Sheet1.Range("A1:M56").PrintOut Copies:=1
Range("B17:L19").Select
Selection.Font.ColorIndex = 0
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 15
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton1").Visible = True
End If
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton2").Visible = True
End If
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton3").Visible = True
End If
If Range("H8").Value = "" Then
ActiveSheet.Shapes("OptionButton4").Visible = True
End If
Range("J5:L5").Select
Selection.Font.ColorIndex = 0
Range("I22:L40,K41:L47").Select
Selection.Font.ColorIndex = 0
Range("J5").Select
Selection.Font.Underline = xlUnderlineStyleSingle
Range("K5:L5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 15
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("I1:L1").Select
Selection.Font.ColorIndex = 2
Range("I1:J1").Select
Selection.Font.Underline = xlUnderlineStyleNone
Range("K1:L1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 2
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Sheet1.Range("A1:M56").PrintOut Copies:=1
End If
ActiveWorkbook.Save
Sheet1.SaveAs FileName:="Q:\private invoices\" & Range("K5").Value
ActiveWorkbook.SaveCopyAs FileName:="Q:\RETAIL SALES POs MAGGIE\" &
Range("K5").Value
ActiveWorkbook.Close

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseMode <> 1 Then Cancel = 1

End Sub

Any ideas
 
Top