pause macro to make changes before printing.

[Baffee]

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!

[Tom Ogilvy]

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.

--
Regards,
Tom Ogilvy


"Baffee" wrote:

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

[Baffee]

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!

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Baffee" wrote:
>
> > 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!

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"Baffee" wrote:

> 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!
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Baffee" wrote:
> >
> > > 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!

[Baffee]

Thanks...it worked like a charm!!

"Tom Ogilvy" wrote:

> if Range("H17").Value = 0 then
> rows(17).EntireRow.Hidden = True
> else
> rows(17).Entirerow.Hidden = False
> end if
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Baffee" wrote:
>
> > 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!
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Baffee" wrote:
> > >
> > > > 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!