Prev: Column Width
Next: Freeze the Title in Excel
From: MAX on 1 May 2010 07:09
I have these two codes (below) in two seperate workbooks. Is it possible to
combine these two codes in one set of code in a single workbook, since
someone told me that I cannot have two Workbook_Open events in one workbook.
Just to tell you that I am a very beginner in programming.

Code 1:

In ThisWorkbook:
-------------------
Private Sub Workbook_Open()
StartBlink1

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlink1

End Sub

In Module:
--------------
Public RunWhen As Double

Sub StartBlink1()
With ThisWorkbook.Worksheets("Champions League").Range("A2").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 2 ' White Text
Else
.ColorIndex = 3 ' Red Text
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink1", , True
End Sub

Sub StopBlink1()
ThisWorkbook.Worksheets("CHAMPIONS LEAGUE ").Range("A2").Font.ColorIndex
= _
xlColorIndexAutomatic
Application.OnTime RunWhen, "StartBlink1", , False
End Sub

Code 2.

In ThisWorkbook:
-------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
stopFlashing
End Sub

Private Sub Workbook_Open()
startFlashing
End Sub

In Module:
--------------
Option Explicit
Dim nextSecond

Sub startFlashing()
flashCell
End Sub

Sub stopFlashing()
On Error Resume Next
Application.OnTime nextSecond, "flashCell", , False
End Sub

Sub flashCell()
nextSecond = Now + TimeValue("00:00:01")
Application.OnTime nextSecond, "flashCell"


If Range("A114").Interior.ColorIndex = 3 Then
Range("A114").Interior.ColorIndex = 5
Range("A114").Value = IIf(Range("K112") = Range("L112"),
Range("A108"), IIf(Range("K112") > Range("L112"), Range("E111"),
Range("H111")))
ElseIf Range("A114").Interior.ColorIndex = 5 Then
Range("A114").Interior.ColorIndex = 3
Range("A114").Value = "CHECKED"
End If
End Sub

Any help is appriciated, thanks.
From: JLatham on 1 May 2010 13:04
MAX, they are correct in saying that you can only have one Workbook_Open()
event in a workbook. But each workbook you use may have its own
Workbook_Open() event which may or may not do the same type of action.

There's no rule that says you couldn't have code like this in a workbook's
_Open() event:
Private Sub Workbook_Open()
StartBlink1
startFlashing
End Sub

But in your two routines, you'd need to be very explicit in stating what
worksheet's and ranges should be affected. The StartBlink1 is using very
explicit identification of the workbook and worksheet to be affected; right
now the startFlashing, or more accurately, the flashCell() routine is not
being explicit and would affect which ever sheet happens to be the active
sheet when it is called, and during the use of the workbook, you might work
with several different sheets, so you'd get unexpected actions as you chose a
sheet and the startFlashing/flashCell routines were called.

But if the two workbooks are just to be opened at the same time, and remain
as 2 different workbooks, each workbook having it's own Workboo_Open()
routine is not an issue. But you'd still want to change the flashCell code
to specify "ThisWorkbook." and what worksheet within that workbook to to
affect.


"MAX" wrote:

> I have these two codes (below) in two seperate workbooks. Is it possible to
> combine these two codes in one set of code in a single workbook, since
> someone told me that I cannot have two Workbook_Open events in one workbook.
> Just to tell you that I am a very beginner in programming.
>
> Code 1:
>
> In ThisWorkbook:
> -------------------
> Private Sub Workbook_Open()
> StartBlink1
>
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> StopBlink1
>
> End Sub
>
> In Module:
> --------------
> Public RunWhen As Double
>
> Sub StartBlink1()
> With ThisWorkbook.Worksheets("Champions League").Range("A2").Font
> If .ColorIndex = 3 Then ' Red Text
> .ColorIndex = 2 ' White Text
> Else
> .ColorIndex = 3 ' Red Text
> End If
> End With
> RunWhen = Now + TimeSerial(0, 0, 1)
> Application.OnTime RunWhen, "StartBlink1", , True
> End Sub
>
> Sub StopBlink1()
> ThisWorkbook.Worksheets("CHAMPIONS LEAGUE ").Range("A2").Font.ColorIndex
> = _
> xlColorIndexAutomatic
> Application.OnTime RunWhen, "StartBlink1", , False
> End Sub
>
> Code 2.
>
> In ThisWorkbook:
> -------------------
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> stopFlashing
> End Sub
>
> Private Sub Workbook_Open()
> startFlashing
> End Sub
>
> In Module:
> --------------
> Option Explicit
> Dim nextSecond
>
> Sub startFlashing()
> flashCell
> End Sub
>
> Sub stopFlashing()
> On Error Resume Next
> Application.OnTime nextSecond, "flashCell", , False
> End Sub
>
> Sub flashCell()
> nextSecond = Now + TimeValue("00:00:01")
> Application.OnTime nextSecond, "flashCell"
>
>
> If Range("A114").Interior.ColorIndex = 3 Then
> Range("A114").Interior.ColorIndex = 5
> Range("A114").Value = IIf(Range("K112") = Range("L112"),
> Range("A108"), IIf(Range("K112") > Range("L112"), Range("E111"),
> Range("H111")))
> ElseIf Range("A114").Interior.ColorIndex = 5 Then
> Range("A114").Interior.ColorIndex = 3
> Range("A114").Value = "CHECKED"
> End If
> End Sub
>
> Any help is appriciated, thanks.
 | 
Pages: 1
Prev: Column Width
Next: Freeze the Title in Excel