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.