Returning focus to last used cell in VBA function

[wasabi]

I've got a function as follows:

Function VerificarInfinito(a As Double)
Set c = ActiveCell
ActiveSheet.ChartObjects("Planta").Activate
If (a >= 3) Then
    ActiveChart.SeriesCollection(2).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(13).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(14).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(1).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(4).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(16).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(3).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(15).Format.Line.Transparency = 1
ElseIf a < 1 / 3 Then
    ActiveChart.SeriesCollection(2).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(13).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(14).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(1).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(4).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(16).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(3).Format.Line.Transparency = 0
    ActiveChart.SeriesCollection(15).Format.Line.Transparency = 0
Else
    ActiveChart.SeriesCollection(2).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(13).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(14).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(1).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(4).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(16).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(3).Format.Line.Transparency = 1
    ActiveChart.SeriesCollection(15).Format.Line.Transparency = 1
End If
c.Activate
VerificarInfinito = a
End Function

It does everything I need it to do (in this case, make certain lines in a graph visible or not depending on the input value). However, the function ends with the focus on the chart, not in the last used cell. I tried fixing this by saving the ActiveCell in the beginning and then Activating it again at the end of the function, however this has no effect.

I've used this solution (saving the ActiveCell) in other VBA subroutines (also altering the contents of a graph) and it works like a charm, so I don't entirely understand why it doesn't work here.

Sub Drop_Esq()
a = Range("S4").Value
Set c = ActiveCell
'... do stuff with the graph
c.Activate
End Sub

Is there a relevant difference due to this being a Function as opposed to a Sub(routine)?

 

[AlphaFrog]

You don't need to activate the chart to change it...

Sub VerificarInfinito(a As Double)

    With ActiveSheet.ChartObjects("Planta")
        If (a >= 3) Then
            .SeriesCollection(2).Format.Line.Transparency = 0
            .SeriesCollection(13).Format.Line.Transparency = 0
            .SeriesCollection(14).Format.Line.Transparency = 0
            .SeriesCollection(1).Format.Line.Transparency = 0
            .SeriesCollection(4).Format.Line.Transparency = 1
            .SeriesCollection(16).Format.Line.Transparency = 1
            .SeriesCollection(3).Format.Line.Transparency = 1
            .SeriesCollection(15).Format.Line.Transparency = 1
        ElseIf a < 1 / 3 Then
            .SeriesCollection(2).Format.Line.Transparency = 1
            .SeriesCollection(13).Format.Line.Transparency = 1
            .SeriesCollection(14).Format.Line.Transparency = 1
            .SeriesCollection(1).Format.Line.Transparency = 1
            .SeriesCollection(4).Format.Line.Transparency = 0
            .SeriesCollection(16).Format.Line.Transparency = 0
            .SeriesCollection(3).Format.Line.Transparency = 0
            .SeriesCollection(15).Format.Line.Transparency = 0
        Else
            .SeriesCollection(2).Format.Line.Transparency = 1
            .SeriesCollection(13).Format.Line.Transparency = 1
            .SeriesCollection(14).Format.Line.Transparency = 1
            .SeriesCollection(1).Format.Line.Transparency = 1
            .SeriesCollection(4).Format.Line.Transparency = 1
            .SeriesCollection(16).Format.Line.Transparency = 1
            .SeriesCollection(3).Format.Line.Transparency = 1
            .SeriesCollection(15).Format.Line.Transparency = 1
        End If
    End With
    
End Sub

Also, this doesn't need to be a Function as best I can tell. It doesn't return a different\new value. Call the above sub with something like...
VerificarInfinito 4

 

[wasabi]

I don't know why, but any time I've ever tried using With ChartObject, it's never worked. In this case, it fails on the very first .SeriesCollection() call.

And I have it as a function because I need it to update whenever the input value is altered. The call to the function is actually "=VerificarInfinito(B1/B2)" so that it will update whenever either B1 or B2 are changed. I need that value to know how to alter the graph but I also want it displayed on the screen, which is why I simply have the function return the same value as in inputted.

As far as I know, there's no way to link a Sub to a cell. The other Subs I have (where my ActiveCell ... Activate code works) are linked to control buttons.

 

[AlphaFrog]

I didn't realize this was a worksheet User Defined Function (UDF).

A UDF cannot select a different cell among many other things. Here's a good pimer on UDF limits.
http://www.decisionmodels.com/calcsecretsj.htm

 

[AlphaFrog]

Another way to link code to a cell change is to use the Worksheet_Change event procedure.
Events And Event Procedures In VBA

This would not have the limits of a UDF.

 

[wasabi]

Ah, thanks. The Worksheet_Change method worked like a charm. Much appreciated. However, With ChartObject still didn't work...

However, I simply changed it back to ActiveChart and it worked.

 

[AlphaFrog]

Your welcome. Thanks for the feedback.

The With command should be this...

With ActiveSheet.ChartObjects("Planta").Chart