My code works in command button but not in public sub

[ucanalways]

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 Where:=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

 

[OssieMac]

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

 

[ucanalways]

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














- 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

 

[OssieMac]

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