pause macro to make changes before printing.

[Guest]

We have 4 pages in the workbook. The first 3 represent payroll info for 3
years. The 4th has summary for an employee showing the 3 years. The code
below scrolls through changing each cell reference to each employee and
printing out a report.
There are some rows that represent info that we do not want to show if it
does not pertain to an employee (bonuses). I am trying change the cell
reference and then pause while the unnecessary rows are manually hidden,
print the report and loop to the next employee.

The message box causes a pause, however the hourglass is still there and I
can't make any modifications. How do I get aroun this? It looks to me that
the macro is still running and that's why I can't make changes.

Sub macro1()
For Emp = 8 To 63
Application.Goto Reference:="Name"
ActiveCell.Replace What:=Emp, Replacement:=Emp + 1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:=Emp, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Application.Goto Reference:="Wages"
Selection.Replace What:=Emp, Replacement:=Emp + 1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="Benefits"
Selection.Replace What:=Emp, Replacement:=Emp + 1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="Hours"
Selection.Replace What:=Emp, Replacement:=Emp + 1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.Goto Reference:="Rate"
Selection.Replace What:=Emp, Replacement:=Emp + 1, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox ("Make changes to the report")
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next Emp
End Sub


Thanks!

 

[Guest]

A msgbox is modal. So as you say, the macro is still running.

There is no provision for doing what you describe. Why can't you put code
in your procedure that recognizes when rows should be hidden and have the
code hide them.

 

[Guest]

Thanks. I was hoping there was a work around but at least I can stop
searching.

I thought about doing it that way, but the person I'm doing this for wanted
me to try it with a pause and a message box.

I would like to hide row 17, H17 is 0 etc. Could you give me a hint on what
the code woudl be. I usually program in Ascess and Excel is a bit different.


I got as far as:
Rows("17:17").Select
Selection.EntireRowHidden = True

I don't know how to write the if statement and reference Cell H17 =0.


Thanks!

 

[Guest]

if Range("H17").Value = 0 then
rows(17).EntireRow.Hidden = True
else
rows(17).Entirerow.Hidden = False
end if

 

[Guest]

Thanks...it worked like a charm!!

if Range("H17").Value = 0 then
rows(17).EntireRow.Hidden = True
else
rows(17).Entirerow.Hidden = False
end if