Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting unlocked cells and stop users from pasting
Hi,
There are multiple users that need to update a worksheet that I have protected so I need to ensure that they are restricted from inserting/ deleting cells or makig major formatting changes (the cells are unlocked though for them to make their changes directly). But we still require the majority of the user's to make one formating change, which is to change applicable text to red in any cells of the spreadsheet. Does anyone know a VBA code I can add to my code to permit users without full access to do so. Also, is there a way to prevent users from pasting over protected unlocked cells. As many of the users are overwriting formulas and formatting when they do so. Your help will be appreciated!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting unlocked cells and stop users from pasting
You could Lock the entire sheet, create a macro that "Unprotects and
Protects" the worksheet and allows the "selected" cell to be unlocked. You can pass the worksheet password in the macro then lock the macro from prying eyes. Something like this? (Assuming the entire sheet is locked) Sub Macro3() With Selection ActiveSheet.Unprotect Password:=1 With Selection.Font .Color = -16776961 .TintAndShade = 0 End With End With ActiveSheet.Protect Password:=1 End Sub The user slects a cell to color RED and then runs the macro, they really won't format anything, its all in the code. Am I close to what you want? -- Regards Rick XP Pro Office 2007 " wrote: Hi, There are multiple users that need to update a worksheet that I have protected so I need to ensure that they are restricted from inserting/ deleting cells or makig major formatting changes (the cells are unlocked though for them to make their changes directly). But we still require the majority of the user's to make one formating change, which is to change applicable text to red in any cells of the spreadsheet. Does anyone know a VBA code I can add to my code to permit users without full access to do so. Also, is there a way to prevent users from pasting over protected unlocked cells. As many of the users are overwriting formulas and formatting when they do so. Your help will be appreciated!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting unlocked cells and stop users from pasting
On Oct 12, 1:15 am, Rick S. wrote:
You could Lock the entire sheet, create a macro that "Unprotects and Protects" the worksheet and allows the "selected" cell to be unlocked. You can pass the worksheet password in the macro then lock the macro from prying eyes. Something like this? (Assuming the entire sheet is locked) Sub Macro3() With Selection ActiveSheet.Unprotect Password:=1 With Selection.Font .Color = -16776961 .TintAndShade = 0 End With End With ActiveSheet.Protect Password:=1 End Sub The user slects a cell to color RED and then runs the macro, they really won't format anything, its all in the code. Am I close to what you want? -- Regards Rick XP Pro Office 2007 " wrote: Hi, There are multiple users that need to update a worksheet that I have protected so I need to ensure that they are restricted from inserting/ deleting cells or makig major formatting changes (the cells are unlocked though for them to make their changes directly). But we still require the majority of the user's to make one formating change, which is to change applicable text to red in any cells of the spreadsheet. Does anyone know a VBA code I can add to my code to permit users without full access to do so. Also, is there a way to prevent users from pasting over protected unlocked cells. As many of the users are overwriting formulas and formatting when they do so. Your help will be appreciated!!- Hide quoted text - - Show quoted text - Hi Rick, Thanks for your help. So with this macro do the use's need to be supplied with a password? Because I am trying to avoid doing that. Also, if I lock all of the cells, does that mean that they cannot directly make changes to the sheet without a password? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting unlocked cells and stop users from pasting
You should lock the "Project" (vba code), with the worksheet password in the
code then they can not see, will not see or need to enter the password. In my example the entire sheet is locked and the password is coded. What is the extent of "editing" the users will need? -- Regards Rick XP Pro Office 2007 " wrote: On Oct 12, 1:15 am, Rick S. wrote: You could Lock the entire sheet, create a macro that "Unprotects and Protects" the worksheet and allows the "selected" cell to be unlocked. You can pass the worksheet password in the macro then lock the macro from prying eyes. Something like this? (Assuming the entire sheet is locked) Sub Macro3() With Selection ActiveSheet.Unprotect Password:=1 With Selection.Font .Color = -16776961 .TintAndShade = 0 End With End With ActiveSheet.Protect Password:=1 End Sub The user slects a cell to color RED and then runs the macro, they really won't format anything, its all in the code. Am I close to what you want? -- Regards Rick XP Pro Office 2007 " wrote: Hi, There are multiple users that need to update a worksheet that I have protected so I need to ensure that they are restricted from inserting/ deleting cells or makig major formatting changes (the cells are unlocked though for them to make their changes directly). But we still require the majority of the user's to make one formating change, which is to change applicable text to red in any cells of the spreadsheet. Does anyone know a VBA code I can add to my code to permit users without full access to do so. Also, is there a way to prevent users from pasting over protected unlocked cells. As many of the users are overwriting formulas and formatting when they do so. Your help will be appreciated!!- Hide quoted text - - Show quoted text - Hi Rick, Thanks for your help. So with this macro do the use's need to be supplied with a password? Because I am trying to avoid doing that. Also, if I lock all of the cells, does that mean that they cannot directly make changes to the sheet without a password? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting unlocked cells and stop users from pasting
On Oct 17, 12:58 am, Rick S. wrote:
You should lock the "Project" (vba code), with the worksheet password in the code then they can not see, will not see or need to enter the password. In my example the entire sheet is locked and the password is coded. What is the extent of "editing" the users will need? -- Regards Rick XP Pro Office 2007 " wrote: On Oct 12, 1:15 am, Rick S. wrote: You could Lock the entire sheet, create a macro that "Unprotects and Protects" the worksheet and allows the "selected" cell to be unlocked. You can pass the worksheet password in the macro then lock the macro from prying eyes. Something like this? (Assuming the entire sheet is locked) Sub Macro3() With Selection ActiveSheet.Unprotect Password:=1 With Selection.Font .Color = -16776961 .TintAndShade = 0 End With End With ActiveSheet.Protect Password:=1 End Sub The user slects a cell to color RED and then runs the macro, they really won't format anything, its all in the code. Am I close to what you want? -- Regards Rick XP Pro Office 2007 " wrote: Hi, There are multiple users that need to update a worksheet that I have protected so I need to ensure that they are restricted from inserting/ deleting cells or makig major formatting changes (the cells are unlocked though for them to make their changes directly). But we still require the majority of the user's to make one formating change, which is to change applicable text to red in any cells of the spreadsheet. Does anyone know a VBA code I can add to my code to permit users without full access to do so. Also, is there a way to prevent users from pasting over protected unlocked cells. As many of the users are overwriting formulas and formatting when they do so. Your help will be appreciated!!- Hide quoted text - - Show quoted text - Hi Rick, Thanks for your help. So with this macro do the use's need to be supplied with a password? Because I am trying to avoid doing that. Also, if I lock all of the cells, does that mean that they cannot directly make changes to the sheet without a password? Thanks!- Hide quoted text - - Show quoted text - Hi Rick, Actually yor initial macro worked!! Thanks for all of your help!! Much appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I stop format change when copying data into unlocked cells | Excel Worksheet Functions | |||
Stop users pasting data in cells | Excel Discussion (Misc queries) | |||
How to allow all users to make changes to unlocked cells on WrkSh | Excel Discussion (Misc queries) | |||
Protect unlocked cells in a protected worksheet from cut/pasting | Excel Discussion (Misc queries) | |||
How do you prevent users from pasting over validated cells? | Excel Discussion (Misc queries) |