Formatting unlocked cells and stop users from pasting

[roniaelm]

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

 

[Rick S.]

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

 

[roniaelm]

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









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

 

[Rick S.]

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

 

[roniaelm]

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








- Show quoted text -

Hi Rick,

Actually yor initial macro worked!!
Thanks for all of your help!!
Much appreciated!