Hi,

In one of my sheets I need to create an individual sheet with some
results.
Durisng the creation of this sheet I also create a button to close the
sheet and return to regular activity in the workbook.

All of this is done within a sub and the solution I thought was to
have a do while loop with doevent waiting for a value in a cell of
another sheet. (this value is changed when the user clicks the button)

This works pretty well, but some users are having trouble because the
button stops working, and it happens randomly.

I am not sure why Doevent stops working which effectively kills the
the macro and the user gets stuck.
It could also be that the button macro doesnt work, in either case the
running of macros has been stopped becuase the button doesnt change
the cell value as it should.
What can cause this so I can troubleshoot and find a solution?

hi
you have discribed using doevents in a way that is alien to me. post your
code.

Regards
FSt1

" wrote:

Hi,

In one of my sheets I need to create an individual sheet with some
results.
Durisng the creation of this sheet I also create a button to close the
sheet and return to regular activity in the workbook.

All of this is done within a sub and the solution I thought was to
have a do while loop with doevent waiting for a value in a cell of
another sheet. (this value is changed when the user clicks the button)

This works pretty well, but some users are having trouble because the
button stops working, and it happens randomly.

I am not sure why Doevent stops working which effectively kills the
the macro and the user gets stuck.
It could also be that the button macro doesnt work, in either case the
running of macros has been stopped becuase the button doesnt change
the cell value as it should.
What can cause this so I can troubleshoot and find a solution?

Hi,

In module1 I have a sub that creates a new sheet called report and
formats it and copies data from another sheet and on the Report sheet
a button is created with this code:

Public Sub SendEmail(WorkingSheet As String)
Dim Msg As String
Dim Ws As Worksheet
Dim NewWs As Worksheet
ThisWorkbook.Sheets(WorkingSheet).Unprotect ("meteor")
UnprotectMonthlySheet
ThisWorkbook.Sheets("MonthlyTrack").Range("R24").V alue = False
'Hide all sheets
For Each Ws In Worksheets
If Ws.Name = "Report" Then
Application.DisplayAlerts = False
Ws.Delete
Application.DisplayAlerts = True
ElseIf Ws.Name < WorkingSheet Then
Ws.Visible = xlSheetHidden
End If
Next Ws

Dim btn As Button ', Lbl As msforms.Label
Worksheets.Add
ThisWorkbook.ActiveSheet.Name = "Report"
ThisWorkbook.Sheets(WorkingSheet).Select
Range("A1:D23").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Report").Activate
Sheets("Report").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ThisWorkbook.Sheets(WorkingSheet).Select
Application.CommandBars("Chart").Visible = False
If WorkingSheet = "MonthlyTrack" Then
ActiveSheet.ChartObjects("Chart 1").Activate
'ThisWorkbook.Sheets("MonthlyTrack").Unprotect ("meteor")
If WorkingSheet = "Week_to_date" Then
ActiveSheet.ChartObjects("Chart 1026").Activate
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
Sheets("Report").Activate
Range("A3").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate

Range("A3").Select
Set NewWs = ActiveSheet

With NewWs

NewWs.Range("B25") = "You have activated Report mode!"
NewWs.Range("B26") = "You can Email or Print this page."
NewWs.Range("B28") = "Click 'DONE' to exit Report mode."
Range("B25:B28").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Set btn = NewWs.Buttons.Add(65.25, 502.75, 296.25, 32.25)
btn.Select
Selection.Characters.Text = "[...DONE...]"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = "CommandButtonSendEmail"
End With

ThisWorkbook.Sheets(WorkingSheet).Visible = xlSheetHidden

'Wait for user to send email
ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue = False
NewWs.Activate
NewWs.Range("A34").Select

Do While ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue =
False
DoEvents
Loop
ThisWorkbook.Sheets(WorkingSheet).Visible = xlSheetVisible

Application.DisplayAlerts = False
NewWs.Delete
Application.DisplayAlerts = True

'Show all sheets again
For Each Ws In Worksheets
Ws.Visible = xlSheetVisible
Next Ws
ThisWorkbook.Sheets("MonthlyTrack").Range("R24").V alue = True
End Sub

Heres the code for the CommandButtonSendEmail which is also located in
Module1:

Public Sub CommandButtonSendEmail()
ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue = True
End Sub

Thats it...
What could be wrong to make Doevents stop?

Any ideas?

Any ideas?

Hi,
That has worked?
I don't think you are using Doevents for its intended purpose.
Doevents yields control to the operating system.
It stops or pauses the macro and "tells" windows "You have control of the
processor. Take care of any and ALL pending events. When you are done return
control to me."

If Windows has no pending events you are stuck in a very tight loop.

Since Windows has control, Excel events may not be detected. Excel is
getting very little processor time.

