macro control

dadad4

New Member
Joined
Nov 3, 2004
Messages
20
does anyoneknow, is it possible to assign more than one macro to a single command button ?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.)
Code:
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

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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:
Code:
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

New Member
Joined
Nov 3, 2004
Messages
20
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.
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top