Help me clean this up...

[BigDave]

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,

[Bob Phillips]

"BigDave" <[email protected]> wrote in
message news:[email protected]...
>
> Question1: .screenupdating = false and xlCalculationManual - are these
> necessary. What would happen if I took those out?

It would be slower and you would see lots of screen flashing.

> 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?

Yeah, hidden and protected is okay


Worksheets("ws1").Range("A2:M21").Copy Worksheets("Sheet2").Range("A2")

> Qestion3: When this proceedure ends, incell functions I have on Sheet2
> cycle through again. Here is the function:

Don't understand this one.

[BigDave]

Thanks - I cleaned it up quite a bit and it is doing 99% of what I want to. Thanks so much for the help, I really appreciate it.

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 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
Worksheets("ws1").Range("a2:m21").copy
Next

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 With
End Sub