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

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

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

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

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

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

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