VBA code crosstalk between Worksheet and Module

jbrojbro

New Member
Joined
Mar 17, 2013
Messages
8
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
 
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
Code:
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
Code:
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).

Code:
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.

Code:
Sub CallsToSheet1()
    Call Sheet1.CodeInSheet1
End Sub
 
Last edited:
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top