How to protect few cells of the worksheet from editing?and few other doubts also plz help

Hi,
I want to create a template where in few fields like suppose
Company Logo
1.Name
2.Age
3.telephone number
Q1.
Now the user will open the document .I want that user should not be
able to change the logo and the titles.He should be able to only fill
the fields.I want to restrict the access to few cells in short,Kindly
guide me how to accomplish this task .Is it possible to do so?Can I
protect few cells in the worksheet from editing?If yes how?Plz help.Plz
tell me if it can't be done.

I have few word documents also wherin I have to do the same,a part of
the document should be frozen I mean restricted from editing (the names
of fields) and user will only be able to fill the fields.Kindly help I
have to submit it today.

Q2.
I have a work sheet with some format(Ex:-name age telephone number) now
I want that if user adds a new work sheet into this workbook the new
worksheet should automatically open up with the same format .if he adds
5 worksheets all should open up with the same format.without the need
for the user to copy it from the prev sheet.
Kindly help me with the ways to do this.

Regards and Thanks in advance
Divya

Q1.
Select cells you want to allow to modify.
Cell formatting/Protection/Uncheck Locked option/OK
Protect worksheet: Tools/Protection/Sheet protection/OK

Q2.
This is a workbook_event code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sheets("ExistingSheet").Cells.Copy '<--CHANGE the sheet name as necessary
Sh.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Post if you need help to install it!


„divya” ezt �*rta:

> Hi,
> I want to create a template where in few fields like suppose
> Company Logo
> 1.Name
> 2.Age
> 3.telephone number
> Q1.
> Now the user will open the document .I want that user should not be
> able to change the logo and the titles.He should be able to only fill
> the fields.I want to restrict the access to few cells in short,Kindly
> guide me how to accomplish this task .Is it possible to do so?Can I
> protect few cells in the worksheet from editing?If yes how?Plz help.Plz
> tell me if it can't be done.
>
> I have few word documents also wherin I have to do the same,a part of
> the document should be frozen I mean restricted from editing (the names
> of fields) and user will only be able to fill the fields.Kindly help I
> have to submit it today.
>
> Q2.
> I have a work sheet with some format(Ex:-name age telephone number) now
> I want that if user adds a new work sheet into this workbook the new
> worksheet should automatically open up with the same format .if he adds
> 5 worksheets all should open up with the same format.without the need
> for the user to copy it from the prev sheet.
> Kindly help me with the ways to do this.
>
> Regards and Thanks in advance
> Divya
>
>

Hi Stefi,
I got the way to protect cells,thnk u for tht,but in the 2nd
question I also want the new worksheet to gain the protection for the
same cells along with getting pasted.I jsut started learning VBA a week
back,writing macros,wanted to know how do I get to know which methods
or properties to use,I think it all comes with experience,I hope one
day I too will be able to clear doubts of the questins posted.

Coming back to my question no 2
1=2EHey I copied the event code u sent me ,when I inserted a new sheet
its just selecting all the cells of the new sheet as per sh.select but
not pasting the contents of the cells from the template worksheet .I
don't understand why. Its selecting the contents to be copied from the
template sheet,because on new sheet when I am clicking ctrl+v its
pasting the content but in the workbook_event code its not.Reply back
plz.

2=2EAs per my understanding the code you sent will copy the entire
contents of the template sheet to the new sheet,can I modify the code
to copy only selected cells like only the headings titles and not the
entries below it.ok will try and do it.at present its not pasting
anything.

3=2EAlso I wanted to have the new worsheet with the same protected cells
as its template has.

tell me how to do it.

Thanks a lot for ure time and help ,
Cheerz,
Divya


