Clearcontents not working with protecton

[onwankpa]

Hello,

I am a novice to VBA and constructed a macro to help with data entry on a worksheet. I am constructing a data entry worksheet using MS Excel 2010.

I am experiencing two issues that I cant' resolve and need help.


1.Macro clears content when protection is off, but not when protection is on.
2.Formulas in certain cells used to retain with clear contents, but now they get wiped away and are not available for subsequent entries.

(For some reason this code wouldnt work without filling every cell, so I put in a null feature, which fills every empty cell and while I believe that it is a great idea and one I would like to keep, it is probably is what is deleting the formulas. For some reason it used to retain some formulas in certain cells in the past, even after the null was placed in and now it is is not.)


My VBA code is a mash-up of content I found online; and is listed below.

I have been circling and circling around this online for a few days now, and probably made some obvious "Duh" like errors, but I need some help fixing the issue.

Please help.

Thanks in advance.

--

ub UpdateDatabase()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim ClrRng As Range
Dim myCopy As String
Dim myCopy2 As String
Dim myCopy3 As String
Dim myCopy4 As String
Dim myCell As Range
Dim cell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D12,D14,G14,J14,D46,G46,J46,D16,D18,D22,G22,D24,G24,J24,D26,G26,J26,J22,D34,D36,D51,D53,J51,G51,J55,J53,D55,G55,E68,H68"
myCopy2 = "E70,H70,K70,D59,G59,D61,G61,J61,J68,D74,G74,J74,G80,J78,D80,D82,G82,J82,D84,G84,J84,G88,J88,G90,J90,G92,J92"
myCopy3 = "D40,G40,J40,D42,D30,D96,G96,C98,J103"
myCopy4 = "D65,D68,G68,D70,G70,J70,G74,D78,G78,D103,G103"
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Database")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

'fill blank cells with "Null"
With inputWks
Set myRng = Union(.Range(myCopy), .Range(myCopy2), .Range(myCopy3))

For Each cell In myRng
If cell.Value = 0 Then
cell.Value = "Null"
End If
Next cell
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
With inputWks
Set ClrRng = Union(.Range(myCopy), .Range(myCopy2), .Range(myCopy3), .Range(myCopy4))
On Error Resume Next
With ClrRng.Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
ActiveWorkbook.Save
End With
On Error GoTo 0
End With

End Sub

[Kaper]

1) edit your post and use code tags - see forum rules how.

2) before "real action" unprotect your sheet. you can do this in code in a way similar to:

Please Login or Register  to view this content.

(possibly with a password)
and after all action protect it back.