VBA code crosstalk between Worksheet and Module

[jbrojbro]

Hi all. I can't seem to figure out how to initiate crosstalk between VBA code in a specific Worksheet and VBA code within a particular module. Let me try to explain...I have a scrollbar, and for each value the contents of a particular cell are highlighted. I'd like to also activate hyperlinks for each of the highlighted cells. When the user clicks the hyperlinked cells, I'd like the scrollbar to jump to the corresponding value.

I've got VBA code that will run when a hyperlink is clicked, and I've got the scrollbar VBA code working properly, but I can't figure out how to call the scrollbar code from the hyperlink code. Does that make sense?

Any ideas?

Thanks,
JBro

 

[Jerry Sullivan]

Hi JBro,

Using the keyword Public in the declaration of procedures (Sub and Functions) in a Standard Code module makes those procedures accessible to all modules in the VBA Project (all modules in the workbook).

Public scope is the default, so you can omit that keyword and get the same result; however declaring the scope of your procedures makes the intent of your code clearer.

Place this code in the sheet code module for Sheet1

Public Sub CodeInSheet1()
    MsgBox "You found me in Sheet1"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Call CodeInModule1
    
    If IsNumeric(Target.Value) Then _
        MsgBox "The Changed Cell's value plus 10 equals: " _
            & SumMe(x:=Target.Value, y:=10)
End Sub

Place this code in Module1

Public Sub CodeInModule1()
    MsgBox "You found me in Module1"
End Sub

Public Function SumMe(x As Double, y As Double)
    SumMe = x + y
End Function

If you enter numbers on Sheet1, you'll get messages showing that Sheet1's Worksheet Change event is able to call a Sub and a Procedure in Module1

The same is not true for calling procedures in Sheet Modules from other modules in the workbook. Paste this into Module1 and try to run it. You'll get an error because CodeInSheet1 doesn't not have scope in Module1 (even though it has the Public keyword).

Sub FailsToCallSheet1()
    Call CodeInSheet1
End Sub

As a good practice code in a Worksheet Module is related to events and objects on that specific sheet, and typically there isn't a reason to call that code from a Standard Code Module.

If you must call code in a Sheet module, you can do it by explicitly referencing its class module name.

Sub CallsToSheet1()
    Call Sheet1.CodeInSheet1
End Sub

 

[jbrojbro]

Thanks Jerry. This is very helpful, and I'll work on implementing it this evening.

Cheers,
JBro