Macro not setting password and also how can I get user to print then carry on with macro.

[ghrek]

Hi

I have started a macro but need help please.

I have set a password on the sheet to protect it and have a macro where I want to unprotect it and then set up area to print.
The user then selects the printer in which to print to and then I need it to re protect.

This is what I have so far. Any Ideas?

 ActiveSheet.Unprotect  ( need it to automatically input password)
    ActiveWindow.SmallScroll Down:=40
    Range("B54:H107").Select

Then need it to print area selected above and user to select printer in which to print to and then carry on
by reprotecting sheet.

Range("N36").Select
    ActiveWindow.SmallScroll Down:=-68
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("8:1048576,E5:XFD7,A5:B7,1:4").Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=0
    Range("23:1048576,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4").Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=28
    Range( _
        "39:1048576,E34:XFD38,A34:C38,23:33,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4"). _
        Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=8
    Range( _
        "48:1048576,I46:XFD47,A46:A47,39:45,E34:XFD38,A34:C38,23:33,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4" _
        ).Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=8
    Range( _
        "57:1048576,I56:XFD56,A56:F56,48:55,I46:XFD47,A46:A47,39:45,E34:XFD38,A34:C38,23:33,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4" _
        ).Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=8
    Range( _
        "72:1048576,D60:XFD71,A60:B71,57:59,I56:XFD56,A56:F56,48:55,I46:XFD47,A46:A47,39:45,E34:XFD38,A34:C38,23:33,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4" _
        ).Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=12
    Range( _
        "89:1048576,D81:XFD88,A81:B88,78:80,I77:XFD77,A77:F77,72:76,D60:XFD71,A60:B71,57:59,I56:XFD56,A56:F56,48:55,I46:XFD47,A46:A47,39:45,E34:XFD38,A34:C38,23:33,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4" _
        ).Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=12
    Range( _
        "102:1048576,D94:XFD101,A94:B101,I93:XFD93,A93:F93,89:92,D81:XFD88,A81:B88,78:80,I77:XFD77,A77:F77,72:76,D60:XFD71,A60:B71,57:59,I56:XFD56,A56:F56,48:55,I46:XFD47,A46:A47,39:45,E34:XFD38,A34:C38,23:33,I11:XFD22,A11:G22,8:10,E5:XFD7,A5:B7,1:4" _
        ).Select
    Range("A1").Activate
    ActiveWindow.SmallScroll Down:=-84
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
Need it to reprotect sheet and input password automatically.
    ActiveWorkbook.Save
End Sub

Any Ideas?

Thanks

 

[hajiali]

try
to unprotect

ActiveSheet.Unprotect Password:="SetPassword"

set the password inside " "

" your print code here"

to protect

ActiveSheet.Protect Password:="SetPassword"

 

[GWteB]

How about ...

Sub ghrek()

    Const cPassword As String = "YourSecretPassword"    ' <<< change password to suit
    
    Dim oWs As Worksheet
    Set oWs = ThisWorkbook.Worksheets("Sheet1")         ' <<< change worksheet name to suit

    With oWs
        .Unprotect Password:=cPassword
        .PageSetup.PrintArea = "$B$54:$H$107"
        Application.Dialogs(xlDialogPrint).Show
        .Cells.Locked = True
        .Cells.FormulaHidden = False
        .Range("C5:D7,H11:H22,D34:D38,B46:H47,G56:H56,C60:C71,G77:H77,C81:C88,G93:H93,C94:C101").Locked = False
        .Protect Password:=cPassword, DrawingObjects:=False, Contents:=True, Scenarios:=False
        oWs.Parent.Save
    End With
End Sub