ExcelBanter - 2 codes in one sheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - 2 codes in one sheet (https://www.excelbanter.com/excel-programming/442078-2-codes-one-sheet.html)

Max

2 codes in one sheet

I have these codes (below) in 2 seperate sheets. Is it possible to use these
codes in one sheet?
If yes, where and how I have to put them?

These are the 2 codes:

1st Code:

In Workbook:
-------------------
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
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++

2nd Code:

In Workbook:
-------------------
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

Bob Phillips[_4_]

2 codes in one sheet

Just copy the code from one workbook to the other, and add the extra calls.

--

HTH

Bob

"MAX" wrote in message
...
I have these codes (below) in 2 seperate sheets. Is it possible to use
these
codes in one sheet?
If yes, where and how I have to put them?

These are the 2 codes:

1st Code:

In Workbook:
-------------------
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
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++

2nd Code:

In Workbook:
-------------------
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

ryguy7272

2 codes in one sheet

Hummmm, I've never called a sub from Worksheet_Open, but I guess it can be
done. Like this:
Sub Macro1
'code
'code
'code
Macro2 'Calling Macro2
End Sub

Sub Macro2
'code
'code
'code
End Sub

In your case:

Private Sub Workbook_Open()
StartBlink1

Call StartBlink1
End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"MAX" wrote:

I have these codes (below) in 2 seperate sheets. Is it possible to use these
codes in one sheet?
If yes, where and how I have to put them?

These are the 2 codes:

1st Code:

In Workbook:
-------------------
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
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++

2nd Code:

In Workbook:
-------------------
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

Max

2 codes in one sheet

Thank you for your answers but I am not understanding since I am a very
beginner. Will you please explain to me in an easier way by show me what to
write in WORKBOOK and in MODULE.

Thanks in advance

"ryguy7272" wrote:

Hummmm, I've never called a sub from Worksheet_Open, but I guess it can be
done. Like this:
Sub Macro1
'code
'code
'code
Macro2 'Calling Macro2
End Sub

Sub Macro2
'code
'code
'code
End Sub

In your case:

Private Sub Workbook_Open()
StartBlink1

Call StartBlink1
End Sub

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"MAX" wrote:

I have these codes (below) in 2 seperate sheets. Is it possible to use these
codes in one sheet?
If yes, where and how I have to put them?

These are the 2 codes:

1st Code:

In Workbook:
-------------------
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
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++

2nd Code:

In Workbook:
-------------------
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

Gord Dibben

2 codes in one sheet

I would assume by "separate sheets" you mean separate workbooks because you
cannot have two Workbook_Open events in one workbook.

Do you want to combine the two sets of code into one set of code in a single
workbook?

I note your "Flashcell" macro has no qualifying worksheet.

Gord Dibben MS Excel MVP

On Tue, 27 Apr 2010 09:01:01 -0700, MAX
wrote:

I have these codes (below) in 2 seperate sheets. Is it possible to use these
codes in one sheet?
If yes, where and how I have to put them?

These are the 2 codes:

1st Code:

In Workbook:
-------------------
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
+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++

2nd Code:

In Workbook:
-------------------
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

Max

2 codes in one sheet

Yes I want to combine the two sets of code into one set of code in a single
workbook.

Thank you

"Gord Dibben" wrote:

I would assume by "separate sheets" you mean separate workbooks because you
cannot have two Workbook_Open events in one workbook.

Do you want to combine the two sets of code into one set of code in a single
workbook?

I note your "Flashcell" macro has no qualifying worksheet.

Gord Dibben MS Excel MVP

On Tue, 27 Apr 2010 09:01:01 -0700, MAX
wrote:

I have these codes (below) in 2 seperate sheets. Is it possible to use these
codes in one sheet?
If yes, where and how I have to put them?

These are the 2 codes:

1st Code:

In Workbook:
-------------------
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
+++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++

2nd Code:

In Workbook:
-------------------
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

.

All times are GMT +1. The time now is 12:25 PM.