Macro to slightly modify the subroutine

[ucanalways]

I have a command button in Sheet3. The code in the backend for this
command button is
Call command1

When the user clicks the button, Sheet3 will copied and renamed as
Sheet4. So, all the objects of sheet3 including the commnand button
will be copied to sheet4. But, I would like to change the code for
this button in sheet4 as
Call command2 instead of being Call command1

Similarly, when the user clicks the button again in sheet3, it will be
copied as sheet5 and the command button should have code
Call command3 instead of being Call command1

Is there any macro that can modify the subroutine slightly? Please let
me know. Thanks

 

[Bill Renaud]

I think this is a very dangerous concept. Adding or modifying code in a
workbook that is already running a macro can cause Excel to crash or
completely lock up.

Maybe if you post your code for Command1, we can help you make it generic,
so that it will work on all worksheets. There is nothing wrong with having
multiple Forms buttons in a workbook that all call the same macro. The
macro just needs to start by setting an object variable to the active
sheet, as follows:

Sub DoStuff()
Dim wsActive as Worksheet

Set wsActive = ActiveSheet

'Perform your actions here.
End Sub

 

[ucanalways]

I think this is a very dangerous concept. Adding or modifying code in a
workbook that is already running a macro can cause Excel to crash or
completely lock up.

Maybe if you post your code for Command1, we can help you make it generic,
so that it will work on all worksheets. There is nothing wrong with having
multiple Forms buttons in a workbook that all call the same macro. The
macro just needs to start by setting an object variable to the active
sheet, as follows:

Sub DoStuff()
Dim wsActive as Worksheet

Set wsActive = ActiveSheet

'Perform your actions here.
End Sub

Bill, infact I am using activesheet concept. My code is given below..
myformula1 and myformula2 correspond to column A of sheet1.. I want
sheet3 to have this formula but
sheet4 to correspond to column B of sheet1,
sheet5 to correspond to column C of sheet1,
sheet6 to correspond to column D of sheet1... How would I do that? Any
idea on this please? Would select case work? Please let me know..
Thanks

Public Sub command2()
Dim wsActive As Worksheet

Set wsActive = ActiveSheet
Dim shp As Shape
For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.Delete
End If

Next shp

Dim i As Double
Dim j As Double


Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=wsActive.Range("H4"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection.NewSeries

i = wsActive.Range("F4").Value

j = i + 3

With wsActive
ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(j, 1))
End With
With wsActive
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, 2), .Cells(j, 2))
End With
'With wsActive '====> Error here so commented the the following
two lines... Rest works well.
'Range("F6").Value =
Application.WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(j, 2)))
'End With

ActiveChart.SeriesCollection(1).name = wsActive.Range("A1").Value
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet3"
'must be made generic later


'Dim shp As Shape
For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.IncrementLeft -47.25
shp.IncrementTop -1.5
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.00"
End If


Next shp
End Sub

 

[Bill Renaud]

<<...myformula1 and myformula2 correspond to column A of sheet1...>>

Where are "myformula1" and "myformula2" in your code?

Also, what to you mean by "to correspond to" in your post?

I made a few slight revisions in your code (mostly to use With statements).
The statement to set Range("F6") to a Sum was missing a period in front of
Range. You still need to take "Sheet3" out somehow, but I don't have your
data, so that I can run the macro to see what it does exactly.

I notice that you delete all of the embedded charts, then basically rebuild
them (or at least rebuild 1 of them). I wonder if that's really necessary.

'----------------------------------------------------------------------
Public Sub command2()
Dim wsActive As Worksheet
Dim shp As Shape
Dim i As Double
Dim j As Double

Set wsActive = ActiveSheet

For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.Delete
End If
Next shp

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=wsActive.Range("H4"), _
PlotBy:=xlColumns
.SeriesCollection.NewSeries
End With 'ActiveChart

With wsActive
i = .Range("F4").Value
j = i + 3

ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(j, 1))
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, 2), .Cells(j, 2))
.Range("F6").Value = Application.WorksheetFunction _
.Sum(.Range(.Cells(2, 2), .Cells(j, 2)))
End With 'wsActive

With ActiveChart
.SeriesCollection(1).Name = wsActive.Range("A1").Value
.Legend.Delete
.Location Where:=xlLocationAsObject, Name:="Sheet3"
End With 'ActiveChart
'must be made generic later

For Each shp In wsActive.Shapes
If shp.Type = msoChart _
Then
With shp
.IncrementLeft -47.25
.IncrementTop -1.5
.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = "0.00"
End With 'shp
End If
Next shp
End Sub

 

[ucanalways]

