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
> > > > >
> > >
> > > > >
> > >
> > >
>
>

Hiiii
1.I have a form which is loaded on workbook_open event.
2.On this form There are two option buttons namely optSI and
optFPS.the user selects one and clicks Ok button
3.Now the onClick event for OK has following code
Public fps, si As Integer ' I want to know scope of
these variables
Private Sub CmdOK_Click()
If OptFPS Then
fps=3D1
Sheets.Add
ElseIf optSI Then
Sheets.Add
si =3D 1
End If
Unload Me
End Sub
4. Now when a sheet is added I want to
1. call convertmkstofps if optfps is selected
2 call convertmkstosi if optsi is selected

How can we do this??What I did is
I wrote followinfg code in the Workbook_newsheet event


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sheets("Template").Cells.Copy
Sh.Select
ActiveSheet.Paste
If fps =3D 1 Then
Call convertmkstofps ' this macro will make changes in few cells
to FPS units
ElseIf si =3D 1 Then
Call convertmkstosi ' this macro will make changes in few cells
to SI units
End If
End Sub


But this doesn't seem to work,I tried to display fps and si in
message box it doesn't show any value.
I have declared these values as public as u see in Note 3 above the

command button click event,then too its scope is limited only till the
form.


Plzz tell me how do I do this,I mean access (optfps and optsi) values
or (fps and si )in the Workbook_NewSheet(ByVal Sh As Object) event


Regards
Divya



Stefi wrote:

> Insert this line after ActiveSheet.Paste:
> ActiveSheet.Protect DrawingObjects:=3DTrue, Contents:=3DTrue, Scenarios:=
=3DTrue
> Stefi
>
>
> ,,divya" ezt =EDrta:
>
> > 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:=3D=
xlNone, _
> > > > > 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 t=
he
> > > > 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 metho=
ds
> > > > 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 ba=
ck
> > > > 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 co=
de
> > > > to copy only selected cells like only the headings titles and not t=
he
> > > > 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 ce=
lls
> > > > 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:=3D=
xlNone, _
> > > > > 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 no=
t be
> > > > > > able to change the logo and the titles.He should be able to onl=
y 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?C=
an 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 p=
art of
> > > > > > the document should be frozen I mean restricted from editing (t=
he 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 num=
ber) now
> > > > > > I want that if user adds a new work sheet into this workbook th=
e new
> > > > > > worksheet should automatically open up with the same format .if=
he adds
> > > > > > 5 worksheets all should open up with the same format.without th=
e 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
> > > > > >=20
> > > >=20
> > > > > >
> > > >=20
> > > >
> >=20
> >

Hi Stefi
I placed the public variables at wrong place so was getting such an
error.I sorted it out.
You had sent me this code for copying

Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > > > > > Sheets("ExistingSheet").Cells.Copy '<--CHANGE the sheet na=
me as necessary
> > > > > > Sh.Select
> > > > > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operation:=
=3DxlNone, _
> > > > > > SkipBlanks:=3DFalse, Transpose:=3DFalse
> > > > > > End Sub

But when new sheet is created it has the whole sheet selected.I have to
click on the new worksheet to deselect.Can't I deselect in the code
itself??
I added the line sh.deselect but it doesn't work.Tell me why it gives
error and how to do .
regards
Divya

divya wrote:

