2 codes in one sheet
in [Programming]
|
From: MAX on 27 Apr 2010 12:01 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
From: Bob Phillips on 27 Apr 2010 12:20 Just copy the code from one workbook to the other, and add the extra calls. -- HTH Bob "MAX" <MAX(a)discussions.microsoft.com> wrote in message news:D6738FB6-B795-4711-A211-B8B71C8A6BFE(a)microsoft.com... >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
From: ryguy7272 on 27 Apr 2010 12:23 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
From: MAX on 27 Apr 2010 13:23 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
From: Gord Dibben on 27 Apr 2010 13:47 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 <MAX(a)discussions.microsoft.com> 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
|
Next
|
Last
Pages: 1 2 Prev: Wrapping entries in a ListBox Next: VBA code to protect cells Outside a given range |