<<...myformula1 and myformula2 correspond to column A of sheet1...>>

Where are "myformula1" and "myformula2" in your code?

Also, what to you mean by "to correspond to" in your post?

I made a few slight revisions in your code (mostly to use With statements).
The statement to set Range("F6") to a Sum was missing a period in front of
Range. You still need to take "Sheet3" out somehow, but I don't have your
data, so that I can run the macro to see what it does exactly.

I notice that you delete all of the embedded charts, then basically rebuild
them (or at least rebuild 1 of them). I wonder if that's really necessary.

'----------------------------------------------------------------------
Public Sub command2()
Dim wsActive As Worksheet
Dim shp As Shape
Dim i As Double
Dim j As Double

Set wsActive = ActiveSheet

For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.Delete
End If
Next shp

Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=wsActive.Range("H4"), _
PlotBy:=xlColumns
.SeriesCollection.NewSeries
End With 'ActiveChart

With wsActive
i = .Range("F4").Value
j = i + 3

ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(j, 1))
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, 2), .Cells(j, 2))
.Range("F6").Value = Application.WorksheetFunction _
.Sum(.Range(.Cells(2, 2), .Cells(j, 2)))
End With 'wsActive

With ActiveChart
.SeriesCollection(1).Name = wsActive.Range("A1").Value
.Legend.Delete
.Location Where:=xlLocationAsObject, Name:="Sheet3"
End With 'ActiveChart
'must be made generic later

For Each shp In wsActive.Shapes
If shp.Type = msoChart _
Then
With shp
.IncrementLeft -47.25
.IncrementTop -1.5
.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = "0.00"
End With 'shp
End If
Next shp
End Sub

Bill, Thank you for that revision. I gave you the code for subroutine
command2 in my previous reply. And you are right, it does not have
myformula1 and myformula2. Given below is the code for command1 which
has myformula1 and myformula2. Basically, this code is very similar to
the one in command2. It has just few additional lines. I have
incorporated you revision in this code.

To answer your "correspond to" question, I mean that if the sheet name
is sheet3 then
myformula1 = "=min(Sheet1!A:A)"
myformula2 = "=max(Sheet1!A:A)"

If the sheet name is sheet4 then
myformula1 = "=min(Sheet1!B:B)"
myformula2 = "=max(Sheet1!B:B)"

If the sheet name is sheet5 then
myformula1 = "=min(Sheet1!C:C)"
myformula2 = "=max(Sheet1!C:C)"

etc... How to make this generic as you made the rest of the code
generic using wsactive?

Also, is it possible to replace
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet3"
with something like
ActiveChart.Location Where:=xlLocationAsObject, wsactive

I hope I have answered your questions.

***************************************
Public Sub command1()
Dim myformula1 As String
Dim myformula2 As String
Dim myformula3 As String
Dim wsActive As Worksheet

Set wsActive = ActiveSheet

myformula1 = "=min(Sheet1!A:A)"
myformula2 = "=max(Sheet1!A:A)"
Range("F2").Value = myformula1
Range("F3").Value = myformula2
Range("F4").Value = 20

Dim shp As Shape
For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.Delete
End If

Next shp