> Hiiii
> 1.I have a form which is loaded on workbook_open event.
> 2.On this form There are two option buttons namely optSI and
> optFPS.the user selects one and clicks Ok button
> 3.Now the onClick event for OK has following code
> Public fps, si As Integer ' I want to know scope of
> these variables
> Private Sub CmdOK_Click()
> If OptFPS Then
> fps=3D1
> Sheets.Add
> ElseIf optSI Then
> Sheets.Add
> si =3D 1
> End If
> Unload Me
> End Sub
> 4. Now when a sheet is added I want to
> 1. call convertmkstofps if optfps is selected
> 2 call convertmkstosi if optsi is selected
>
> How can we do this??What I did is
> I wrote followinfg code in the Workbook_newsheet event
>
>
> Private Sub Workbook_NewSheet(ByVal Sh As Object)
> Sheets("Template").Cells.Copy
> Sh.Select
> ActiveSheet.Paste
> If fps =3D 1 Then
> Call convertmkstofps ' this macro will make changes in few cells
> to FPS units
> ElseIf si =3D 1 Then
> Call convertmkstosi ' this macro will make changes in few cells
> to SI units
> End If
> End Sub
>
>
> But this doesn't seem to work,I tried to display fps and si in
> message box it doesn't show any value.
> I have declared these values as public as u see in Note 3 above the
>
> command button click event,then too its scope is limited only till the
> form.
>
>
> Plzz tell me how do I do this,I mean access (optfps and optsi) values
> or (fps and si )in the Workbook_NewSheet(ByVal Sh As Object) event
>
>
> Regards
> Divya
>
>
>
> Stefi wrote:
>
> > Insert this line after ActiveSheet.Paste:
> > ActiveSheet.Protect DrawingObjects:=3DTrue, Contents:=3DTrue, Scenarios=
:=3DTrue
> > Stefi
> >
> >
> > ,,divya" ezt =EDrta:
> >
> > > 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 prote=
ct
> > > these cells?
> > > Cheerz ,
> > > Divya
> > >
> > >
> > >
> > > Stefi wrote:
> > > > Hi divya,
> > > >
> > > > That is because You wrote that you want new sheets "open up with th=
e same
> > > > FORMAT" and you didn't mention same CONTENT!
> > > > Replace line
> > > > > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operation:=
=3DxlNone, _
> > > > > > 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 t=
he 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 met=
hods
> > > > > 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 sh=
eet
> > > > > its just selecting all the cells of the new sheet as per sh.selec=
t but
> > > > > not pasting the contents of the cells from the template worksheet=
=2EI
> > > > > don't understand why. Its selecting the contents to be copied fro=
m 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 na=
me as necessary
> > > > > > 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 supp=
ose
> > > > > > > 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 o=
nly fill
> > > > > > > the fields.I want to restrict the access to few cells in shor=
t,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?Pl=
z 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.Kind=
ly help I
> > > > > > > have to submit it today.
> > > > > > >
> > > > > > > Q2.
> > > > > > > I have a work sheet with some format(Ex:-name age telephone n=
umber) 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
> > > > > > >
> > > > >=20
> > > > > > >
> > > > >=20
> > > > >
> > >=20
> > >

Hi divya,

Deselect is not an existing method in VBA. A certain range (it may as well
be a single cell) is always selected. So simply select something else, say A1
as in the starting position of a worksheet, insert this line as last line in
the sub:

Range("A1").Select

Regards,
Stefi


„divya” ezt �*rta:

> Hi Stefi
> I placed the public variables at wrong place so was getting such an
> error.I sorted it out.
> You had sent me this code for copying
>
> 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
>
> But when new sheet is created it has the whole sheet selected.I have to
> click on the new worksheet to deselect.Can't I deselect in the code
> itself??
> I added the line sh.deselect but it doesn't work.Tell me why it gives
> error and how to do .
> regards
> Divya
>
> divya wrote:
>
> > Hiiii
> > 1.I have a form which is loaded on workbook_open event.
> > 2.On this form There are two option buttons namely optSI and
> > optFPS.the user selects one and clicks Ok button
> > 3.Now the onClick event for OK has following code
> > Public fps, si As Integer ' I want to know scope of
> > these variables
> > Private Sub CmdOK_Click()
> > If OptFPS Then
> > fps=1
> > Sheets.Add
> > ElseIf optSI Then
> > Sheets.Add
> > si = 1
> > End If
> > Unload Me
> > End Sub
> > 4. Now when a sheet is added I want to
> > 1. call convertmkstofps if optfps is selected
> > 2 call convertmkstosi if optsi is selected
> >
> > How can we do this??What I did is
> > I wrote followinfg code in the Workbook_newsheet event
> >
> >
> > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > Sheets("Template").Cells.Copy
> > Sh.Select
> > ActiveSheet.Paste
> > If fps = 1 Then
> > Call convertmkstofps ' this macro will make changes in few cells
> > to FPS units
> > ElseIf si = 1 Then
> > Call convertmkstosi ' this macro will make changes in few cells
> > to SI units
> > End If
> > End Sub
> >
> >
> > But this doesn't seem to work,I tried to display fps and si in
> > message box it doesn't show any value.
> > I have declared these values as public as u see in Note 3 above the
> >
> > command button click event,then too its scope is limited only till the
> > form.
> >
> >
> > Plzz tell me how do I do this,I mean access (optfps and optsi) values
> > or (fps and si )in the Workbook_NewSheet(ByVal Sh As Object) event
> >
> >
> > Regards
> > Divya
> >
> >
> >
> > Stefi wrote:
> >
> > > 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
> > > > > > > >
> > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
>
>

