Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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) "Carlton Patterson" wrote in message ... 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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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 : 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Modules
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 : 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Modules | Excel Discussion (Misc queries) | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
When to code in sheet or userform modules and when to use modules | Excel Programming | |||
Combining Modules | Excel Programming | |||
Class Modules vs Modules | Excel Programming |