Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy paste Macro in a Protected Sheet
Hi Guys!!!
I have a spreedsheet that has a checkbox with a macro and I want to protect the spreadsheet to protect the formulas from novices users. I have figure out all the steps of unlock the cell that I want the user to enter the data, unlocked the cells that link to the check box. The PROBLEM is that I have a range that a macro has to copy and paste but I want the range to remain lock for the users but gets unlock when the macro needs to copy and paste. this is the code that I have right now: WHAT DO I NEED TO write to allow the macro to unlock, copy, paste and lock the file again. Sub CheckBox1107_Click() ' ' CheckBox1107_Click Macro ' If ActiveSheet.Range("D19").Value = True Then Application.Goto Reference:="R16C4" Range("D16:D18").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("H9").Select ActiveCell.FormulaR1C1 = "0" Range("H11").Select ActiveCell.FormulaR1C1 = "0" Range("H12").Select ActiveCell.FormulaR1C1 = "0" Range("D12").Select Application.Goto Reference:="R16C4" Else Application.Goto Reference:="R45C2" Range("B43:B45").Select Range("B45").Activate Selection.Copy Application.Goto Reference:="R16C4" Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.Goto Reference:="R16C4" End If End Sub WHAT DO I NEED TO write to allow the macro to unlock, copy, paste and lock the file again. Thank YOU, PAMELA xoxo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy paste Macro in a Protected Sheet
Lock the sheet with a macro instead of manually. When you lock it with a macro, you can lock it from user input only, but leave other code free to change things. For instance, this code locks the sheets when the workbook opens, the user can't change anything, but your macros could: ============= Private Sub Workbook_Open() 'If you have different passwords 'for each Worksheet. Sheets(1).Protect Password:="Secret", _ UserInterFaceOnly:=True Sheets(2).Protect Password:="Carrot", _ UserInterFaceOnly:=True 'Repeat as needed. End Sub =============== Adjust for your needs. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48359 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
1 Create a macro to Copy & paste certain data to another sheet | Excel Discussion (Misc queries) | |||
How copy/paste values in Excel doc protected sheet 2 other doc? | Excel Discussion (Misc queries) | |||
Do not allow Copy/Paste Functionality in a Protected Sheet | Excel Worksheet Functions | |||
How to disable copy and paste function in a protected sheet | Setting up and Configuration of Excel | |||
How to copy/paste info into the protected sheet | Excel Discussion (Misc queries) |