What you need to do is end the sub.
Add a new sub that gets fired when the button is clicked. (An event)

Something like this...
NewWs.Activate
NewWs.Range("A34").Select

' REM out loop
' Do While ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue =False
' DoEvents
' Loop
' ThisWorkbook.Sheets(WorkingSheet).Visible = xlSheetVisible
End Sub

Public Sub CommandButtonSendEmail_Click() 'XXXX Assumes button is named
"CommandButtonSendEmail" XXXXXX
ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue = True

Application.DisplayAlerts = False
NewWs.Delete
Application.DisplayAlerts = True

'Show all sheets again
For Each Ws In Worksheets
Ws.Visible = xlSheetVisible
Next Ws
ThisWorkbook.Sheets("MonthlyTrack").Range("R24").V alue = True

End Sub

John




wrote in message
...
Hi,

In module1 I have a sub that creates a new sheet called report and
formats it and copies data from another sheet and on the Report sheet
a button is created with this code:

Public Sub SendEmail(WorkingSheet As String)
Dim Msg As String
Dim Ws As Worksheet
Dim NewWs As Worksheet
ThisWorkbook.Sheets(WorkingSheet).Unprotect ("meteor")
UnprotectMonthlySheet
ThisWorkbook.Sheets("MonthlyTrack").Range("R24").V alue = False
'Hide all sheets
For Each Ws In Worksheets
If Ws.Name = "Report" Then
Application.DisplayAlerts = False
Ws.Delete
Application.DisplayAlerts = True
ElseIf Ws.Name < WorkingSheet Then
Ws.Visible = xlSheetHidden
End If
Next Ws

Dim btn As Button ', Lbl As msforms.Label
Worksheets.Add
ThisWorkbook.ActiveSheet.Name = "Report"
ThisWorkbook.Sheets(WorkingSheet).Select
Range("A1:D23").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Report").Activate
Sheets("Report").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ThisWorkbook.Sheets(WorkingSheet).Select
Application.CommandBars("Chart").Visible = False
If WorkingSheet = "MonthlyTrack" Then
ActiveSheet.ChartObjects("Chart 1").Activate
'ThisWorkbook.Sheets("MonthlyTrack").Unprotect ("meteor")
If WorkingSheet = "Week_to_date" Then
ActiveSheet.ChartObjects("Chart 1026").Activate
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
Sheets("Report").Activate
Range("A3").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate

Range("A3").Select
Set NewWs = ActiveSheet

With NewWs

NewWs.Range("B25") = "You have activated Report mode!"
NewWs.Range("B26") = "You can Email or Print this page."
NewWs.Range("B28") = "Click 'DONE' to exit Report mode."
Range("B25:B28").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Set btn = NewWs.Buttons.Add(65.25, 502.75, 296.25, 32.25)
btn.Select
Selection.Characters.Text = "[...DONE...]"
With Selection
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 10
.Font.ColorIndex = xlAutomatic
.Locked = True
.LockedText = True
End With
btn.OnAction = "CommandButtonSendEmail"
End With

ThisWorkbook.Sheets(WorkingSheet).Visible = xlSheetHidden

'Wait for user to send email
ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue = False
NewWs.Activate
NewWs.Range("A34").Select

Do While ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue =
False
DoEvents
Loop
ThisWorkbook.Sheets(WorkingSheet).Visible = xlSheetVisible

Application.DisplayAlerts = False
NewWs.Delete
Application.DisplayAlerts = True

'Show all sheets again
For Each Ws In Worksheets
Ws.Visible = xlSheetVisible
Next Ws
ThisWorkbook.Sheets("MonthlyTrack").Range("R24").V alue = True
End Sub

Heres the code for the CommandButtonSendEmail which is also located in
Module1:

Public Sub CommandButtonSendEmail()
ThisWorkbook.Sheets("MonthlyTrack").Range("M25").V alue = True
End Sub

Thats it...
What could be wrong to make Doevents stop?

Thanks Jaf,

Simple and I missed it. :)
I am still learning using VBA and Excel.
For some reason I thought that I could not stop running the macro
otherwise the button would not work... dont know why I thought that
thats why I used the DOEVENT to loop the macro.

Looking back now I cant explain why I did it this way but I did. weird
huh? :)

Hi Jaf,

Didnt take me long to figure out why I needed to have a doevent in a
loop. :)

I need to stop the macro until the button is pressed to allow the user
to email the report before doing other tasks

I have two report sheets that need to be emailed, one is weekly and
the other is monthly.
At the beginning of every week the AUTO_OPEN sub on module1 calls this
sub and it was to wait until the button is clicked before continuing
to reset the report.
I also have placed a button on the weekly report itself that also
calls this sub as well in case user wants to email the report or
print.
And finally at the begining of everymonth the sub is called for the
monthly report.

