Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Max Max is offline
external usenet poster
 
Posts: 390
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 834
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,836
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
Max Max is offline
external usenet poster
 
Posts: 390
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22,906
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.programming
Max Max is offline
external usenet poster
 
Posts: 390
Default 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


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
If 0, count various codes in a range on another sheet Steve Excel Worksheet Functions 8 January 8th 10 10:34 PM
Combine two Codes into one Sheet Steved Excel Programming 2 May 19th 08 02:29 AM
I create sheet using vb codes after that i want change sheet name Parthiban Excel Programming 1 February 21st 08 06:51 AM
Changing of Sheet Codes for the worksheets... RV Excel Programming 3 February 4th 08 03:14 PM
Copy sheet and strip all vba codes helmekki[_55_] Excel Programming 0 November 10th 04 04:38 AM


All times are GMT +1. The time now is 12:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"