Dim i As Double
Dim j As Double


Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=wsActive.Range("H4"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection.NewSeries

With wsActive
i = .Range("F4").Value
j = i + 3

ActiveChart.SeriesCollection(1).XValues = _
.Range(.Cells(2, 1), .Cells(j, 1))
ActiveChart.SeriesCollection(1).Values = _
.Range(.Cells(2, 2), .Cells(j, 2))
.Range("F6").Value = Application.WorksheetFunction _
.Sum(.Range(.Cells(2, 2), .Cells(j, 2)))
End With 'wsActive


ActiveChart.SeriesCollection(1).name = wsActive.Range("A1").Value
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet3"
'must be made generic later


'Dim shp As Shape
For Each shp In wsActive.Shapes
If shp.Type = msoChart Then
shp.IncrementLeft -47.25
shp.IncrementTop -1.5
shp.ScaleWidth 1.6, msoFalse, msoScaleFromTopLeft
shp.ScaleHeight 1.46, msoFalse, msoScaleFromTopLeft
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.00"
End If


Next shp
End Sub

 

[Bill Renaud]

Try this for the top part of your code:

Dim wsActive As Worksheet
Dim myformula1 As String
Dim myformula2 As String

Set wsActive = ActiveSheet

Select Case wsActive.Name
Case "Sheet3"
myformula1 = "=min(Sheet1!A:A)"
myformula2 = "=max(Sheet1!A:A)"
Case "Sheet4"
myformula1 = "=min(Sheet1!B:B)"
myformula2 = "=max(Sheet1!B:B)"
Case "Sheet5"
myformula1 = "=min(Sheet1!C:C)"
myformula2 = "=max(Sheet1!C:C)"
Case Else
'Error message and exit here?
End Select

The line:
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet3"

....would become:
ActiveChart.Location Where:=xlLocationAsObject, name:=wsActive.Name

 

[ucanalways]

Try this for the top part of your code:

Dim wsActive As Worksheet
Dim myformula1 As String
Dim myformula2 As String

Set wsActive = ActiveSheet

Select Case wsActive.Name
Case "Sheet3"
myformula1 = "=min(Sheet1!A:A)"
myformula2 = "=max(Sheet1!A:A)"
Case "Sheet4"
myformula1 = "=min(Sheet1!B:B)"
myformula2 = "=max(Sheet1!B:B)"
Case "Sheet5"
myformula1 = "=min(Sheet1!C:C)"
myformula2 = "=max(Sheet1!C:C)"
Case Else
'Error message and exit here?
End Select

The line:
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet3"

...would become:
ActiveChart.Location Where:=xlLocationAsObject, name:=wsActive.Name

Bill, I tried it for 3 select cases. The concept looks works great..
I have 122 sheets. So, I am planning to have 120 select cases.

I have another question. For example, sheet4, sheet5 and sheet6 are
inserted and then deleted, the next inserting sheet is usually named
sheet7. Is there any way to get it automatically named as sheet4
instead of sheet7? Please let me know. Thanks

 

[Bill Renaud]

<<I have 122 sheets. So, I am planning to have 120 select cases.>>

WOW!!! I didn't realize that you have so many sheets! We may need to
re-think this concept a little bit.

<<I have another question. For example, sheet4, sheet5 and sheet6 are
inserted and then deleted, the next inserting sheet is usually named
sheet7. Is there any way to get it automatically named as sheet4
instead of sheet7?>>

Again, back to my reply above. You may need to name all of your sheets in
a very systematic way, so we can use a macro that has a For loop in it
somehow. This brings to mind some questions:

1. After Sheet7 is renamed to Sheet4 (to replace the original Sheet4, are
the formulas the same as the original Sheet4, or are they different?

2. Let's consider renaming Sheet1 and Sheet2 to something else (i.e.
"Summary", "Scratchpad", etc.). Then all of the sheets that will
"correspond to" (your original terminology) Sheet1 (now renamed) can be
renamed to some constant string with a sequential number appended on the
end (i.e. "DataPlot001", "DataPlot002", etc.). Then we can develop a single
macro that will update all sheets (and maybe rename them) all at once. Is
this possible?

3. Why are some sheets being deleted?

4. Why 122 sheets!!!

(I'd like to think about this overall problem a while before suggesting any
more code!)

 

[ucanalways]

<<I have 122 sheets. So, I am planning to have 120 select cases.>>

WOW!!! I didn't realize that you have so many sheets! We may need to
re-think this concept a little bit.

Bill, that would be great.

<<I have another question. For example, sheet4, sheet5 and sheet6 are
inserted and then deleted, the next inserting sheet is usually named
sheet7. Is there any way to get it automatically named as sheet4
instead of sheet7?>>

Again, back to my reply above. You may need to name all of your sheets in
a very systematic way, so we can use a macro that has a For loop in it
somehow. This brings to mind some questions:

1. After Sheet7 is renamed to Sheet4 (to replace the original Sheet4, are
the formulas the same as the original Sheet4, or are they different?

The formula will be same as the original sheet4. I gave this situation
because,
the code generates chart and if it is not satisfctory, then I would
delete all the sheets.
Assuming that Sheet4 to Sheet 10 are generated and I am unhappy with
the result, then I
will delete the charts and then hit the command button again. So, the
new sheet will be named
Sheet11 and according to select-case formula myformula1 and myformula2
would become
"=min(Sheet!(I:I))" and "=max(Sheet!(I:I))" repectively. But, this
should NOT happen. Sheet4-Sheet10
are deleted so the worksheet added should be named Sheet4 and not
Sheet11. Only, then formula
would correspond to Sheet4

2. Let's consider renaming Sheet1 and Sheet2 to something else (i.e.
"Summary", "Scratchpad", etc.). Then all of the sheets that will
"correspond to" (your original terminology) Sheet1 (now renamed) can be
renamed to some constant string with a sequential number appended on the
end (i.e. "DataPlot001", "DataPlot002", etc.). Then we can develop a single
macro that will update all sheets (and maybe rename them) all at once. Is
this possible?

That would be great too! The simpler the better. I will brief what I
am trying to do
in the reply for your question4

3. Why are some sheets being deleted?

If the chart output is not satisfactory, then I may have to delete it
and re-run it

4. Why 122 sheets!!!

Sheet1 has data in 122 columns

Sheet 2 calculates the min and max for the corresponding each column
in sheet1.

For example, the min and max of column E of sheet1 are calculated in
column E of sheet2.
sheet3 has a template that generates chart. The template has quite a
lot of description with
option boxes etc.. That is the main reason, I would like to copy this
sheet and just change the
formula for the successive sheets.

Sheet3 has the following formula that correspond to sheet1 column A
=MIN(Sheet1!A:A)
=MAX(Sheet1!A:A)
=FREQUENCY(Sheet1!A2:A65536,Sheet3!A2:A201)

So, if I copy this sheet3 as sheet4 and change "A:A" to "B:B" in those
formulae, I would get my
required chart. Similarly, if I copy sheet3 as sheet28 and change
"A:A" to "Z:Z" in those formulae,
I would the required chart again.. Imagine if I have to manually copy
sheet3 122 times, rename all the sheets
and manually change the formula.. Also, this will increase the file
size while I keep on adding sheets.
That is the reason I am seeking your help. Please assist me.
Thank you

(I'd like to think about this overall problem a while before suggesting any
more code!)

I agree with you Bill.

 

[Bill Renaud]

You workbook must be absolutely huge (many MB!) with 122 worksheets with
embedded charts on them!

After thinking this problem over a little more, it appears to me that a
better approach might be to add a column of data to Sheet2 (MinMax) to hold
a copy of a single column of data from Sheet1 (Data). It would use the
OFFSET formula to select a single column of data, which your MIN and MAX
formulas could then operate on. Sheet3 (Chart) would then be a single chart
sheet. I would add a spinner control from the Forms toolbar to the chart,
and use it to spin through each column of data.

I am rather concerned about the following:

<<Assuming that Sheet4 to Sheet 10 are generated and I am unhappy with the
result, then I will delete the charts and then hit the command button
again.>>

Won't this just create the same chart with the same flaw again, or are you
using a different formula somewhere to "filter" the data to eliminate
"outliers" or something? It sounds like you are doing statistical process
control, where you are making a chart of a column of data and also plotting
the MIN and MAX value on the chart (I haven't looked at the code in more
detail yet).

(Sorry I have some other stuff to do today, so probably won't get back to
this until tonight.)

I have used the OFFSET function before with a spinner control to produce
variable charts, and it works great and saves a lot of workbook size! We
could even write code to manipulate the spinner value (it is simply a cell
on a worksheet somewhere (Scratchpad)) and then print out the chart (or
whatever you are doing with your charts).

 

[ucanalways]

You workbook must be absolutely huge (many MB!) with 122 worksheets with
embedded charts on them!

After thinking this problem over a little more, it appears to me that a
better approach might be to add a column of data to Sheet2 (MinMax) to hold
a copy of a single column of data from Sheet1 (Data). It would use the
OFFSET formula to select a single column of data, which your MIN and MAX
formulas could then operate on. Sheet3 (Chart) would then be a single chart
sheet. I would add a spinner control from the Forms toolbar to the chart,
and use it to spin through each column of data.

Bill, that should be fine too. I think this should hold good for my
case.

I am rather concerned about the following:

<<Assuming that Sheet4 to Sheet 10 are generated and I am unhappy with the
result, then I will delete the charts and then hit the command button
again.>>

Won't this just create the same chart with the same flaw again, or are you
using a different formula somewhere to "filter" the data to eliminate
"outliers" or something? It sounds like you are doing statistical process
control, where you are making a chart of a column of data and also plotting
the MIN and MAX value on the chart (I haven't looked at the code in more
detail yet).

Yes, you are correct. There may be modifications in the input data if
the output
is not satisfactory. So, the sheet may have to be deleted and re-
generated.

(Sorry I have some other stuff to do today, so probably won't get back to
this until tonight.)

I hope to hear back from you tonight

I have used the OFFSET function before with a spinner control to produce
variable charts, and it works great and saves a lot of workbook size! We
could even write code to manipulate the spinner value (it is simply a cell
on a worksheet somewhere (Scratchpad)) and then print out the chart (or
whatever you are doing with your charts).

I havent seen the spinner control in action before. It sounds like a
user-friendly feature.

 

[Bill Renaud]

I sent you a workbook by e-mail just now, so I hope you get it. It
demonstrates how to do dynamic charts.