Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Saving some cells
I have a protected worksheet with some cells unprotected, and this works
fine till I run a macro to essentally clear some of the unprotected cells. The cells get cleared but the 'unprotected' cells get locked in the process. Is there a way round this or am I missing the obvious. Have included the macro code for your comments Many thanks Bernard Sub Macro1() ' ' ' ' Keyboard Shortcut: Ctrl+d ' Application.Goto Reference:="R6C1" Range("A6").Select Range(Selection, Selection.End(xlDown)).Select Range("A6:AF28").Select Selection.Clear Application.Goto Reference:="R32C1" Range("A32:AF38").Select Selection.Clear Application.Goto Reference:="R42C1" Range("A42:AF48").Select Selection.Clear Application.Goto Reference:="R1C2" Range("B1").Select ActiveCell.FormulaR1C1 = "N" Selection.AutoFill Destination:=Range("B1:AF1"), Type:=xlFillDefault Range("B1:AF1").Select 'Range("U21").Select Application.CommandBars("Stop Recording").Visible = False Range("A3").Select MsgBox "Enter First Date of the Month, eg 1/10/2006" MsgBox "Confirm Public Holidays, Change N to Y, where appropriate " End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Saving some cells
Hi Bunnis,
I have a protected worksheet with some cells unprotected, and this works fine till I run a macro to essentally clear some of the unprotected cells. The cells get cleared but the 'unprotected' cells get locked in the process. Replace each instance of : Selection.Clear with Selection.ClearContents If, however, you also need to delete formats, change each instance of: Selection.Clear to With Selection .Clear .Locked = False End with --- Regards, Norman "Bunnis" wrote in message ... I have a protected worksheet with some cells unprotected, and this works fine till I run a macro to essentally clear some of the unprotected cells. The cells get cleared but the 'unprotected' cells get locked in the process. Is there a way round this or am I missing the obvious. Have included the macro code for your comments Many thanks Bernard Sub Macro1() ' ' ' ' Keyboard Shortcut: Ctrl+d ' Application.Goto Reference:="R6C1" Range("A6").Select Range(Selection, Selection.End(xlDown)).Select Range("A6:AF28").Select Selection.Clear Application.Goto Reference:="R32C1" Range("A32:AF38").Select Selection.Clear Application.Goto Reference:="R42C1" Range("A42:AF48").Select Selection.Clear Application.Goto Reference:="R1C2" Range("B1").Select ActiveCell.FormulaR1C1 = "N" Selection.AutoFill Destination:=Range("B1:AF1"), Type:=xlFillDefault Range("B1:AF1").Select 'Range("U21").Select Application.CommandBars("Stop Recording").Visible = False Range("A3").Select MsgBox "Enter First Date of the Month, eg 1/10/2006" MsgBox "Confirm Public Holidays, Change N to Y, where appropriate " End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |