Worksheet Function in a Module

[ijourneaux]

I would like to avoid placing a function in a worksheet but I have a couple of situations where I can't seem to get around it.

The first case is the Worksheet_FollowHyperLink function.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim FriendlyName As String
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim RowValue As Long
    Dim ActualTag As String
    
    FriendlyTagName = Target.Range.Value
    FirstRow = 2
    LastRow = Worksheets("TagGroups").Range("A65536").End(xlUp).row
    RowValue = Application.Match(FriendlyTagName, Worksheets("TagGroups").Range("c1:c210"), 0)
    ActualTag = Worksheets("TagGroups").Cells(RowValue, 2)
    Worksheets("Trend").Activate
    Sheet3.UpdateTrend ActualTag
End Sub

Another interesting thing here is that I had to Use

Sheet3.UpdateTrend Actual Tag

and could not use

Worksheets("Trend").UpdateTrend Actual Tag

The second case is where there is an object in the worksheet I need to access

Dim T1, T2 As Date

Public Sub UpdateTrend(tag As String)
    Dim T1, T2 As Date
    Dim i As Integer
'    Dim CurTag As TrendTag
    
    T2 = Now()
    T1 = T2 - 20
    Server = Worksheets("Configuration").Cells(1, 2)
    Map = ""
    
    If (Worksheets("Trend").TrendPlot1.TrendTags.Count > 0) Then
        ' if there are already tags in the plot, clear it
        Worksheets("Trend").TrendPlot1.TrendTags.RemoveAll
    End If
    Worksheets("Trend").TrendPlot1.TrendTags.Add Server, Map, tag

'   Dim CurTag As AtBasicPlotsCtl.TrendTag
   ' update plot control's time
   Worksheets("Trend").TrendPlot1.Time.SetTimeRange T1, T2
    
    ' also update chart times - they should update automatically, but do not currently
    Worksheets("Trend").TrendPlot1.TrendChart.TimeScale.SetTimeRange T1, T2
    
    ' also get new data - SetTimeRange should probably respond automatically
    ' but it does not currently, so just reset time server to force an update
    Worksheets("Trend").TrendPlot1.TimeServer = Worksheets("Trend").TrendPlot1.TimeServer
   'Cycle through all the tags setting the scaling type
   NumTags = Worksheets("Trend").TrendPlot1.TrendTags.Count
   For i = 1 To NumTags
    Set CurTag = TrendPlot1.TrendTags.Item(i)
      CurTag.Scaling.Scaling = 0
      CurTag.PropertiesChanged  'Force drawing to update
   Next

End Sub

In this case, I could refer to the object using

Worksheets("Trend").TrendPlot1.TimeServer

some times but I could not use it in the line where I set CurTag

 Set CurTag = Worksheets("Trend").TrendPlot1.TrendTags.Item(i)

did not work. If It had I would ahve been able to place the sub in a module

Apprecaite any comments or assistance.

Take Care

[romperstomper]

A worksheet event procedure has to go into the module for that sheet. No way round that, but you can move the actual processing code to a normal module and call that from the event.
For the rest, I can't think offhand of any reason why you would sometimes be able to use a given syntax and sometimes not.