Pop Up and Audio alarm running as background tasks.

  • Thread starter Thread starter Gary Ainsworth
  • Start date Start date
G

Gary Ainsworth

Hi,
I am researching on how to create and implement a VBA app. to run in
the background and then 'alarm' when a certain time has elapsed and
then a certain temperature value has been reached. The sheet will be
updated by an add in for Aspen Process Explorer but I need help with
the following please:

1/ How can I get the program to run 'invisibly' as it were and then
make the app become active when not so. (is it appactivate?)
2/ What is the best way to achieve a 'pop up' and 'audio' alarms from
either the countdown timer or a value being matched?
3/ How can I stop the application from being closed down unauthorised.
(i.e. it needs to continue to run in the backround while other apps
are used.)

Thanks for any pointers
Regards
Gary

P.S.
I have had a search through the group but a little specific help would
be greatly appreciated.
 
http://support.microsoft.com/default.aspx?scid=kb;en-us;158140
XL: How to Play Sounds Using Visual Basic for Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;149777
XL: How to Play a Warning Sound If Critical Value Is Exceeded

http://support.microsoft.com/default.aspx?scid=kb;en-us;86281
HOWTO: Play a Waveform (.WAV) Sound File in Visual Basic
[Contains the Constants for the Arguments]


for scheduling code to run
http://www.cpearson.com/excel/ontime.htm

About events:

http://www.cpearson.com/excel.events.htm

Use the beforeclose event to cancel the close unless some specific condition
is met.

----------------------------
this code posted by Chip Pearson makes a userform come to the front:

Try the following, for Excel 2000 and later.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Sub ShowTheForm()
Dim FHwnd As Long
Load UserForm1
FHwnd = FindWindow("ThunderDFrame", UserForm1.Caption)
If FHwnd Then
SetForegroundWindow FHwnd
End If
UserForm1.Show
End Sub
 
Thanks for your help. Very useful indeed. Howevever, I am struggling
to get the macro to work. Basically it cannot find the macro. They are
both on the same sheet so why it cannot fond them I do not know. Both
macros are listed.

Thanks
Gazza

Public NextTime As Date
Public EndTime As Date


Sub StartCount()
EndTime = Now + TimeValue("00:00:15")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

Sub Continuecount()
Dim sh As Worksheet
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then
Set sh = ActiveSheet
Worksheets("something").Activate
sh.Visible = xlSheetHidden
' call code to continue process
Else
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End If
End Sub


Tom Ogilvy said:
http://support.microsoft.com/default.aspx?scid=kb;en-us;158140
XL: How to Play Sounds Using Visual Basic for Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;149777
XL: How to Play a Warning Sound If Critical Value Is Exceeded

http://support.microsoft.com/default.aspx?scid=kb;en-us;86281
HOWTO: Play a Waveform (.WAV) Sound File in Visual Basic
[Contains the Constants for the Arguments]


for scheduling code to run
http://www.cpearson.com/excel/ontime.htm

About events:

http://www.cpearson.com/excel.events.htm

Use the beforeclose event to cancel the close unless some specific condition
is met.

----------------------------
this code posted by Chip Pearson makes a userform come to the front:

Try the following, for Excel 2000 and later.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Sub ShowTheForm()
Dim FHwnd As Long
Load UserForm1
FHwnd = FindWindow("ThunderDFrame", UserForm1.Caption)
If FHwnd Then
SetForegroundWindow FHwnd
End If
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

--

Regards,
Tom Ogilvy


Gary Ainsworth said:
Hi,
I am researching on how to create and implement a VBA app. to run in
the background and then 'alarm' when a certain time has elapsed and
then a certain temperature value has been reached. The sheet will be
updated by an add in for Aspen Process Explorer but I need help with
the following please:

1/ How can I get the program to run 'invisibly' as it were and then
make the app become active when not so. (is it appactivate?)
2/ What is the best way to achieve a 'pop up' and 'audio' alarms from
either the countdown timer or a value being matched?
3/ How can I stop the application from being closed down unauthorised.
(i.e. it needs to continue to run in the backround while other apps
are used.)