Thank you Stefi,yah aftrwards I found that deselect is for charts.Nice
tip I didn't think of it.
Stefi wrote:
> Hi divya,
>
> Deselect is not an existing method in VBA. A certain range (it may as well
> be a single cell) is always selected. So simply select something else, sa=
y A1
> as in the starting position of a worksheet, insert this line as last line=
in
> the sub:
>
> Range("A1").Select
>
> Regards,
> Stefi
>
>
> ,,divya" ezt =EDrta:
>
> > Hi Stefi
> > I placed the public variables at wrong place so was getting such an
> > error.I sorted it out.
> > You had sent me this code for copying
> >
> > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > > > > > > > Sheets("ExistingSheet").Cells.Copy '<--CHANGE the shee=
t name as necessary
> > > > > > > > Sh.Select
> > > > > > > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operati=
on:=3DxlNone, _
> > > > > > > > SkipBlanks:=3DFalse, Transpose:=3DFalse
> > > > > > > > End Sub
> >
> > But when new sheet is created it has the whole sheet selected.I have to
> > click on the new worksheet to deselect.Can't I deselect in the code
> > itself??
> > I added the line sh.deselect but it doesn't work.Tell me why it gives
> > error and how to do .
> > regards
> > Divya
> >
> > divya wrote:
> >
> > > Hiiii
> > > 1.I have a form which is loaded on workbook_open event.
> > > 2.On this form There are two option buttons namely optSI and
> > > optFPS.the user selects one and clicks Ok button
> > > 3.Now the onClick event for OK has following code
> > > Public fps, si As Integer ' I want to know scope of
> > > these variables
> > > Private Sub CmdOK_Click()
> > > If OptFPS Then
> > > fps=3D1
> > > Sheets.Add
> > > ElseIf optSI Then
> > > Sheets.Add
> > > si =3D 1
> > > End If
> > > Unload Me
> > > End Sub
> > > 4. Now when a sheet is added I want to
> > > 1. call convertmkstofps if optfps is selected
> > > 2 call convertmkstosi if optsi is selected
> > >
> > > How can we do this??What I did is
> > > I wrote followinfg code in the Workbook_newsheet event
> > >
> > >
> > > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > > Sheets("Template").Cells.Copy
> > > Sh.Select
> > > ActiveSheet.Paste
> > > If fps =3D 1 Then
> > > Call convertmkstofps ' this macro will make changes in few cells
> > > to FPS units
> > > ElseIf si =3D 1 Then
> > > Call convertmkstosi ' this macro will make changes in few cells
> > > to SI units
> > > End If
> > > End Sub
> > >
> > >
> > > But this doesn't seem to work,I tried to display fps and si in
> > > message box it doesn't show any value.
> > > I have declared these values as public as u see in Note 3 above t=
he
> > >
> > > command button click event,then too its scope is limited only till the
> > > form.
> > >
> > >
> > > Plzz tell me how do I do this,I mean access (optfps and optsi) values
> > > or (fps and si )in the Workbook_NewSheet(ByVal Sh As Object) event
> > >
> > >
> > > Regards
> > > Divya
> > >
> > >
> > >
> > > Stefi wrote:
> > >
> > > > Insert this line after ActiveSheet.Paste:
> > > > ActiveSheet.Protect DrawingObjects:=3DTrue, Contents:=3DTrue, Scena=
rios:=3DTrue
> > > > Stefi
> > > >
> > > >
> > > > ,,divya" ezt =EDrta:
> > > >
> > > > > 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 ce=
lls
> > > > > 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 w=
ant
> > > > > 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 follo=
w the
> > > > > steps to protect cells every time I make new sheet and manually p=
rotect
> > > > > these cells?
> > > > > Cheerz ,
> > > > > Divya
> > > > >
> > > > >
> > > > >
> > > > > Stefi wrote:
> > > > > > Hi divya,
> > > > > >
> > > > > > That is because You wrote that you want new sheets "open up wit=
h the same
> > > > > > FORMAT" and you didn't mention same CONTENT!
> > > > > > Replace line
> > > > > > > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operati=
on:=3DxlNone, _
> > > > > > > > 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 h=
ope 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 ne=
w sheet
> > > > > > > its just selecting all the cells of the new sheet as per sh.s=
elect but
> > > > > > > not pasting the contents of the cells from the template works=
heet .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.Re=
ply back
> > > > > > > plz.
> > > > > > >
> > > > > > > 2.As per my understanding the code you sent will copy the ent=
ire
> > > > > > > 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 pas=
ting
> > > > > > > anything.
> > > > > > >
> > > > > > > 3.Also I wanted to have the new worsheet with the same protec=
ted 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 shee=
t name as necessary
> > > > > > > > Sh.Select
> > > > > > > > Selection.PasteSpecial Paste:=3DxlPasteFormats, Operati=
on:=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 sho=
uld 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 d=
o so?Can I
> > > > > > > > > protect few cells in the worksheet from editing?If yes ho=
w?Plz help..Plz
> > > > > > > > > tell me if it can't be done.
> > > > > > > > >
> > > > > > > > > I have few word documents also wherin I have to do the sa=
me,a part of
> > > > > > > > > the document should be frozen I mean restricted from edit=
ing (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 telepho=
ne number) now
> > > > > > > > > I want that if user adds a new work sheet into this workb=
ook the new
> > > > > > > > > worksheet should automatically open up with the same form=
at .if he adds
> > > > > > > > > 5 worksheets all should open up with the same format.with=
out 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
> > > > > > > > >
> > > > > > >
> > > > > > > > >
> > > > > > >=20
> > > > > > >
> > > > >=20
> > > > >
> >=20
> >

