Make function only active on one worksheet in the workbook.

[mhwolog]

Hello,
I'm having trouble making a modification to my existing working code. I've tried multiple combinations but can't seem to get it to work.

I would like to add the condition that if Worksheets("Index").Range("C28").Text = "1" Then this function will only evaluate Worksheets("Underlying Assets, Settings")
i.e. the function is not active on any other worksheet at that time.

Any help would be appreciated, thanks.

Public Function Alarm(cell, Condition) As Boolean


    Dim strAlarmHTKpath As String, varProc As Variant


    Debug.Print "Alarm: " & cell.Address
    
    On Error GoTo ErrHandler
    
    If Evaluate(cell.Value & Condition) Then
        If Worksheets("Index").Range("C28").Text <> "" Then     'Alarm is disabled
              'strAlarmHTKpath = "C:\users\dropbox\alarm.exe"       'Alarm will not sound
        Else
        
              strAlarmHTKpath = "C:\users\dropbox\alarm.exe" 'Must be an exe file, .ahk won't work; Alarm will sound
              varProc = Shell(strAlarmHTKpath, 1)
        End If
        Alarm = True
        Exit Function
    End If
    
ErrHandler:
        Alarm = False
        
End Function

 

[Yongle]

I am not sure why you need multiple conditions if you only want the alarm to ring when both C28 = 1 and the cell is in a specific worksheet
- should the alarm ring at any other time?


try

        If Evaluate(cell.Value & Condition) Then
            If Worksheets("Index").Range("C28").Text = "1" And cell.Parent.Name = "Underlying Assets, Settings" Then
                    strAlarmHTKpath = "C:\users\dropbox\alarm.exe" 
                    varProc = Shell(strAlarmHTKpath, 1)
            End If
        End If

or perhaps this as the first line of the function:

IF cell.Parent.Name <> "Underlying Assets, Settings" then Exit Function

 

[mhwolog]

Thankyou Yongle,
I was able to incorporate the second line to get it to work. I needed three conditions - alarm (or function) off completely, alarm only triggers on one worksheet, alarm triggers on all worksheets. Thanks again.

Public Function Alarm(cell, Condition) As Boolean


    Dim strAlarmHTKpath As String, varProc As Variant
    Debug.Print "Alarm: " & cell.Address
    
    On Error GoTo ErrHandler
    
    If Evaluate(cell.Value & Condition) Then
        If Worksheets("Index").Range("C28").Text = "1" Then       'Only trigger alarm for underlyings page
            If cell.Parent.Name = "Underlying Assets, Settings" Then GoTo ActivateAlarm


            If cell.Parent.Name <> "Underlying Assets, Settings" Then Exit Function
        
        ElseIf Worksheets("Index").Range("C28").Text <> "" Then       'Alarm is disabled
         'strAlarmHTKpath = "C:\users\dropbox\alarm.exe"          'Alarm will not sound


        Else
ActivateAlarm:
         strAlarmHTKpath = "C:\users\dropbox\alarm.exe"      'Must be an exe file, .ahk won't work; Alarm will sound
         varProc = Shell(strAlarmHTKpath, 1)
        End If
        Alarm = True
        Exit Function
    End If
    
ErrHandler:
        Alarm = False
        
End Function

 

[Yongle]

alarm (or function) off completely (anything other than 1 or 2 in C28)
alarm only triggers on one worksheet (C28 = 1)
alarm triggers on all worksheets (C28 = 2)

How about this

Function Alarm(cell, condition) As Boolean
    Dim strAlarmHTKpath As String, varProc As Variant
    Dim wsName  As String:  wsName = cell.Parent.Name
    Dim tstName As String:  tstName = "Underlying Assets, Settings"
    Dim Flag    As String:  Flag = Worksheets("Index").Range("C28").Value
    
    On Error Resume Next
    If Evaluate(cell.Value & condition) Then
        Select Case Flag
            Case [COLOR=#ff0000]1[/COLOR]:         If wsName = tstName Then Alarm = True
            Case [COLOR=#ff0000]2[/COLOR]:         Alarm = True
            Case Else:      Alarm = False
        End Select
    End If
    If Err.Number > 0 Then Alarm = False
    
    If Alarm Then
        strAlarmHTKpath = "C:\users\dropbox\alarm.exe"      
        varProc = Shell(strAlarmHTKpath, 1)
    End If
End Function

Your function contains a lot of exit points and it is not immediately obvious what is going on (especially if you want to amend it 12 months from now! )
- above function contains only one exit point

 

[mhwolog]

Thanks, Yongle I will give it a try!