Private Sub CommandButton7_Click()
Dim varAnswer As String
varAnswer = MsgBox("Are you certain you wish to proceed? This cannot
be undone." & Chr(10) & Chr(10) & "Edits to this workbook my only be
entered into your Data Sheet manually once the current data is
compiled.", vbOKCancel)
If varAnswer = vbCancel Then
Exit Sub
End If
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Worksheets("ws1")
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value <= " " Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty
End If
Next
ActiveWorkbook.Unprotect ("xxx")
Sheets("ws1").Visible = True
Sheets("ws1").Select
.DisplayPageBreaks = False
Sheets("ws1").Range("a2:m21").Select
Selection.copy
Sheets("ws1").Visible = False
ActiveWorkbook.Protect ("xxx")
Sheets("Sheet2").Select
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
MsgBox ("Your inspection data for this worksheet has been compiled.
Please go immeditately to the current version of you Data
Sheet/PivotTable to import this data.")
End Sub
Question1: .screenupdating = false and xlCalculationManual - are these
necessary. What would happen if I took those out?
Question2: this proceedure is activated with a button on Sheet2.
After the blanks are deleted, I need to copy A2:M21 on sheet "ws1", but
"ws1" is (1) hidden and (2) the workbook is protected. The code above
does what its supposed to do, but in proceedure the clipboard is
getting wiped out and I can't paste (into another, seperate workbook)
what I copied. Can I copy a range on a hidden sheet in a protected
workbook? If so, how?
Qestion3: When this proceedure ends, incell functions I have on Sheet2
cycle through again. Here is the function:
Function commenttext2(incell As String) As String
If incell > " " Then commenttext2 = InputBox("Please enter your
datasheet comment for" & " " & " " & ActiveSheet.Range("c41").Value)
End Function
The comment that is typed into the inputBox is part of what is copied
in the proceedure at the top. Why is the execution of the proceedure
at the top causing these functions to re-cycle?
Thanks,
