macro control

[dadad4]

does anyoneknow, is it possible to assign more than one macro to a single command button ?

 

[HalfAce]

Absolutely. There is more than one way.
One way is:
Say you have 3 macros, named Macro1, Macro2 & macro3. (Stored in one or more standard modules)
You can simply call these by name from the command button. (ie.)

Private Sub CommandButton1_Click()
Call Macro1
Call Macro2
Call Macro3
End Sub

Another way would be to simply combine the code of all 3 routines into the CommandButton's code. This may or may not be desireable, depending on the code of each routine.

If you need more help, you could post the code for each routine and someone here can no doubt help point you in the right direction.

Hope it helps,
Dan

 

[Tazguy37]

Sort of. (At least as far as I know.) Using the method outlined here, you could run a particular macro based on a cell's value (using validation, if you want.)

See Von Pookie's second post [ur=http://www.mrexcel.com/board2/viewtopic.php?t=110039l]here[/url].

Hope that helps!

 

[HalfAce]

Oh, thinking along the lines of how Taz interprets your question...
If you were using a Forms toolbar button, you can do something along the lines of this.

Sub OnOffButton()
ActiveSheet.Shapes("Button 1").Select 'Amend to suit
Select Case Selection.Characters.Text
    Case "On"
        Call Macro1
        ActiveSheet.Shapes("Button 1").Select
        Selection.Characters.Text = "Off"
    Case "Off"
        Call Macro2
        ActiveSheet.Shapes("Button 1").Select
        Selection.Characters.Text = "On"
    Case Else
End Select
SendKeys "{ESC}"
End Sub

In a standard module I have these:

Sub Macro1()
Application.ScreenUpdating = False
    ActiveSheet.Range("A1").Value = "Light is on."
    With ActiveSheet.Range("A1").Font
    .ColorIndex = 3
    End With
    With ActiveSheet.Range("A1").Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
Application.ScreenUpdating = True
End Sub

Sub Macro2()
Application.ScreenUpdating = False
    ActiveSheet.Range("A1").Value = "Light is off."
    With ActiveSheet.Range("A1").Font
    .ColorIndex = 0
    End With
    With ActiveSheet.Range("A1").Interior
        .ColorIndex = xlNone
    End With
Application.ScreenUpdating = True
End Sub

You just need to make sure the button's text says either On or Off to begin with.
Does that help?
Dan

 

[dadad4]

Thanks for your help half ace,
The following are the codes I’m trying to use
Code 1


Public RunWhen As Double
Public timer_value As Integer
Public Const cRunIntervalSeconds = 1 ' 1 second
Public Const cRunWhat = "The_Sub"


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=True
End Sub


Sub The_Sub()

'get the current timer value
timer_value = Range("timer").Value

'now increase "level now" if the timer has got down to Zero
If timer_value = 0 Then Range("level_now").Value = Range("level_now").Value + 1

'now reduce the timer by 1, or reset to the duration if it's on Zero
If timer_value = 0 Then Range("timer").Value = Range("duration").Value Else Range("timer").Value = timer_value - 1

'now beep in last ten seconds, first getting the new timer value
timer_value = Range("timer").Value
If timer_value > 0 And timer_value < 11 Then Beep

StartTimer

End Sub


Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
Range("timer").Value = Range("duration").Value
End Sub


Sub PauseTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
End Sub


Sub ResumeTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=True
End Sub


Code 2


Dim NextTime As Date
Sub Flash()
NextTime = Now + TimeValue("00:00:01")
With ActiveWorkbook.Styles("Flash").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
Application.OnTime NextTime, "Flash"
End Sub

Sub StopIt()
Application.OnTime NextTime, "Flash", schedule:=False
ActiveWorkbook.Styles("Flash").Font.ColorIndex = xlAutomatic
End Sub


I would like to get the FLASH macro to work on the START command, and the STOPIT to work on the resume command, is this possible ?

i have both of the working on seperate work books and would like to combine them.dadad.