Macro to Activate a Macro

E

Ejensen

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, let’s 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.
 
J

Jacob Skaria

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 said:
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, let’s 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.
Click to expand...
 
J

Joel

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 said:
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 said:
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, let’s 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.
Click to expand...
Click to expand...
 
R

r

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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


Ejensen said:
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, let’s 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.
Click to expand...
 
E

Ejensen

Thanks for the input.

Jacob Skaria said:
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 said:
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, let’s 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.
Click to expand...
Click to expand...
 
E

Ejensen

Thank you for spelling it out for me. I really appreciate your help very much.

Joel said:
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 said:
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 said:
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, let’s 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.
Click to expand...
Click to expand...
Click to expand...
 
E

Ejensen

Thank you for the feedback.

r said:
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/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html


Ejensen said:
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, let’s 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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro language 2
ScaleType error 7
secondary Y axis 1
Chart Creation using VBA 1
Graphing 2
How to code these VBA lines in Applescript? 0
Trying to Learn Code to Copy Charts 2
Macro -- repetitive tasks 1

Top