Help with a button on a spreadsheet

Posted by Anita on February 12, 2002 1:18 PM

I have a command button that is on a "protected/shared" workbook - this shared workbook has a password assigned to it.

I'm trying to define a print area that is not consecutive via a macro so I've defined the code as follows:

Private Sub CommandButton1_Click()
Call PrintWorksheet
End Sub

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

'
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=5
Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("U:U").Select
Selection.EntireColumn.Hidden = True
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
End Sub

When I attempt to execute this button from my workbook, I get the message: "Unable to set the Hidden Property of the Range class".

I think this may have something to do with the fact that the workbook is protected and shared but I'm not quite sure.

Any ideas anyone?

Thanks in advance!
Anita

Posted by Jim on February 12, 2002 1:32 PM

Hi Anita,

Try: Private Sub Workbook_BeforePrint(Cancel As Boolean)

Jim PrintWorksheet Macro Macro recorded 2/12/2002 by Anita Grimes

Posted by Andrew Glasfeld on February 12, 2002 1:44 PM

Before you can make any formatting changes, the workbook/worksheet needs to be unprotected. Apply the Unprotect method to the Workbook object (with the optional password argument if there is one - I've used the very cryptic "password" as an example):
ActiveWorkbook.Unprotect "password"

When you're done, use:
ActiveWorkbook.Protect "password"

There are a few other arguments to consider with the Protect method, so you might want to take a look at the VBA help files to see if you need to mess with those.

One of those vexing things that nobody tells you in any of the books...

Posted by Anita on February 12, 2002 1:46 PM

Hi Jim,

Unfortunately, I'm not too great with VB code - where should I put this text? How does this 'private sub workbook_before print' know that my workbook is shared and protected?

I'm trying to alleviate any users from changing anything in the workbook and I need to have the ability to have multiple users access the workbook at the same time.

Thanks! PrintWorksheet Macro Macro recorded 2/12/2002 by Anita Grimes : '

Posted by Anita on February 12, 2002 2:00 PM

Andrew - I added in the code (using UnprotectSharing not just Unprotect) but now I get: Method "UnprotectSharing" of object '_Workbook' failed"

Here is my revised code:

Private Sub CommandButton1_Click()
ActiveWorkbook.UnprotectSharing "bp02acg"
Call PrintWorksheet
ActiveWorkbook.ProtectSharing "bp02acg"
End Sub

Print Worksheet call from above is the same as below.

Thanks again!
Anita Before you can make any formatting changes, the workbook/worksheet needs to be unprotected. Apply the Unprotect method to the Workbook object (with the optional password argument if there is one - I've used the very cryptic "password" as an example):

Posted by Andrew on February 13, 2002 6:27 AM

Oh yeah - you have to take the focus off the control (button) before you can execute most advanced tasks in Excel (try giving the focus to a control and see how limited the menus become).

I usually just use "Range("A1").Activate" as the first line of a _Click() event procedure to make sure all objects are available to the procedure.

It's funny - this sounds alot like a project I'd done a while back where these two same problems stopped me for a couple of days...