Thanks for any pointers
Regards
Gary

P.S.
I have had a search through the group but a little specific help would
be greatly appreciated.
Click to expand...
Click to expand...
 
Do you really mean that they're in a worksheet module?

If yes, try moving all this code to a general module.



Gary said:
Thanks for your help. Very useful indeed. Howevever, I am struggling
to get the macro to work. Basically it cannot find the macro. They are
both on the same sheet so why it cannot fond them I do not know. Both
macros are listed.

Thanks
Gazza

Public NextTime As Date
Public EndTime As Date

Sub StartCount()
EndTime = Now + TimeValue("00:00:15")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

Sub Continuecount()
Dim sh As Worksheet
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then
Set sh = ActiveSheet
Worksheets("something").Activate
sh.Visible = xlSheetHidden
' call code to continue process
Else
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End If
End Sub

Tom Ogilvy said:
http://support.microsoft.com/default.aspx?scid=kb;en-us;158140
XL: How to Play Sounds Using Visual Basic for Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;149777
XL: How to Play a Warning Sound If Critical Value Is Exceeded

http://support.microsoft.com/default.aspx?scid=kb;en-us;86281
HOWTO: Play a Waveform (.WAV) Sound File in Visual Basic
[Contains the Constants for the Arguments]


for scheduling code to run
http://www.cpearson.com/excel/ontime.htm

About events:

http://www.cpearson.com/excel.events.htm

Use the beforeclose event to cancel the close unless some specific condition
is met.

----------------------------
this code posted by Chip Pearson makes a userform come to the front:

Try the following, for Excel 2000 and later.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Sub ShowTheForm()
Dim FHwnd As Long
Load UserForm1
FHwnd = FindWindow("ThunderDFrame", UserForm1.Caption)
If FHwnd Then
SetForegroundWindow FHwnd
End If
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

--

Regards,
Tom Ogilvy


Gary Ainsworth said:
Hi,
I am researching on how to create and implement a VBA app. to run in
the background and then 'alarm' when a certain time has elapsed and
then a certain temperature value has been reached. The sheet will be
updated by an add in for Aspen Process Explorer but I need help with
the following please:

1/ How can I get the program to run 'invisibly' as it were and then
make the app become active when not so. (is it appactivate?)
2/ What is the best way to achieve a 'pop up' and 'audio' alarms from
either the countdown timer or a value being matched?
3/ How can I stop the application from being closed down unauthorised.
(i.e. it needs to continue to run in the backround while other apps
are used.)

Thanks for any pointers
Regards
Gary

P.S.
I have had a search through the group but a little specific help would
be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave that fixed it. Can you explain when you should put the
code in a module or a worksheet please.

Dave Peterson said:
Do you really mean that they're in a worksheet module?

If yes, try moving all this code to a general module.



Gary said:
Thanks for your help. Very useful indeed. Howevever, I am struggling
to get the macro to work. Basically it cannot find the macro. They are
both on the same sheet so why it cannot fond them I do not know. Both
macros are listed.

Thanks
Gazza

Public NextTime As Date
Public EndTime As Date

Sub StartCount()
EndTime = Now + TimeValue("00:00:15")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

Sub Continuecount()
Dim sh As Worksheet
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then
Set sh = ActiveSheet
Worksheets("something").Activate
sh.Visible = xlSheetHidden
' call code to continue process
Else
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End If
End Sub

Tom Ogilvy said:
http://support.microsoft.com/default.aspx?scid=kb;en-us;158140
XL: How to Play Sounds Using Visual Basic for Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;149777
XL: How to Play a Warning Sound If Critical Value Is Exceeded

http://support.microsoft.com/default.aspx?scid=kb;en-us;86281
HOWTO: Play a Waveform (.WAV) Sound File in Visual Basic
[Contains the Constants for the Arguments]


for scheduling code to run
http://www.cpearson.com/excel/ontime.htm