You are welcome! Thanks for the feedback!
Stefi


„divya” ezt �*rta:

> Thank you Stefi,yah aftrwards I found that deselect is for charts.Nice
> tip I didn't think of it.
> Stefi wrote:
> > Hi divya,
> >
> > Deselect is not an existing method in VBA. A certain range (it may as well
> > be a single cell) is always selected. So simply select something else, say A1
> > as in the starting position of a worksheet, insert this line as last line in
> > the sub:
> >
> > Range("A1").Select
> >
> > Regards,
> > Stefi
> >
> >
> > ,,divya" ezt �*rta:
> >
> > > Hi Stefi
> > > I placed the public variables at wrong place so was getting such an
> > > error.I sorted it out.
> > > You had sent me this code for copying
> > >
> > > 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
> > >
> > > But when new sheet is created it has the whole sheet selected.I have to
> > > click on the new worksheet to deselect.Can't I deselect in the code
> > > itself??
> > > I added the line sh.deselect but it doesn't work.Tell me why it gives
> > > error and how to do .
> > > regards
> > > Divya
> > >
> > > divya wrote:
> > >
> > > > Hiiii
> > > > 1.I have a form which is loaded on workbook_open event.
> > > > 2.On this form There are two option buttons namely optSI and
> > > > optFPS.the user selects one and clicks Ok button
> > > > 3.Now the onClick event for OK has following code
> > > > Public fps, si As Integer ' I want to know scope of
> > > > these variables
> > > > Private Sub CmdOK_Click()
> > > > If OptFPS Then
> > > > fps=1
> > > > Sheets.Add
> > > > ElseIf optSI Then
> > > > Sheets.Add
> > > > si = 1
> > > > End If
> > > > Unload Me
> > > > End Sub
> > > > 4. Now when a sheet is added I want to
> > > > 1. call convertmkstofps if optfps is selected
> > > > 2 call convertmkstosi if optsi is selected
> > > >
> > > > How can we do this??What I did is
> > > > I wrote followinfg code in the Workbook_newsheet event
> > > >
> > > >
> > > > Private Sub Workbook_NewSheet(ByVal Sh As Object)
> > > > Sheets("Template").Cells.Copy
> > > > Sh.Select
> > > > ActiveSheet.Paste
> > > > If fps = 1 Then
> > > > Call convertmkstofps ' this macro will make changes in few cells
> > > > to FPS units
> > > > ElseIf si = 1 Then
> > > > Call convertmkstosi ' this macro will make changes in few cells
> > > > to SI units
> > > > End If
> > > > End Sub
> > > >
> > > >
> > > > But this doesn't seem to work,I tried to display fps and si in
> > > > message box it doesn't show any value.
> > > > I have declared these values as public as u see in Note 3 above the
> > > >
> > > > command button click event,then too its scope is limited only till the
> > > > form.
> > > >
> > > >
> > > > Plzz tell me how do I do this,I mean access (optfps and optsi) values
> > > > or (fps and si )in the Workbook_NewSheet(ByVal Sh As Object) event
> > > >
> > > >
> > > > Regards
> > > > Divya
> > > >
> > > >
> > > >
> > > > Stefi wrote:
> > > >
> > > > > 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
> > > > > > > > > >
> > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > >
> > >
>
>