| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
is it possible to record a macro that would lock a sheet
hi again
is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
When a macro runs the sheet is already effectively locked. You can protect
it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
Hi Bob,
Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
Just loop them all
For Each sh In Activeworkbook.Worksheets sh.Protect Next sh -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diddy" wrote in message ... Hi Bob, Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
Thank you Bob :-)
-- Deirdre "Bob Phillips" wrote: Just loop them all For Each sh In Activeworkbook.Worksheets sh.Protect Next sh -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diddy" wrote in message ... Hi Bob, Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
This was a very helpful post. I was wondering if it's possible to create a
macro that would protect all the worksheets in the workbook but also allow users to edit a certain range? Thanks, EugeniaP. "Diddy" wrote: Thank you Bob :-) -- Deirdre "Bob Phillips" wrote: Just loop them all For Each sh In Activeworkbook.Worksheets sh.Protect Next sh -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diddy" wrote in message ... Hi Bob, Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
Hi EugeniaP,
Once you have unprotected the ranges you want to be able to edit in Format Cells Protection then you can use the code on this page http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm under UserInterFaceOnly which allows you to run other macros at the same time as users aer able to edit ranges. Cheers -- Deirdre "EugeniaP" wrote: This was a very helpful post. I was wondering if it's possible to create a macro that would protect all the worksheets in the workbook but also allow users to edit a certain range? Thanks, EugeniaP. "Diddy" wrote: Thank you Bob :-) -- Deirdre "Bob Phillips" wrote: Just loop them all For Each sh In Activeworkbook.Worksheets sh.Protect Next sh -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diddy" wrote in message ... Hi Bob, Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
Hi, Diddy!
I am not sure I get this. Here is my problem. I have 20 sheets in my workbook that I need to protect but at the same time allow users to edit range "C2:C30" in each one of these sheets. I don't want to go into every sheet and manually do "allow users to edit range" 20 times. What would the macro be to do all at once? Thanks, EugeniaP "Diddy" wrote: Hi EugeniaP, Once you have unprotected the ranges you want to be able to edit in Format Cells Protection then you can use the code on this page http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm under UserInterFaceOnly which allows you to run other macros at the same time as users aer able to edit ranges. Cheers -- Deirdre "EugeniaP" wrote: This was a very helpful post. I was wondering if it's possible to create a macro that would protect all the worksheets in the workbook but also allow users to edit a certain range? Thanks, EugeniaP. "Diddy" wrote: Thank you Bob :-) -- Deirdre "Bob Phillips" wrote: Just loop them all For Each sh In Activeworkbook.Worksheets sh.Protect Next sh -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diddy" wrote in message ... Hi Bob, Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
is it possible to record a macro that would lock a sheet
Hi EugeniaP,
I'm only a newbie so I can only tell you what I would do. You can probably do this via VBA and if you want to do that it may be better to post again with a new subject line. For what it's worth here's what I would do Hold shift and click on sheets you want to group. On one of the sheets click on the top left hand corner to highlight the whole of the sheet. Then Format Cells Protection. I usually clear the locked tick box just to make sure that all protection is removed then do the same again ticking the locked tick box to apply protection to all cells. Highlight the range you want un-protected and Format Cells Protection uncheck box. Then right click on one of the grouped sheet tabs and choose Ungroup sheets. Then you can protect the sheets using VBA or manually. HTH Diddy -- Deirdre "EugeniaP" wrote: Hi, Diddy! I am not sure I get this. Here is my problem. I have 20 sheets in my workbook that I need to protect but at the same time allow users to edit range "C2:C30" in each one of these sheets. I don't want to go into every sheet and manually do "allow users to edit range" 20 times. What would the macro be to do all at once? Thanks, EugeniaP "Diddy" wrote: Hi EugeniaP, Once you have unprotected the ranges you want to be able to edit in Format Cells Protection then you can use the code on this page http://www.ozgrid.com/VBA/excel-macr...cted-sheet.htm under UserInterFaceOnly which allows you to run other macros at the same time as users aer able to edit ranges. Cheers -- Deirdre "EugeniaP" wrote: This was a very helpful post. I was wondering if it's possible to create a macro that would protect all the worksheets in the workbook but also allow users to edit a certain range? Thanks, EugeniaP. "Diddy" wrote: Thank you Bob :-) -- Deirdre "Bob Phillips" wrote: Just loop them all For Each sh In Activeworkbook.Worksheets sh.Protect Next sh -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Diddy" wrote in message ... Hi Bob, Following on from that, how would you code it to protect all sheets in the workbook? Cheers -- Deirdre "Bob Phillips" wrote: When a macro runs the sheet is already effectively locked. You can protect it afterwards with worksheets("Sheet1").Protect -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "des-sa" wrote in message ... hi again is there a way to have macro lock a specific sheet for editing when the macro is run, then maybe have it password protected for editing afterwards thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
| Record New Macro | Setting up and Configuration of Excel | |||
| lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
| how do i create a record sheet | New Users to Excel | |||
| Service record sheet | Excel Discussion (Misc queries) | |||
![des-sa[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
