Calling one function from the other function in VBA (Excel)

Question

I have two VBA functions, but i am unable to call the other from the first function.

Function 1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lastRow As Long
  With ActiveSheet
    lastRow = .Cells(.Rows.count, "A").End(xlUp).Row
    Dim I, J As Integer
    For I = 1 To lastRow
      If Cells(I, "C").Value = "" Then
        MsgBox "Please Enter Business Type Value", vbOKOnly
        Exit Sub
      End If
    Next I
  End With
End Sub

And the 2nd function:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub
  Application.EnableEvents = False

  If Not Application.Intersect(Target, Me.Columns(3)) Is Nothing Then
    ActiveSheet.Unprotect
    Select Case Target.Value
      Case Is = "CNS"
        Target.Offset(0, 4).Locked = True
      Case Is = "cns"
        Target.Offset(0, 4).Locked = True
      Case Is = "APL"
        Target.Offset(0, 4).Locked = False
      Case Is = "apl"
        Target.Offset(0, 4).Locked = False
      Case Else
        MsgBox "Value not covered by the program", vbInformation + vbOKOnly
    End Select
    ActiveSheet.Protect
  Else
  End If
  Application.EnableEvents = True
End Sub

please help somebody.. thanks in advance..

What method is trying to call which? What have you tried? What errors are you seeing? — Paul Oliver, Dec 18 '15 at 05:27
I think your answer will be on this question: https://stackoverflow.com/questions/1072075/how-do-i-call-a-vba-function-into-a-sub-procedure — Paul Oliver, Dec 18 '15 at 05:31
i want to call Sub Worksheet_Change from Sub Worksheet_SelectionChange — nina, Dec 18 '15 at 05:51

score 1 · Accepted Answer · edited Dec 24 '15 at 23:43

1

In the same module you just call nameoffunction
You can make function public

public sub function

But it's a poor (sometime good) solution. You should structure your code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 call modul1.function1 ( Target ) ' As Range)
End sub

Private Sub Worksheet_Change(ByVal Target As Range)
 call modul1.function1 ( Target  ) ' as range
 call modul1.function2 ( Target  )
end sub

edit ok ugly way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
....
 call Worksheet_change ( Target)
End sub

edited Dec 24 '15 at 23:43

Josh Crozier

233,099
56
391
304

answered Dec 18 '15 at 05:48

thank u foe helping but i am not getting it how to write – nina Dec 18 '15 at 05:55
this call worksheet_change (target) is working, thank you for that – nina Dec 18 '15 at 07:44

Calling one function from the other function in VBA (Excel)

1 Answers1