PDA

View Full Version : a quick hi added with question



Eville
11-21-2008, 02:32 PM
Hello,

As every1 else started once ... so do i now ;)
I did use the forums alot before but i thought now it the time to say thank you to every1 for the loads of info that can be found here!
Im still searching & reading alot but one question thats keeping me busy:

I have a protected excel sheet (because i want end users not to change worksheet names else my macro to make a new sheet stops working) but now i cant copy the sheet anymore with my macro. So i have to choose:
unprotect sheet and risk ppl renaming the sheets or protect sheet but impossible to copy the sheet without removing the protection 1st.
Its probs kinda basic for most forum users but im still a starter and spent last 8 hours on the sheet and now i run into this lol.
Thx 4 any ideas in advance!

Ev

nst1107
11-21-2008, 02:38 PM
Use Workbook.Unprotect ("MyPassword")
or Worksheet.Unprotect ("MyPassword")
to remove password protection momentarily, then use
Workbook.Protect ("MyPassword")
or Worksheet.Protect ("MyPassword")
to protect again.

Eville
11-21-2008, 02:42 PM
Use Workbook.Unprotect ("MyPassword")
or Worksheet.Unprotect ("MyPassword")
to remove password protection momentarily, then use
Workbook.Protect ("MyPassword")
or Worksheet.Protect ("MyPassword")
to protect again.
Thats a quick reply :bow: . Ok thx alot. im gonna try fit it in there somewere lol : pray2:

Ev

Edit: just tried. This is the code:

Workbook.unprotect ("MyPassword")
Sheets("LOGSHEET(1)").Select
Sheets("LOGSHEET(1)").Copy Before:=Sheets(1)

Workbook.protect ("MyPassword")

Gives error code "424", Object required

Hmm ill have me thoughts over it what went wrong lol

GTO
11-21-2008, 02:58 PM
Greetings Eville,

Can you post the code you are using (the sub or function) thus far?

Mark

Eville
11-21-2008, 03:05 PM
Greetings Eville,

Can you post the code you are using (the sub or function) thus far?

Mark
Dont expect much pls, im only a starter & still need loads and loads to learn ><
So far the code to copy the original sheet is:

Sub Logsheet()
'
Workbook.unprotect ("MyPassword")
Sheets("LOGSHEET(1)").Select
Sheets("LOGSHEET(1)").Copy Before:=Sheets(1)

Workbook.protect ("MyPassword")
End Sub

As you can see its for a logsheet, to keep production results and when its full (7 coils) we start a next sheet with this above function.
I think i have 2 put the workbook protect and unprotect in a different way but still searching how 2 do so. So looks dumb maybe ... not sure :P

Ev

nst1107
11-21-2008, 03:10 PM
I'm sorry. This is the exact verbage you would use. Insert your real password where it says "MyPassword":

Sub Logsheet()
'
ThisWorkbook.Unprotect ("MyPassword")
Sheets("LOGSHEET(1)").Select
Sheets("LOGSHEET(1)").Copy Before:=Sheets(1)

ThisWorkbook.Protect ("MyPassword")
End Sub

Eville
11-21-2008, 03:21 PM
I'm sorry. This is the exact verbage you would use. Insert your real password where it says "MyPassword":

Sub Logsheet()
'
ThisWorkbook.Unprotect ("MyPassword")
Sheets("LOGSHEET(1)").Select
Sheets("LOGSHEET(1)").Copy Before:=Sheets(1)

ThisWorkbook.Protect ("MyPassword")
End Sub

Ty very much, its working now :D also its working on the next sheets & when i forget to protect the sheet and copy a sheet it automatically protects it. Thx loads :D
I should have known its ThisWorkbook and not Workbook :banghead:

Ev