protect workbook and also can visible true/false

[mango]

dear all, any workaround to have both work? i really need this work. if
cannot any suggestion? pls help


Private Sub Workbook_Open()

ActiveWorkbook.Protect Structure:=True, Windows:=False

Sheets(2).Visible = True

Sheet2.CommandButtonDESB.Visible = False
Sheet2.CommandButtonKNDP.Visible = False
Sheet2.CommandButtonKNMG.Visible = False
Sheet2.CommandButtonKNMI.Visible = False
Sheet2.CommandButtonKNOG.Visible = False
Sheet2.CommandButtonKNPS.Visible = False
Sheet2.CommandButtonIMBI.Visible = False
Sheet2.CommandButtonIMTR.Visible = False

Sheet2.Label1.Visible = False
Sheet2.Label2.Visible = False
Sheet2.Label3.Visible = False
Sheet2.Label4.Visible = False
Sheet2.Label5.Visible = False
Sheet2.Label6.Visible = False
Sheet2.Label7.Visible = False
Sheet2.Label8.Visible = False

Sheets(1).Visible = False
Sheets(3).Visible = False
Sheets(4).Visible = False
Sheets(5).Visible = False
Sheets(6).Visible = False
Sheets(7).Visible = False
Sheets(8).Visible = False
Sheets(9).Visible = False


Sheet2.CommandButton1.Visible = True
Sheet2.Label9.Visible = True


ThisWorkbook.Saved = True

End Sub

 

[hgrove]

mango wrote...

any workaround to have both work? i really need this work. if canno any
suggestion? pls help

Private Sub Workbook_Open()

ActiveWorkbook.Protect Structure:=True, Windows:=False

Sheets(2).Visible = True

Sheet2.CommandButtonDESB.Visible = False ...
Sheet2.Label1.Visible = False ...
Sheets(1).Visible = False ...
Sheet2.CommandButton1.Visible = True
Sheet2.Label9.Visible = True

ThisWorkbook.Saved = True

End Sub

Don't protect the workbook untill *AFTER* you make the other changes
Add 3 statements at the beginning,

On Error Goto Cleanup
Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False

and add 3 statements just before the end,

Cleanup:
Application.EnableCancelKey = xlInterrupt
Application.ScreenUpdating = Tru

 

[mango]

Hi hgrove,
sorry i do not really get you.
you asked me to do as follow? nothing happens. no protection. infact i just
1 2 be able to hide and show freely with protection(no add/delete/resequence
of sheet only). user still can edit on the sheet.
besides, what is xl like xldisabled? symbol :- and also R1C1(row 1 column 1)
in the macro?

thanks alot


Private Sub Workbook_Open()
'Dim a As Integer
'For a = 2 To Sheets.Count
'Sheets(a).Visible = True
'Next a
'ActiveWorkbook.protect Structure:=True, Windows:=False
On Error GoTo cleanup
Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False

Sheets(2).Visible = True

Sheet2.CommandButtonDESB.Visible = False
Sheet2.CommandButtonKNDP.Visible = False
Sheet2.CommandButtonKNMG.Visible = False
Sheet2.CommandButtonKNMI.Visible = False
Sheet2.CommandButtonKNOG.Visible = False
Sheet2.CommandButtonKNPS.Visible = False
Sheet2.CommandButtonIMBI.Visible = False
Sheet2.CommandButtonIMTR.Visible = False

Sheet2.Label1.Visible = False
Sheet2.Label2.Visible = False
Sheet2.Label3.Visible = False
Sheet2.Label4.Visible = False
Sheet2.Label5.Visible = False
Sheet2.Label6.Visible = False
Sheet2.Label7.Visible = False
Sheet2.Label8.Visible = False

Sheets(1).Visible = False
Sheets(3).Visible = False
Sheets(4).Visible = False
Sheets(5).Visible = False
Sheets(6).Visible = False
Sheets(7).Visible = False
Sheets(8).Visible = False
Sheets(9).Visible = False


Sheet2.CommandButton1.Visible = True
Sheet2.Label9.Visible = True


ThisWorkbook.Saved = True

cleanup:
Application.EnableCancelKey = xlInterrupt
Application.ScreenUpdating = True

End Sub

 

[Harlan Grove]

sorry i do not really get you.
you asked me to do as follow? nothing happens. no protection. infact
i just 1 2 be able to hide and show freely with protection (no
add/delete/resequence of sheet only). user still can edit on the sheet.
besides, what is xl like xldisabled? symbol :- and also R1C1(row 1
column 1) in the macro?

I have no intention of revising your macro for you.

I'll try again.

Do *NOT* protect your *WORKBOOK* in your macro until *AFTER* you have run
the statements like

Sheets(2).Visible = True

If you protect the workbook before you change any particular worksheet's
..Visible property, your macro will throw a runtime error at such statements.

Put the statement that protects the workbook near the *END* of the macro. If
the workbook is already protected (as it WILL be when you open it the second
an subsequent times), you'll need to UNPROTECT it at the beginning of the
macro.

I suggested adding additional statements to (1) trap errors and run an error
handler when any errors occur, (2) disable the cancel key so users can't
stop this macro from running (well, they can, but they couldn't do so
innocently), and (3) disable screen updating while the macro runs. These are
normal steps in startup macros like yours.

Unless you need to look up the meaning of the word AFTER, this should be
clear enough. But just in case, PUT THE PROTECT METHOD CALL JUST BEFORE THE
Cleanup: LABEL (the error handler) AND ITS TWO FOLLOWING STATEMENTS WHICH
SHOULD BE FOLLOWED IMMEDIATELY BY End Sub. Your problem is due ENTIRELY to
running the Protect method too soon.

 

[mango]

Hi Harlan,
thanks. i put in the protect and unprotect statement and it works nicely.
now i notice that user can unprotect the workbook by using tools menu and
unprotect it as no password box prompted out. Do i need to put in the
password before the protect and unprotect statement in place?
thanks

 

[Harlan Grove]

i put in the protect and unprotect statement and it works nicely.
now i notice that user can unprotect the workbook by using tools
menu and unprotect it as no password box prompted out. Do i need
to put in the password before the protect and unprotect statement
in place?

You could add a password to the .Protect and .Unprotect statements. Note,
however, that internal passwords are fairly simple to break.