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.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 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:=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

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

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

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

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