protect - macro?

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

Hey guys,
I have this macro and I want my worksheet to be protected but it says I am
not able to protect the sheet because of the macro.
Is there any way around it?

Thanks
 
I forgot one thing, the code...
so here it is:

Private Sub Worksheet_Activate()
Call Main
End Sub

Sub Main()
Dim hideRows As Range
Application.ScreenUpdating = False
With Range("G3:G2401")
.EntireRow.Hidden = False
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next
Set hideRows = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Selection.AutoFilter
If Not hideRows Is Nothing Then hideRows.EntireRow.Hidden = True
Range("G202,G402,G602,G802,G1002,G1202,G1402," & _
"G1602,G1802,G2002,G2202,G2402").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub


Thanks
 
"It" says ???

You have two options. One is protecting by code, userinterface only:

Sheets(1).Protect Password:="Yo", _
UserInterfaceOnly:=True

Now macros can do more things than the user is allowed.

Or you can simply have your macro unprotect-work-protect like this

Sheets(1).Unrotect Password:="Yo"
'autofilter, hide, stuff
Sheets(1).Protect Password:="Yo"
 
What if the user looks in the code? the password is viewable.


--
Stu


Harald Staff said:
"It" says ???

You have two options. One is protecting by code, userinterface only:

Sheets(1).Protect Password:="Yo", _
UserInterfaceOnly:=True

Now macros can do more things than the user is allowed.

Or you can simply have your macro unprotect-work-protect like this

Sheets(1).Unrotect Password:="Yo"
'autofilter, hide, stuff
Sheets(1).Protect Password:="Yo"
Click to expand...
 
You can prevent that in the VB editor menu Tools > VBA proj Properties > Protection. But
I'd prefer hiding or masking the password instead; unlocked code may provide you with good
debugging information until the code is perfect.

Anyway, you're too paranoid. Excel is not safe and sheet passwords can be cracked easily.
Security is basically there to prevent fools from doing severe damage to formulas, not to
protect our digital rights.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

Stu said:
What if the user looks in the code? the password is viewable.
Click to expand...
 
That is true I am too paranoid and for no reason, because like you say it is
to prevent fools from messing with things they shouldn't but I know a lot of
fools who will probably venture into the code and mess that up too.

And I cant seem to get the:

Sheets(1).Protect Password:="Yo", _
UserInterfaceOnly:=True

or

Sheets(1).Unrotect Password:="Yo"
'autofilter, hide, stuff
Sheets(1).Protect Password:="Yo"

to work.

Thanks
 
Why are you talking about users. You said *You* would be using xl2002, so
limitations of xl97 were of no consequence. Now you have users - will they
all also be using xl2002? If not, you have more surprises headed your way.

Using userinterfaceonly will not work with your autofilter code.
Userinterfaceonly only works with an already existing filter. There is no
reason the unprotect shouldn't work if you spell it correctly and the
password is the correct password.

The other method I showed will work with userinterfaceonly, of course.
 
I haven't got a clue what the users would be using, but the users should
have pretty up-to-date software and OS.

And, where do I put the

Sheets(1).Unprotect Password:="Yo"
'autofilter, hide, stuff
Sheets(1).Protect Password:="Yo"

Thanks
 
Just like in real life; unprotect the sheet, perform actions, protect the
sheet when done. You should know your own code well enough to spot the
appropriate locations ;-)
 
ah yes,
I have put them in the sub
whereas before I was trying to put them in a sub of their own which is why
it wouldn't work.

Thanks
 
Right where you show it:

Sub Main()
Dim hideRows As Range
Application.ScreenUpdating = False
Activesheet.Unprotect Password:="Yo"
With Range("G3:G2401")
.EntireRow.Hidden = False
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next
Set hideRows = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Selection.AutoFilter
If Not hideRows Is Nothing Then hideRows.EntireRow.Hidden = True
Range("G202,G402,G602,G802,G1002,G1202,G1402," & _
"G1602,G1802,G2002,G2202,G2402").EntireRow.Hidden = False
Application.ScreenUpdating = True
Application.Protect Password:="Yo"
End Sub

Might want to find out. You should be doing your development in the oldest
version of excel where the application will be used.
 
The problem with that is that I don't have the oldest version of XL that it
will be used on. and the code I decided on is the following way round
(exactly the same as yours just moved around a little):

Sub MainHide()
Application.ScreenUpdating = False
Main.UnProtect Password:="Yo"
Dim hideRows As Range
With Range("G3:G2401")
.EntireRow.Hidden = False
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next
Set hideRows = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Selection.AutoFilter
If Not hideRows Is Nothing Then hideRows.EntireRow.Hidden = True
Range("G202,G402,G602,G802,G1002,G1202,G1402," & _
"G1602,G1802,G2002,G2202,G2402").EntireRow.Hidden = False
Main.Protect Password:="Yo"
Application.ScreenUpdating = True
End Sub

Thanks
 
With reference to where you put the statement, what is the significance of
this:
The problem with that is that I don't have the oldest version of XL that it
will be used on.
Click to expand...

You can be cute now, but when your app fails, you might want some help.
 
I am in the process of emailing everyone that will use this spreadsheet so I
will soon know if it will fail or not.

Thanks
 
Back
Top