About events:

http://www.cpearson.com/excel.events.htm

Use the beforeclose event to cancel the close unless some specific condition
is met.

----------------------------
this code posted by Chip Pearson makes a userform come to the front:

Try the following, for Excel 2000 and later.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Sub ShowTheForm()
Dim FHwnd As Long
Load UserForm1
FHwnd = FindWindow("ThunderDFrame", UserForm1.Caption)
If FHwnd Then
SetForegroundWindow FHwnd
End If
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

--

Regards,
Tom Ogilvy


Hi,
I am researching on how to create and implement a VBA app. to run in
the background and then 'alarm' when a certain time has elapsed and
then a certain temperature value has been reached. The sheet will be
updated by an add in for Aspen Process Explorer but I need help with
the following please:

1/ How can I get the program to run 'invisibly' as it were and then
make the app become active when not so. (is it appactivate?)
2/ What is the best way to achieve a 'pop up' and 'audio' alarms from
either the countdown timer or a value being matched?
3/ How can I stop the application from being closed down unauthorised.
(i.e. it needs to continue to run in the backround while other apps
are used.)

Thanks for any pointers
Regards
Gary

P.S.
I have had a search through the group but a little specific help would
be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I try to only put procedures in the Thisworkbook module and the sheet modules
that can be started by using those dropdowns on the top of the code window.

The workbook_open, worksheet_change type stuff.

(And code that is associated with controls from the ControlToolbox toolbar,
too.)



Gary said:
Thanks Dave that fixed it. Can you explain when you should put the
code in a module or a worksheet please.

Dave Peterson said:
Do you really mean that they're in a worksheet module?

If yes, try moving all this code to a general module.



Gary said:
Thanks for your help. Very useful indeed. Howevever, I am struggling
to get the macro to work. Basically it cannot find the macro. They are
both on the same sheet so why it cannot fond them I do not know. Both
macros are listed.

Thanks
Gazza

Public NextTime As Date
Public EndTime As Date

Sub StartCount()
EndTime = Now + TimeValue("00:00:15")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

Sub Continuecount()
Dim sh As Worksheet
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then
Set sh = ActiveSheet
Worksheets("something").Activate
sh.Visible = xlSheetHidden
' call code to continue process
Else
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End If
End Sub

http://support.microsoft.com/default.aspx?scid=kb;en-us;158140
XL: How to Play Sounds Using Visual Basic for Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;149777
XL: How to Play a Warning Sound If Critical Value Is Exceeded

http://support.microsoft.com/default.aspx?scid=kb;en-us;86281
HOWTO: Play a Waveform (.WAV) Sound File in Visual Basic
[Contains the Constants for the Arguments]


for scheduling code to run
http://www.cpearson.com/excel/ontime.htm

About events:

http://www.cpearson.com/excel.events.htm

Use the beforeclose event to cancel the close unless some specific condition
is met.

----------------------------
this code posted by Chip Pearson makes a userform come to the front:

Try the following, for Excel 2000 and later.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Sub ShowTheForm()
Dim FHwnd As Long
Load UserForm1
FHwnd = FindWindow("ThunderDFrame", UserForm1.Caption)
If FHwnd Then
SetForegroundWindow FHwnd
End If
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

--

Regards,
Tom Ogilvy


Hi,
I am researching on how to create and implement a VBA app. to run in
the background and then 'alarm' when a certain time has elapsed and
then a certain temperature value has been reached. The sheet will be
updated by an add in for Aspen Process Explorer but I need help with
the following please:

1/ How can I get the program to run 'invisibly' as it were and then
make the app become active when not so. (is it appactivate?)
2/ What is the best way to achieve a 'pop up' and 'audio' alarms from
either the countdown timer or a value being matched?
3/ How can I stop the application from being closed down unauthorised.
(i.e. it needs to continue to run in the backround while other apps
are used.)

Thanks for any pointers
Regards
Gary

P.S.
I have had a search through the group but a little specific help would
be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top