Modification in code

Topic

New Reply

I want modify the code to allow users to format cloumns/rows in sheet array. Pl help.
[codebox]Sub Hide()
Dim strName As Variant
For Each strName In Array(“BG3”, “SSC”, “EXHAUST”, “MUFFLER”, “FRAME”, “RIM”, “HRD”, “PNG”, “ANS”)
With Worksheets(strName)
.Columns(“A:AR”).Hidden = True
.Protect Password:=”psd”
.Select
.Range(“AS1”).Select
End With
Next
Sheets(“Key Ratio “).Select
Range(“A3”).Select
End Sub[/codebox]

Reply | Quote

Replies

You can expand the line

.Protect Password:=”psd”

to specify what the user is allowed to do:

.Protect Password:=”psd”, AllowFormattingColumns:=True, AllowFormattingRows:=True

(If you omit AllowFormattingColumns and AllowFormattingColumns they are assumed to be False)

See the help for Protect for other available options.

Reply | Quote

You can expand the line

.Protect Password:=”psd”

to specify what the user is allowed to do:

.Protect Password:=”psd”, AllowFormattingColumns:=True, AllowFormattingRows:=True

(If you omit AllowFormattingColumns and AllowFormattingColumns they are assumed to be False)

See the help for Protect for other available options.

Thanks Hans. It is perfect.

For the sake of curiosity, is it possible to allow to format particular columns/rows instead of entire ws?

Reply | Quote

What exactly do you want to accomplish?

Reply | Quote

What exactly do you want to accomplish?

In a wb, some columns, say A to Z are used as data field & Col. AA to AE are used as summary. The wb is protected & for review purpose, I keep the data fields hidden. With the help of above code, users are allowed to format col./rows as required. I want to restrict the users to format the summary portion only, if possible.

Reply | Quote

You can make it a bit more diffcult to unhide rows / columns by not allowing users to select locked cells. But I don’t think you can prevent it entirely. If you really want that, you shouldn’t allow users to format rows and columns.

Reply | Quote

You can make it a bit more diffcult to unhide rows / columns by not allowing users to select locked cells. But I don’t think you can prevent it entirely. If you really want that, you shouldn’t allow users to format rows and columns.

Thanks Hans but as I stated, it is *NOT* necessarily required to hide particular columns & the code is perfect without any further modification. I was looking for the possibility of avoiding any mis-presentation but that can be taken care of without amending the code. (After all, the users are equally responsible for any (mis) presentation of data.)

Thanks again.

Reply | Quote

Thanks Hans but as I stated, it is *NOT* necessarily required to hide particular columns & the code is perfect without any further modification. I was looking for the possibility of avoiding any mis-presentation but that can be taken care of without amending the code. (After all, the users are equally responsible for any (mis) presentation of data.)

Thanks again.

Pl have a look :

[codebox]Sub unhide()
For Each sht In ActiveWorkbook.Sheets
On Error Resume Next
sht.Unprotect
If Err Then
MsgBox “Password incorrect. Please try again.”, vbExclamation
sht.Unprotect
If Err Then
MsgBox “Sorry! better luck next time”, vbExclamation
End If
End If
On Error GoTo 0
Next sht
Dim strName As Variant
For Each strName In Array(“BG3”, “SSC”, “MUFFLER”, “FRAME”, “RIM”, “HRD”, “PNG”, “ANS”)
With Worksheets(strName)
.Columns(“A:AP”).Hidden = False
.Select
.Range(“A1”).Select
End With
Next
Sheets(“Key Ratio “).Select
Range(“A3”).Select
End Sub[/codebox]

1) if I hit the OR press cancel button instead of supplying the password, the code unprotect the sheet & jump to next sheet.

2) It is more convenient for me to unprotect all sheets in array in single attempt, instead of supplying password for each sheet one by one.

3) If I supplied correct password on second attempt, the code unprotect the sheet but msg still flashes.

Sorry if it sounds irretating but I have not tested the code this way before.

Reply | Quote

Are all sheets protected?
Do all protected sheets have the same password?

Reply | Quote

Are all sheets protected?

No Hans, only sheets in array are protected using following code.
[codebox]ub Hide()
Dim strName As Variant
For Each strName In Array(“BG3”, “SSC”, “MUFFLER”, “FRAME”, “RIM”, “HRD”, “PNG”, “ANS”)
With Worksheets(strName)
.Columns(“A:AR”).Hidden = True
.Columns(“AU:AU”).Hidden = True
.Protect Password:=”psd”, AllowFormattingColumns:=True, AllowFormattingRows:=True
.Select
.Range(“AS1”).Select
End With
Next
Sheets(“Key Ratio “).Select
Range(“A3”).Select
End Sub[/codebox]

Do all protected sheets have the same password?

Yes, all protected sheets have the same password.

Reply | Quote

Do you need the user to supply the password to unprotect the sheets, or would it be OK to include the password in the code (like you do in the code that protects the sheets)?

Reply | Quote

Do you need the user to supply the password to unprotect the sheets, or would it be OK to include the password in the code (like you do in the code that protects the sheets)?

Yes, users are required to supply the password only once to unprotect all protected sheets.

Reply | Quote

Try this:

Code:

Sub Unhide()
  Dim strPwd As String
  Dim strName As Variant
  ' Ask for password
  strPwd = InputBox("Please enter the password.")
  If Not strPwd = "psd" Then
	' Give user another chance
	strPwd = InputBox("Password incorrect. Please try again.")
	If Not strPwd = "psd" Then
	  ' Twice wrong - get out
	  MsgBox "Sorry! Better luck next time.", vbExclamation
	  Exit Sub
	End If
  End If
  ' Loop through protected sheets
  For Each strName In Array("BG3", "SSC", "MUFFLER", "FRAME", "RIM", "HRD", "PNG", "ANS")
	With Worksheets(strName)
	  ' Unprotect
	  .Unprotect Password:=strPwd
	  ' Other actions
	  .Columns("A:AP").Hidden = False
	  .Select
	  .Range("A1").Select
	End With
  Next strName
  ' Final actions
  Sheets("Key Ratio ").Select
  Range("A3").Select
End Sub

Reply | Quote

If I supplied correct password on second attempt, it fails to unprotect the sheets.

Reply | Quote

Sorry, my mistake. The line

If Not strPassword = “psd” Then

should be

If Not strPwd = “psd” Then

If you had had Option Explicit at the top of the module, the Visual Basic Editor would have pointed out the inconsistency.

Reply | Quote

Sorry, my mistake. The line

If Not strPassword = “psd” Then

should be

If Not strPwd = “psd” Then

If you had had Option Explicit at the top of the module, the Visual Basic Editor would have pointed out the inconsistency.

Thanks Hans, it is perfect now.

Reply | Quote

An additional requirement :

Is it possible to modify the code to protect workbook structure too but allowing users to move/copy sheets?

Thanks in advance for any help/suggestion.

Reply | Quote

No.

You’d have to write your own code to
– Unprotect the workbook
– Move a worksheet
– Protect the workbook

And also write code to
– Unprotect the workbook
– Copy a worksheet
– Protect the workbook

Reply | Quote