Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If 0, count various codes in a range on another sheet | Excel Worksheet Functions | |||
Combine two Codes into one Sheet | Excel Programming | |||
I create sheet using vb codes after that i want change sheet name | Excel Programming | |||
Changing of Sheet Codes for the worksheets... | Excel Programming | |||
Copy sheet and strip all vba codes | Excel Programming |