How to protect all sheet ?
 

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


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567884

How to protect all sheet ?
 

vumian wrote:
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


--
vumian

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

How to protect all sheet ?
 

Scoops wrote:
2) Can be added to the code above with

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

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

Regards

Steve

How to protect all sheet ?
 

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


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567884

How to protect all sheet ?
 

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

On Thu, 3 Aug 2006 12:23:48 -0400, vumian
wrote:


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

How to protect all sheet ?
 

hi Gord Dibben,

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

thank you very much.


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567884

How to protect all sheet ?
 

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


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567884