Combining Modules

[Carlton Patterson]

Hello all,

Can someone show me how combine the following modules into one module.

MODULE ONE

Sub Flash()
cTimes = cTimes + 1
If cTimes >= 10 Then
With ActiveWorkbook.Styles("Flash")
.Font.ColorIndex = xlColorIndexAutomatic
End With
Else
nStart = Now + TimeValue("00:00:01")
With ActiveWorkbook.Styles("Flash")
If .Font.ColorIndex = 3 Then
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone
Else
.Font.ColorIndex = 3
.Interior.ColorIndex = 19
.Interior.Pattern = xlSolid
End If
End With
Application.OnTime nStart, "Flash"
End If
End Sub


MODULE TWO

Sub Flash2()
cTimes = cTimes + 1
If cTimes >= 10 Then
With ActiveWorkbook.Styles("Flash")
.Font.ColorIndex = xlColorIndexAutomatic
End With
Else
nStart = Now + TimeValue("00:00:01")
With ActiveWorkbook.Styles("Flash")
If .Font.ColorIndex = 3 Then
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone
Else
.Font.ColorIndex = 3
.Interior.ColorIndex = 19
.Interior.Pattern = xlSolid
End If
End With
Application.OnTime nStart, "Flash2"
End If
End Sub


Cheers

Carlton



*** Sent via Developersdex http://www.developersdex.com ***

 

[Carlton Patterson]

Alternatively,

Could someone please show me how to improve on the following code so
that "H9" and "H12" don't have to reference different modules - in this
case,FLASH and FLASH2?

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

 

[Bob Phillips]

Sub Flash()
cTimes = cTimes + 1
If cTimes >= 10 Then
With ActiveWorkbook.Styles("Flash")
.Font.ColorIndex = xlColorIndexAutomatic
End With
Else
nStart = Now + TimeValue("00:00:01")
With ActiveWorkbook.Styles("Flash")
If .Font.ColorIndex = 3 Then
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone
Else
.Font.ColorIndex = 3
.Interior.ColorIndex = 19
.Interior.Pattern = xlSolid
End If
End With
Application.OnTime nStart, "Flash"
Application.OnTime nStart, "Flash2"
End If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

could be simpler..

dtFlash must be module level to allow unschedule on close.
nFlash is procedure level, but static so it retains value between runs.

Option Explicit

Dim dtFlash As Date

Sub Flash()
Static nFlash As Integer

With ActiveWorkbook.Styles("Flash")
If .Font.ColorIndex = 3 Then
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexNone
Else
.Font.ColorIndex = 3
.Interior.ColorIndex = 19
.Interior.Pattern = xlSolid
End If
End With

'Reschedule or reset
If nFlash > 10 Then
nFlash = 0
dtFlash = 0
Else
nFlash = nFlash + 1
dtFlash = Now + TimeValue("00:00:01")
Application.OnTime dtFlash, "Flash"
End If

End Sub

Sub Auto_close()
If dtFlash > 0 Then Application.OnTime dtFlash, "Flash", , False
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

 

[Carlton Patterson]

Thanks guys,

I didn't realise that anyone had responded to my request to combine
modules.

I will check it out and let you know how I get on.

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

 

[Carlton Patterson]

KeepItCool,

I have just attempted to run your program, however I'm getting a compile
error saying the following variable is not defined:

dtflash = 0

Can you let me know where I would need to define it?

Cheers

Carlton

*** Sent via Developersdex http://www.developersdex.com ***

 

[keepITcool]

probably you forgot to create the variable.
it must be in the delcaration section at the top of the module
look at my code and you'll see it there (above the procedure)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Carlton Patterson wrote :