Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
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 -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
On Nov 1, 11:16 am, "Bill Renaud"
wrote: 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 -- Regards, Bill Renaud 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 Whe=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
<<...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 Whe=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.NumberForm at = "0.00" End With 'shp End If Next shp End Sub -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
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 Whe=xlLocationAsObject, name:="Sheet3" ....would become: ActiveChart.Location Whe=xlLocationAsObject, name:=wsActive.Name -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
On Nov 1, 4:10 pm, "Bill Renaud"
wrote: 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 Whe=xlLocationAsObject, name:="Sheet3" ...would become: ActiveChart.Location Whe=xlLocationAsObject, name:=wsActive.Name -- Regards, Bill Renaud 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
<<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!) -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
On Nov 1, 10:34 pm, "Bill Renaud"
wrote: <<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. -- Regards, Bill Renaud |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
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). -- Regards, Bill Renaud |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
On Nov 2, 10:08 am, "Bill Renaud"
wrote: 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. -- Regards, Bill Renaud |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to slightly modify the subroutine
I sent you a workbook by e-mail just now, so I hope you get it. It
demonstrates how to do dynamic charts. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating macro for slightly complicated formula | Excel Discussion (Misc queries) | |||
Call a subroutine using variable subroutine name | Excel Discussion (Misc queries) | |||
Calling a VBA add in from a macro subroutine | Excel Programming | |||
How To Quit Subroutine from a called subroutine | Excel Programming | |||
How do I exit a macro subroutine? | Excel Worksheet Functions |