Stefi wrote:
> Q1.
> Select cells you want to allow to modify.
> Cell formatting/Protection/Uncheck Locked option/OK
> Protect worksheet: Tools/Protection/Sheet protection/OK
>
> Q2.
> This is a workbook_event code:
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Sheets("ExistingSheet").Cells.Copy '<--CHANGE the sheet name as nece=
ssary
> Sh.Select
> Selection.PasteSpecial Paste:=3DxlPasteFormats, Operation:=3DxlNone, _
> SkipBlanks:=3DFalse, Transpose:=3DFalse
> End Sub
>
> Post if you need help to install it!
>
>
> ,,divya" ezt =EDrta:
>
> > Hi,
> > I want to create a template where in few fields like suppose
> > Company Logo
> > 1.Name
> > 2.Age
> > 3.telephone number
> > Q1.
> > Now the user will open the document .I want that user should not be
> > able to change the logo and the titles.He should be able to only fill
> > the fields.I want to restrict the access to few cells in short,Kindly
> > guide me how to accomplish this task .Is it possible to do so?Can I
> > protect few cells in the worksheet from editing?If yes how?Plz help.Plz
> > tell me if it can't be done.
> >
> > I have few word documents also wherin I have to do the same,a part of
> > the document should be frozen I mean restricted from editing (the names
> > of fields) and user will only be able to fill the fields.Kindly help I
> > have to submit it today.
> >
> > Q2.
> > I have a work sheet with some format(Ex:-name age telephone number) now
> > I want that if user adds a new work sheet into this workbook the new
> > worksheet should automatically open up with the same format .if he adds
> > 5 worksheets all should open up with the same format.without the need
> > for the user to copy it from the prev sheet.
> > Kindly help me with the ways to do this.
> >=20
> > Regards and Thanks in advance
> > Divya
> >=20

> >

Hi divya,

That is because You wrote that you want new sheets "open up with the same
FORMAT" and you didn't mention same CONTENT!
Replace line
> > Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
by
ActiveSheet.Paste

Regards,
Stefi

„divya” ezt �*rta:

> Hi Stefi,
> I got the way to protect cells,thnk u for tht,but in the 2nd
> question I also want the new worksheet to gain the protection for the
> same cells along with getting pasted.I jsut started learning VBA a week
> back,writing macros,wanted to know how do I get to know which methods
> or properties to use,I think it all comes with experience,I hope one
> day I too will be able to clear doubts of the questins posted.
>
> Coming back to my question no 2
> 1.Hey I copied the event code u sent me ,when I inserted a new sheet
> its just selecting all the cells of the new sheet as per sh.select but
> not pasting the contents of the cells from the template worksheet .I
> don't understand why. Its selecting the contents to be copied from the
> template sheet,because on new sheet when I am clicking ctrl+v its
> pasting the content but in the workbook_event code its not.Reply back
> plz.
>
> 2.As per my understanding the code you sent will copy the entire
> contents of the template sheet to the new sheet,can I modify the code
> to copy only selected cells like only the headings titles and not the
> entries below it.ok will try and do it.at present its not pasting
> anything.
>
> 3.Also I wanted to have the new worsheet with the same protected cells
> as its template has.
>
> tell me how to do it.
>
> Thanks a lot for ure time and help ,
> Cheerz,
> Divya
>
>
> Stefi wrote:
> > Q1.
> > Select cells you want to allow to modify.
> > Cell formatting/Protection/Uncheck Locked option/OK
> > Protect worksheet: Tools/Protection/Sheet protection/OK
> >
> > Q2.
> > This is a workbook_event code:
> > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > Sheets("ExistingSheet").Cells.Copy '<--CHANGE the sheet name as necessary
> > Sh.Select
> > Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
> > SkipBlanks:=False, Transpose:=False
> > End Sub
> >
> > Post if you need help to install it!
> >
> >
> > ,,divya" ezt �*rta:
> >
> > > Hi,
> > > I want to create a template where in few fields like suppose
> > > Company Logo
> > > 1.Name
> > > 2.Age
> > > 3.telephone number
> > > Q1.
> > > Now the user will open the document .I want that user should not be
> > > able to change the logo and the titles.He should be able to only fill
> > > the fields.I want to restrict the access to few cells in short,Kindly
> > > guide me how to accomplish this task .Is it possible to do so?Can I
> > > protect few cells in the worksheet from editing?If yes how?Plz help.Plz
> > > tell me if it can't be done.
> > >
> > > I have few word documents also wherin I have to do the same,a part of
> > > the document should be frozen I mean restricted from editing (the names
> > > of fields) and user will only be able to fill the fields.Kindly help I
> > > have to submit it today.
> > >
> > > Q2.
> > > I have a work sheet with some format(Ex:-name age telephone number) now
> > > I want that if user adds a new work sheet into this workbook the new
> > > worksheet should automatically open up with the same format .if he adds
> > > 5 worksheets all should open up with the same format.without the need
> > > for the user to copy it from the prev sheet.
> > > Kindly help me with the ways to do this.
> > >
> > > Regards and Thanks in advance
> > > Divya
> > >
>
> > >
>
>

