Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro to Activate a Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Macro to Activate a Macro
Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Macro to Activate a Macro
Use CAll to call another procedure. The below code from Macro1 will call
Macro2.

Call Macro2

If this post helps click Yes
---------------
Jacob Skaria


"Ejensen" wrote:

Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Macro to Activate a Macro
1) To get the nearest 15 minute time period use code below

CurrentTime = Time

MyTime = WorksheetFunction.Floor(CurrentTime, TimeValue("00:15"))
'round up if greater than = .5
If (CurrentTime / TimeValue("00:15")) = 0.5 Then
MyTime = MyTime + TimeValue("00:15")
End If

MyTimeStr = Format(MyTime, "HHMMAM/PM")


2) You can use inputbox to get user input. Inputbox only returns 1 value so
you will need 2 inputboxes to get my than one response. You could use a
userorm to allow the user to get more than one value at a time. This is a
litle complicated.

3) To call 132 subroutines you will need 132 Call Statements. I would build
a string of the Names of the calls and then use a Select Case statement to
make the code easier to write

'use code from above here

MyHours = InputBox("Enter Hours")
MyMinutes = Inputbox("Enter Minutes")

if MyMinutes = 0 then
SubName = "Hours" & MyHours & "time" & MyTimeStr
else
SubName = "Hours" & MyHours & "min" & MyMinutes & "time" & MyTimeStr
end if

select case SubName

case "Hours2min15time530am"
call Hours2min15time530am

Case "Hours3time530am"
call Hours3time530am

Case "Hours2min30time530am"
call Hours2min30time530am

Case "Hours2min45time530am"
call Hours2min45time530am

Case "Hour2time530am"
call Hour2time530am
end select

Hour1min45time530am
Hour1min45time530am

"Jacob Skaria" wrote:

Use CAll to call another procedure. The below code from Macro1 will call
Macro2.

Call Macro2

If this post helps click Yes
---------------
Jacob Skaria


"Ejensen" wrote:

Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
r r is offline
external usenet poster
  
Posts: 125
Default Macro to Activate a Macro

132 similar routine can be avoided by passing variable arguments to routine
I would start from this ...
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ejensen" wrote:

Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Macro to Activate a Macro
Thanks for the input.

"Jacob Skaria" wrote:

Use CAll to call another procedure. The below code from Macro1 will call
Macro2.

Call Macro2

If this post helps click Yes
---------------
Jacob Skaria


"Ejensen" wrote:

Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Macro to Activate a Macro
Thank you for spelling it out for me. I really appreciate your help very much.

"Joel" wrote:

1) To get the nearest 15 minute time period use code below

CurrentTime = Time

MyTime = WorksheetFunction.Floor(CurrentTime, TimeValue("00:15"))
'round up if greater than = .5
If (CurrentTime / TimeValue("00:15")) = 0.5 Then
MyTime = MyTime + TimeValue("00:15")
End If

MyTimeStr = Format(MyTime, "HHMMAM/PM")


2) You can use inputbox to get user input. Inputbox only returns 1 value so
you will need 2 inputboxes to get my than one response. You could use a
userorm to allow the user to get more than one value at a time. This is a
litle complicated.

3) To call 132 subroutines you will need 132 Call Statements. I would build
a string of the Names of the calls and then use a Select Case statement to
make the code easier to write

'use code from above here

MyHours = InputBox("Enter Hours")
MyMinutes = Inputbox("Enter Minutes")

if MyMinutes = 0 then
SubName = "Hours" & MyHours & "time" & MyTimeStr
else
SubName = "Hours" & MyHours & "min" & MyMinutes & "time" & MyTimeStr
end if

select case SubName

case "Hours2min15time530am"
call Hours2min15time530am

Case "Hours3time530am"
call Hours3time530am

Case "Hours2min30time530am"
call Hours2min30time530am

Case "Hours2min45time530am"
call Hours2min45time530am

Case "Hour2time530am"
call Hour2time530am
end select

Hour1min45time530am
Hour1min45time530am

"Jacob Skaria" wrote:

Use CAll to call another procedure. The below code from Macro1 will call
Macro2.

Call Macro2

If this post helps click Yes
---------------
Jacob Skaria


"Ejensen" wrote:

Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default Macro to Activate a Macro
Thank you for the feedback.

"r" wrote:


132 similar routine can be avoided by passing variable arguments to routine
I would start from this ...
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Ejensen" wrote:

Sub Hours3time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!RC"
End Sub

Sub Hours2min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[4]C"
End Sub

Sub Hours2min30time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[8]C"
End Sub

Sub Hours2min15time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[12]C"
End Sub

Sub Hour2time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[16]C"
End Sub
Sub Hour1min45time530am()
ActiveSheet.ChartObjects("Chart 302").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.229166666666667
.MaximumScale = 0.354166666666667
.MinorUnit = 0.00347222222
.MajorUnit = 0.00347222222
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveWindow.Visible = False
Windows("Time Tool.xls").Activate
Range("B21").Select
Range("B3").FormulaR1C1 = "5:30:00 AM"
Range("B4:L4").FormulaR1C1 = "=Reference!R[20]C"
End Sub

I have 132 macros; I pasted 6 of them above. I am looking for help with a
macro that I can use to activate one of the 132 macros I have at a time
depending on the time of day.

More specifically I am searching for a code that will get activated and
cause a message box to appear when I open the excel doc, but can still be
activated after the doc has been opened.

If this is possible to do, the message box would have.
- Message €œThe Current Time is (Macro puts current computer time here)€
- Question €œHow much time do you have?€ then beneath this question
- Blank field labeled Hour to give answer
- Blank field labeled Minute to give answer

Depending on the current time on the computer as well as the hour and minute
entered, a macro with a matching title (each macro includes a time, hour, and
minute in its Sub name) a code in VBA will be selected that fits that
criterion. For example, lets say it is 5:30am and I entered 2 hr and 30 min
in the empty fields of the message box. As a result the macro named €œSub
Hour2Min30time530am€ will be activated. Also due to the fact that the macros
I have are in 15 min intervals the code would need to round to the nearest
time otherwise it won't always work, so if it was 5:33am when I ran the code
the macro would default to 5:30am whereas if it was 5:39am when I ran the
code the macro would default to 5:45am. Is this possible to do and if so
could you help me with it? Thank you for any of your help/input.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
activate macro Jase Excel Discussion (Misc queries) 1 June 9th 08 08:25 PM
Activate MS Outlook in macro? Ivan Excel Programming 3 May 25th 06 01:05 PM
activate macro only in sheet2 Narkom Excel Worksheet Functions 3 March 20th 06 09:21 PM
activate a cell value through a macro TUNGANA KURMA RAJU Excel Discussion (Misc queries) 1 November 10th 05 08:48 AM
Formula to activate macro coal_miner Excel Worksheet Functions 5 September 20th 05 04:56 AM


All times are GMT +1. The time now is 03:04 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"

 

Contact Us ExcelBanter forum home Privacy Statement