By removing the DoEvent loop now I cant pause the macro until the
CommandButtonSendEmail button is clicked and cell M25 on the
Monthlytrack sheet is flagged true (the button has been clicked).

How can I do this otherwise? How can I present the report sheet and
wait for user confirmation by clicking on that button before
continuing with the reset?

Hi Jaf,

Didnt take me long to figure out why I needed to have a doevent in a
loop. :)

I need to stop the macro until the button is pressed to allow the user
to email the report before doing other tasks

I have two report sheets that need to be emailed, one is weekly and
the other is monthly.
At the beginning of every week the AUTO_OPEN sub on module1 calls this
sub and it was to wait until the button is clicked before continuing
to reset the report.
I also have placed a button on the weekly report itself that also
calls this sub as well in case user wants to email the report or
print.
And finally at the begining of everymonth the sub is called for the
monthly report.

By removing the DoEvent loop now I cant pause the macro until the
CommandButtonSendEmail button is clicked and cell M25 on the
Monthlytrack sheet is flagged true (the button has been clicked).

How can I do this otherwise? How can I present the report sheet and
wait for user confirmation by clicking on that button before
continuing with the reset?

Hi Jaf,

Didnt take me long to figure out why I needed to have a DoEvent in a
loop. :)

I need the the button to be pressed by the user.

I have two report sheets that need to be emailed, one is weekly and
the other is monthly.

At the beginning of every week the AUTO_OPEN sub on module1 calls
this
sub and it has to wait until the button is clicked before continuing
because it will reset the report.

I also have placed a button on the weekly report itself that also
calls this sub as well in case the user wants to email the report or
print it. (In this case your sugestion works fine because theres
nothing else to be done.)

And finally at the begining of every month the sub is called for the
monthly report and again it has to wait for user confirmation before
continuing and reset the monthly report.
And to make things more challenging sometimes the sub is called twice
in AUTO_OPEN because it is both the beginning of the week and month.

Also Auto_Open sets other flags that are used for the report
calculations after the report is/are reseted.

There got to be a way to tell Excel to pause the macro until the
resume button is pressed. :)
Any ideas?

Hi Jaf,

Didnt take me long to figure out why I needed to have a DoEvent in a
loop. :)

I need the the button to be pressed by the user.

I have two report sheets that need to be emailed, one is weekly and
the other is monthly.

At the beginning of every week the AUTO_OPEN sub on module1 calls
this
sub and it has to wait until the button is clicked before continuing
because it will reset the report.

I also have placed a button on the weekly report itself that also
calls this sub as well in case the user wants to email the report or
print it. (In this case your sugestion works fine because theres
nothing else to be done.)

And finally at the begining of every month the sub is called for the
monthly report and again it has to wait for user confirmation before
continuing and reset the monthly report.
And to make things more challenging sometimes the sub is called twice
in AUTO_OPEN because it is both the beginning of the week and month.

Also Auto_Open sets other flags that are used for the report
calculations after the report is/are reseted.

Theres got to be a way to tell Excel to pause the macro until the
resume button is pressed. :)
Any ideas?

Hi,
When Excel gets to the end of a sub it stops. Because it has no further
instructions.

Think of subs as separate macros. Each one will stop when it ends unless you
tell to go to another sub.
That is why you need an Event like a button click.

All you need to do is add the code you want to run after the email has been
sent to the button click sub or call a third sub from the button click sub
before it ends.

Don't think of the macro "continuing". Think of it firing off another sub.

An altogether different why to get this to work is a message box.

Response = MsgBox("Do you want to send report by email?", vbYesNoCancel)

If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If


John

wrote in message
...
Hi Jaf,

Didnt take me long to figure out why I needed to have a DoEvent in a
loop. :)

I need the the button to be pressed by the user.

I have two report sheets that need to be emailed, one is weekly and
the other is monthly.

At the beginning of every week the AUTO_OPEN sub on module1 calls
this
sub and it has to wait until the button is clicked before continuing
because it will reset the report.

I also have placed a button on the weekly report itself that also
calls this sub as well in case the user wants to email the report or
print it. (In this case your sugestion works fine because theres
nothing else to be done.)

And finally at the begining of every month the sub is called for the
monthly report and again it has to wait for user confirmation before
continuing and reset the monthly report.
And to make things more challenging sometimes the sub is called twice
in AUTO_OPEN because it is both the beginning of the week and month.

Also Auto_Open sets other flags that are used for the report
calculations after the report is/are reseted.

Theres got to be a way to tell Excel to pause the macro until the
resume button is pressed. :)
Any ideas?

That sounds like a major rewrite of code when six lines of code do the
same thing. :)

It also means spaghetti code because Auto_Open sub calls the button
sub it calls back auto_open which can call again the sub and then
again come back to Auto_open when a new week and new month fall on the
same day. :)