How to protect all sheet ?

[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

 

[Scoops]

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

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

 

[Scoops]

2) Can be added to the code above with

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

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

Regards

Steve

 

[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

 

[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

 

[vumian]

hi Gord Dibben,

it's so great, that is a code which i need

thank you very much.

 

[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