How to protect all sheet ?

V

vumian

hi everyone,

i have 2 questions here

i have file, but i do not know how many sheet are there in before

1) how to use VBA to protect all sheet with password ?

2) how to use VBA to copy value of C4 to name of all sheet, if per C
have same value, it must popup mSg 'Error'

- Is there a way to integrate 1 and 2 become one function.

thank you for hel
 
S

Scoops

vumian said:
hi everyone,

i have 2 questions here

i have file, but i do not know how many sheet are there in before

1) how to use VBA to protect all sheet with password ?

2) how to use VBA to copy value of C4 to name of all sheet, if per C4
have same value, it must popup mSg 'Error'

- Is there a way to integrate 1 and 2 become one function.

thank you for help
Click to expand...

Hi vumian

1)

Sub ProtectAllSheets()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Protect
Next
End Sub

2) Can be added to the code above with

sht.Name = Range("C4").Value

but why is it going to fail? Is there a likely to be a duplicate
value, illegal characters or both?

Note: the opposite of .Protect is .Unprotect

Regards

Steve
 
V

vumian

hi man,

ok if it corrects, AND HOW ABOUT THE DUPLICATE NAME OF C4 FOR NAME TO
SHEET.
maybe let's popup msgbox here to info error.

how to do it.

regards
 
G

Gord Dibben

Adapt this to your needs.

Sub Sheetname_cell()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Range("C4").Value
'next lines cover duplicate names

If Err.Number > 0 Then
MsgBox "Change the name of : " & sh.Name & " manually"
Err.Clear
End If
On Error GoTo 0

Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
V

vumian

hi man,

your code so great,

and now, per sheet i have 1 picture, but i do not wanna lock it, it is
normal

how to do it

thanks in advance
 
Top