2 Question regarding a Protected Worksheet

[Accidential_Techie]

1. I created a protected worksheet that has users enter a date in an unprotected cell. Once the user has entered the date, they must Control +c to run a macro which is an autofill for a two week period. The macro actually unproctects the data range, completes the auto fill an then protects the range again. Is there any other way to have the auto fill without the user input of Control C.
2. In the same worksheet can I allow user input into a range but protect the formatting..for example - If I drag and drop from a cell with shading to a cell with no shading ..the shading is moved as well

Thanks for any assistance
Accidential Techie

 

[Dave Peterson]

#1. One way is to use an event that looks for that entry by the user.

Rightclick on the worksheet tab and select view code.
Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Exit Sub

On Error GoTo errHandler:
Me.Range("A1").AutoFill _
Destination:=Me.Range("A1:A" & Me.Cells(Me.Rows.Count,
"B").End(xlUp).Row)

errHandler:
Application.EnableEvents = True

End Sub

You could add unprotect and protect statements to this code, but another way is
to protect the sheet using code in your workbook_open event.

You can allow your macros to do more things than the user can.

Option Explicit
Private Sub Workbook_Open()
Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

#2. That's a problem. The simplest solution may be to teach them how to
pastespecial|values. (Or if it's really important, reapply the formatting when
you want (right before the save?). You could keep a mirrored (formats only
hidden copy of the worksheet and copy|paste special|formats).

 

[Accidential Techie]

Thank you for your response. I am still pretty new at this therefore I do not know what to make of this..the following is appearing in red indicating an error. I am assuming that I should replace a1 with my user input cell of c4 and A1:a should that be replaced with c4. If this assumption is incorrect what should I do and is there anything else I need to change in the statement which referrs directly to my worksheet?

Me.Range("a1").AutoFill
Destination:=Me.Range("a1:a" & Me.Cells(Me.Rows.Count
"B").End(xlUp).Row

Accidential Techie (and truly by accident

 

[JE McGimpsey]

You're probably a victim of linewrap - the second line should be on one
physical line in the module. Or, you can use the line continuation
characters (space-underscore):

Me.Range("a1").AutoFill _
Destination:=Me.Range("a1:a" & Me.Cells(Me.Rows.Count, _
"B").End(xlUp).Row)

 

[Dave Peterson]

Thanks, JE.

(Sometimes I miss those long lines!)