Macro has 2 problems

[ACG]

I have the following macro for which I am getting two different problems.

The first problem is "Run-time error 1004: Unable to set the Hidden Property of the Range class". Here is my code. My workbook is shared and protected:

Private Sub CommandButton1_Click()
[A1].Activate
Call UnprotectSharing
Call PrintWorksheet
Call ProtectSharing
End Sub

Sub UnprotectSharing()
Dim Password As String
Password = "bp02acg"
ActiveWorkbook.UnprotectSharing Password
End Sub

Sub ProtectSharing()
Dim Password As String
Password = "bp02acg"
ActiveWorkbook.ProtectSharing Password
End Sub

Sub PrintWorksheet()
'
' PrintWorksheet Macro
' Macro recorded 2/18/2002 by Anita Grimes
'

'
[A3].Activate
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
Columns("F:G").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=5
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("U:U").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.ScrollColumn = 1
Range("C1:AB51").Select
ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Range("C1").Activate
Selection.EntireColumn.Hidden = False
ActiveWindow.SmallScroll ToRight:=10
Columns("V:V").Select
Selection.EntireColumn.Hidden = True
End Sub

My other problem is when the workbook goes through the "Protect Sharing" function it re-saves the workbook as "bp02acg.xls" as the filename instead of just saving over the existing file.

Please help, this is a verycritical piece of my project that is due at 5 pm today.

Thank you everyone!!!

Anita

[Barrie Davidson]

Try changing your code to this:

Public CurrentFilename As String
Private Sub CommandButton1_Click()
[A1].Activate
Call UnprotectSharing
Call PrintWorksheet
Call ProtectSharing
End Sub

Sub UnprotectSharing()
Dim Password As String
Password = "bp02acg"
ActiveWorkbook.UnprotectSharing Password
End Sub

Sub ProtectSharing()
Dim Password As String
Password = "bp02acg"
ActiveWorkbook.ProtectSharing Filename:=CurrentFilename, Password:=Password
End Sub

Sub PrintWorksheet()
'
' PrintWorksheet Macro
' Macro recorded 2/18/2002 by Anita Grimes
'

'

On Error Resume Next
CurrentFilename = ActiveWorkbook.FullName
[A3].Activate
Columns("A:B").EntireColumn.Hidden = True
Columns("F:G").EntireColumn.Hidden = True
Columns("R:R").EntireColumn.Hidden = True
Columns("U:U").EntireColumn.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Range("C1").EntireColumn.Hidden = False
Columns("V:V").EntireColumn.Hidden = True
End Sub

Hope this is what you need

[ACG]

This appears to have worked - thanks so much for your expertise!

[ACG]

I was able to get this to work in order to print the worksheet; however, when you click on the button for PrintWorksheet it brings you up a pop-up that says "This will remove the workbook from shared use" that works fine if you say OK or Yes; however, if you hit cancel, you get a runtime error. I will have multiple users using this workbook, I would like for it NOT to create a runtime error - is there any VB code I could put in to handle if someone hits cancel to just revert back to the spreadsheet and NOT attempt to Unprotect the document?

Thanks!
Anita

[Barrie Davidson]

On 2002-02-18 11:12, ACG wrote:
I was able to get this to work in order to print the worksheet; however, when you click on the button for PrintWorksheet it brings you up a pop-up that says "This will remove the workbook from shared use" that works fine if you say OK or Yes; however, if you hit cancel, you get a runtime error. I will have multiple users using this workbook, I would like for it NOT to create a runtime error - is there any VB code I could put in to handle if someone hits cancel to just revert back to the spreadsheet and NOT attempt to Unprotect the document?

Thanks!
Anita

Do you want to disable this message box. If yes, change your code to read:

Public CurrentFilename As String
Private Sub CommandButton1_Click()
[A1].Activate
Application.DisplayAlerts = False
Call UnprotectSharing
Call PrintWorksheet
Call ProtectSharing
Application.DisplayAlerts = True
End Sub

Sub UnprotectSharing()
Dim Password As String
Password = "bp02acg"
ActiveWorkbook.UnprotectSharing Password
End Sub

Sub ProtectSharing()
Dim Password As String
Password = "bp02acg"
ActiveWorkbook.ProtectSharing Filename:=CurrentFilename, Password:=Password
End Sub

Sub PrintWorksheet()
'
' PrintWorksheet Macro
' Macro recorded 2/18/2002 by Anita Grimes
'

'

On Error Resume Next
CurrentFilename = ActiveWorkbook.FullName
[A3].Activate
Columns("A:B").EntireColumn.Hidden = True
Columns("F:G").EntireColumn.Hidden = True
Columns("R:R").EntireColumn.Hidden = True
Columns("U:U").EntireColumn.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Cells.Select
Range("C1").EntireColumn.Hidden = False
Columns("V:V").EntireColumn.Hidden = True
End Sub