|
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 |
How to protect few cells of the worksheet from editing?and few oth
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 |
How to protect few cells of the worksheet from editing?and few oth
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
How to protect few cells of the worksheet from editing?and few
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 |
| All times are GMT +1. The time now is 03:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com