Hi Stefi,
I want the new sheet to inherit the protected cells from its
template.Can I do this?Example Consider a worksheet named EMP has 3
headings Name Place Age in cells A1 A2 A3 ,I have locked the 3 cells
and protected this work sheet.Now I put the workbook event code .So
when new workbook is created all the the three fields Name Place and
Age are Copied into this sheet.but they are now not protected.I want
all the new sheets to have these fields cells as protected.Can I do
this directly in the workbook event or I will have to again follow the
steps to protect cells every time I make new sheet and manually protect
these cells?
Cheerz ,
Divya



Stefi wrote:
> Hi divya,
>
> That is because You wrote that you want new sheets "open up with the same
> FORMAT" and you didn't mention same CONTENT!
> Replace line
> > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operation:=3DxlNo=
ne, _
> > > SkipBlanks:=3DFalse, Transpose:=3DFalse
> by
> ActiveSheet.Paste
>
> Regards,
> Stefi
>
> ,,divya" ezt =EDrta:
>
> > Hi Stefi,
> > I got the way to protect cells,thnk u for tht,but in the 2nd
> > question I also want the new worksheet to gain the protection for the
> > same cells along with getting pasted.I jsut started learning VBA a week
> > back,writing macros,wanted to know how do I get to know which methods
> > or properties to use,I think it all comes with experience,I hope one
> > day I too will be able to clear doubts of the questins posted.
> >
> > Coming back to my question no 2
> > 1.Hey I copied the event code u sent me ,when I inserted a new sheet
> > its just selecting all the cells of the new sheet as per sh.select but
> > not pasting the contents of the cells from the template worksheet .I
> > don't understand why. Its selecting the contents to be copied from the
> > template sheet,because on new sheet when I am clicking ctrl+v its
> > pasting the content but in the workbook_event code its not.Reply back
> > plz.
> >
> > 2.As per my understanding the code you sent will copy the entire
> > contents of the template sheet to the new sheet,can I modify the code
> > to copy only selected cells like only the headings titles and not the
> > entries below it.ok will try and do it.at present its not pasting
> > anything.
> >
> > 3.Also I wanted to have the new worsheet with the same protected cells
> > as its template has.
> >
> > tell me how to do it.
> >
> > Thanks a lot for ure time and help ,
> > Cheerz,
> > Divya
> >
> >
> > Stefi wrote:
> > > Q1.
> > > Select cells you want to allow to modify.
> > > Cell formatting/Protection/Uncheck Locked option/OK
> > > Protect worksheet: Tools/Protection/Sheet protection/OK
> > >
> > > Q2.
> > > This is a workbook_event code:
> > > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > > Sheets("ExistingSheet").Cells.Copy '<--CHANGE the sheet name as =
necessary
> > > Sh.Select
> > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operation:=3DxlNo=
ne, _
> > > SkipBlanks:=3DFalse, Transpose:=3DFalse
> > > End Sub
> > >
> > > Post if you need help to install it!
> > >
> > >
> > > ,,divya" ezt =EDrta:
> > >
> > > > Hi,
> > > > I want to create a template where in few fields like suppose
> > > > Company Logo
> > > > 1.Name
> > > > 2.Age
> > > > 3.telephone number
> > > > Q1.
> > > > Now the user will open the document .I want that user should not be
> > > > able to change the logo and the titles.He should be able to only fi=
ll
> > > > the fields.I want to restrict the access to few cells in short,Kind=
ly
> > > > guide me how to accomplish this task .Is it possible to do so?Can I
> > > > protect few cells in the worksheet from editing?If yes how?Plz help=
..Plz
> > > > tell me if it can't be done.
> > > >
> > > > I have few word documents also wherin I have to do the same,a part =
of
> > > > the document should be frozen I mean restricted from editing (the n=
ames
> > > > of fields) and user will only be able to fill the fields.Kindly hel=
p I
> > > > have to submit it today.
> > > >
> > > > Q2.
> > > > I have a work sheet with some format(Ex:-name age telephone number)=
now
> > > > I want that if user adds a new work sheet into this workbook the new
> > > > worksheet should automatically open up with the same format .if he =
adds
> > > > 5 worksheets all should open up with the same format.without the ne=
ed
> > > > for the user to copy it from the prev sheet.
> > > > Kindly help me with the ways to do this.
> > > >
> > > > Regards and Thanks in advance
> > > > Divya
> > > >=20
> >=20
> > > >
> >=20
> >

Insert this line after ActiveSheet.Paste:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Stefi


„divya” ezt �*rta:

