Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My code works in command button but not in public sub
The following code does not work in public sub. When I placed the code
in a public sub, I replaced Sheet3 in the following code with "activesheet". All I am trying to do is make the code generic so that I can used for any activesheet instead of a specific sheet. Frankly, I dont know how to do this. Can anyone please give me the generic code (public sub code) for the code given below. I tried doing this but spiliting of hairs for more than couple of hours due to mysteroius errors. I would deeply appreciate any help. Thank you very much Private Sub CommandButton2_Click() Dim shp As Shape For Each shp In ActiveSheet.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:=Sheets("Sheet3").Range("H4"), PlotBy:= _ xlColumns 'change required ActiveChart.SeriesCollection.NewSeries i = Range("F4").Value 'change required?? j = i + 3 With Sheets("Sheet3") 'change required ActiveChart.SeriesCollection(1).XValues = _ .Range(.Cells(2, 1), .Cells(j, 1)) End With With Sheets("Sheet3") 'change required ActiveChart.SeriesCollection(1).Values = _ .Range(.Cells(2, 2), .Cells(j, 2)) End With With Sheets("Sheet3") 'change required Range("F6").Value = Application.WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(j, 2))) End With ActiveChart.SeriesCollection(1).Name = "=Sheet3!R1C2" ActiveChart.Legend.Select Selection.Delete ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet3" 'Dim shp As Shape For Each shp In ActiveSheet.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My code works in command button but not in public sub
You need to put the code in a module under another name like:-
Sub MyCommandButton 'Insert code here End Sub Then in each worksheet code area insert the code that is called by the button as per the following example:- Private Sub CommandButton2_Click() Call MyCommandButton End Sub You need to refer to active sheet. Best way to do this is to assign it to a variable:- dim wsActive as Worksheet set wsActive = Activesheet then use wsActive in lieu of Sheets("Sheet1") etc. Also ensure that the sub name that goes in the sheets code area matches the name of the button. It doesn't matter if the button name is different just so long as the sub name matches it. Regards, OssieMac " wrote: The following code does not work in public sub. When I placed the code in a public sub, I replaced Sheet3 in the following code with "activesheet". All I am trying to do is make the code generic so that I can used for any activesheet instead of a specific sheet. Frankly, I dont know how to do this. Can anyone please give me the generic code (public sub code) for the code given below. I tried doing this but spiliting of hairs for more than couple of hours due to mysteroius errors. I would deeply appreciate any help. Thank you very much Private Sub CommandButton2_Click() Dim shp As Shape For Each shp In ActiveSheet.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:=Sheets("Sheet3").Range("H4"), PlotBy:= _ xlColumns 'change required ActiveChart.SeriesCollection.NewSeries i = Range("F4").Value 'change required?? j = i + 3 With Sheets("Sheet3") 'change required ActiveChart.SeriesCollection(1).XValues = _ .Range(.Cells(2, 1), .Cells(j, 1)) End With With Sheets("Sheet3") 'change required ActiveChart.SeriesCollection(1).Values = _ .Range(.Cells(2, 2), .Cells(j, 2)) End With With Sheets("Sheet3") 'change required Range("F6").Value = Application.WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(j, 2))) End With ActiveChart.SeriesCollection(1).Name = "=Sheet3!R1C2" ActiveChart.Legend.Select Selection.Delete ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet3" 'Dim shp As Shape For Each shp In ActiveSheet.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My code works in command button but not in public sub
On Oct 30, 8:41 pm, OssieMac
wrote: You need to put the code in a module under another name like:- Sub MyCommandButton 'Insert code here End Sub Then in each worksheet code area insert the code that is called by the button as per the following example:- Private Sub CommandButton2_Click() Call MyCommandButton End Sub You need to refer to active sheet. Best way to do this is to assign it to a variable:- dim wsActive as Worksheet set wsActive = Activesheet then use wsActive in lieu of Sheets("Sheet1") etc. Also ensure that the sub name that goes in the sheets code area matches the name of the button. It doesn't matter if the button name is different just so long as the sub name matches it. Regards, OssieMac " wrote: The following code does not work in public sub. When I placed the code in a public sub, I replaced Sheet3 in the following code with "activesheet". All I am trying to do is make the code generic so that I can used for any activesheet instead of a specific sheet. Frankly, I dont know how to do this. Can anyone please give me the generic code (public sub code) for the code given below. I tried doing this but spiliting of hairs for more than couple of hours due to mysteroius errors. I would deeply appreciate any help. Thank you very much Private Sub CommandButton2_Click() Dim shp As Shape For Each shp In ActiveSheet.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:=Sheets("Sheet3").Range("H4"), PlotBy:= _ xlColumns 'change required ActiveChart.SeriesCollection.NewSeries i = Range("F4").Value 'change required?? j = i + 3 With Sheets("Sheet3") 'change required ActiveChart.SeriesCollection(1).XValues = _ .Range(.Cells(2, 1), .Cells(j, 1)) End With With Sheets("Sheet3") 'change required ActiveChart.SeriesCollection(1).Values = _ .Range(.Cells(2, 2), .Cells(j, 2)) End With With Sheets("Sheet3") 'change required Range("F6").Value = Application.WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(j, 2))) End With ActiveChart.SeriesCollection(1).Name = "=Sheet3!R1C2" ActiveChart.Legend.Select Selection.Delete ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet3" 'Dim shp As Shape For Each shp In ActiveSheet.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- Hide quoted text - - Show quoted text - OssieMac, Thanks for that perfect tip.. Everything seems to be okay except for this for which I get an error: Method 'Range' of object '_Global' failed With wsActive Range("F6").Value = Application.WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(j, 2))) End With I commented these three lines and rest works well with wsActive. Can you help me out to solve this issue? Thank you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My code works in command button but not in public sub
I haven't been through your code with a fine tooth comb but is the sheet you
need for that part of the code the active sheet? Is j a valid number for the row? However, do you really need activesheet? What I mean by this, does it affect your code if it is called from a different active sheet and you need the active sheet reference or does the code still act on the same sheets irrespective of where it is callled from? If the latter, then leave the code as you had it before with the sheet names. If you need to know the active sheet, then I recall that there are some issues in calling subs outside of the sheet code area. I had forgotten this until you raised your problem. I think that the workaround is to call the macro and pass the active sheet name as a parameter. You do this as follows:- Private Sub CommandButton1_Click() Dim strTemp As String strTemp = ActiveSheet.Name Call MyCommandButton(strTemp) End Sub The called sub is then like this example:- Sub MyCommandButton(myActSheet As String) j = 30 'Dummy value for testing With Sheets(myActSheet) Range("F6").Value = _ Application.WorksheetFunction.Sum _ (.Range(.Cells(2, 2), .Cells(j, 2))) End With End Sub Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UNIQ EXPEREINCE: PROCEDURE WORKS FINE USING F8 BUT GET ERROR WHEN COMMAND BUTTON IS CLICKED | Excel Programming | |||
Command button code | Excel Programming | |||
Excel macro works with keyboard shortcut, but not command button. Please Help. | Excel Programming | |||
Opening an Outlook public folder from Excel using a command button... | Excel Programming | |||
how do I set up a command button that works with a combo box | Excel Discussion (Misc queries) |