> Hi Stefi,
> I want the new sheet to inherit the protected cells from its
> template.Can I do this?Example Consider a worksheet named EMP has 3
> headings Name Place Age in cells A1 A2 A3 ,I have locked the 3 cells
> and protected this work sheet.Now I put the workbook event code .So
> when new workbook is created all the the three fields Name Place and
> Age are Copied into this sheet.but they are now not protected.I want
> all the new sheets to have these fields cells as protected.Can I do
> this directly in the workbook event or I will have to again follow the
> steps to protect cells every time I make new sheet and manually protect
> these cells?
> Cheerz ,
> Divya
>
>
>
> Stefi wrote:
> > Hi divya,
> >
> > That is because You wrote that you want new sheets "open up with the same
> > FORMAT" and you didn't mention same CONTENT!
> > Replace line
> > > > Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
> > > > SkipBlanks:=False, Transpose:=False
> > by
> > ActiveSheet.Paste
> >
> > Regards,
> > Stefi
> >
> > ,,divya" ezt �*rta:
> >
> > > Hi Stefi,
> > > I got the way to protect cells,thnk u for tht,but in the 2nd
> > > question I also want the new worksheet to gain the protection for the
> > > same cells along with getting pasted.I jsut started learning VBA a week
> > > back,writing macros,wanted to know how do I get to know which methods
> > > or properties to use,I think it all comes with experience,I hope one
> > > day I too will be able to clear doubts of the questins posted.
> > >
> > > Coming back to my question no 2
> > > 1.Hey I copied the event code u sent me ,when I inserted a new sheet
> > > its just selecting all the cells of the new sheet as per sh.select but
> > > not pasting the contents of the cells from the template worksheet .I
> > > don't understand why. Its selecting the contents to be copied from the
> > > template sheet,because on new sheet when I am clicking ctrl+v its
> > > pasting the content but in the workbook_event code its not.Reply back
> > > plz.
> > >
> > > 2.As per my understanding the code you sent will copy the entire
> > > contents of the template sheet to the new sheet,can I modify the code
> > > to copy only selected cells like only the headings titles and not the
> > > entries below it.ok will try and do it.at present its not pasting
> > > anything.
> > >
> > > 3.Also I wanted to have the new worsheet with the same protected cells
> > > as its template has.
> > >
> > > tell me how to do it.
> > >
> > > Thanks a lot for ure time and help ,
> > > Cheerz,
> > > Divya
> > >
> > >
> > > Stefi wrote:
> > > > Q1.
> > > > Select cells you want to allow to modify.
> > > > Cell formatting/Protection/Uncheck Locked option/OK
> > > > Protect worksheet: Tools/Protection/Sheet protection/OK
> > > >
> > > > Q2.
> > > > This is a workbook_event code:
> > > > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > > > Sheets("ExistingSheet").Cells.Copy '<--CHANGE the sheet name as necessary
> > > > Sh.Select
> > > > Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
> > > > SkipBlanks:=False, Transpose:=False
> > > > End Sub
> > > >
> > > > Post if you need help to install it!
> > > >
> > > >
> > > > ,,divya" ezt �*rta:
> > > >
> > > > > Hi,
> > > > > I want to create a template where in few fields like suppose
> > > > > Company Logo
> > > > > 1.Name
> > > > > 2.Age
> > > > > 3.telephone number
> > > > > Q1.
> > > > > Now the user will open the document .I want that user should not be
> > > > > able to change the logo and the titles.He should be able to only fill
> > > > > the fields.I want to restrict the access to few cells in short,Kindly
> > > > > guide me how to accomplish this task .Is it possible to do so?Can I
> > > > > protect few cells in the worksheet from editing?If yes how?Plz help..Plz
> > > > > tell me if it can't be done.
> > > > >
> > > > > I have few word documents also wherin I have to do the same,a part of
> > > > > the document should be frozen I mean restricted from editing (the names
> > > > > of fields) and user will only be able to fill the fields.Kindly help I
> > > > > have to submit it today.
> > > > >
> > > > > Q2.
> > > > > I have a work sheet with some format(Ex:-name age telephone number) now
> > > > > I want that if user adds a new work sheet into this workbook the new
> > > > > worksheet should automatically open up with the same format .if he adds
> > > > > 5 worksheets all should open up with the same format.without the need
> > > > > for the user to copy it from the prev sheet.
> > > > > Kindly help me with the ways to do this.
> > > > >
> > > > > Regards and Thanks in advance
> > > > > Divya
> > > > >
> > >
> > > > >
> > >
> > >
>
>