Solved

Calling sub routine from a module

Posted on 2013-10-28
3
275 Views
Last Modified: 2013-10-28
Folks,
When I activate a worksheet  I call "NotifyUserGeneral":
Private Sub Worksheet_Activate()
NotifyUserGeneral
End Sub

Open in new window

I have a module that contains "NotifyUserGeneral" below:
Option Explicit
Private mMessageDisplayed As Boolean
-----------------------------------------------------------------------
Private Sub Worksheet_Activate()
    NotifyUserGeneral
End Sub
-----------------------------------------------------------------------
Private Sub NotifyUserGeneral()
If ActiveSheet.ProtectContents = True And Not mMessageDisplayed Then
    MsgBox "Except for the data cells shaded in light blue with black font the rest of this worksheet is protected!"
    mMessageDisplayed = True
End If
End Sub
---------------------------------------------------------------------------
Private Sub NotifyUserCell()
If ActiveSheet.ProtectContents = True And ActiveCell.Locked = True Then
    Msbbox "That cell is protected and cannot be changed!"
End If
End Sub
----------------------------------------------------------------------------------------------------------

Open in new window

However, I keep getting "Sub or Function not defined"  error when I activate the worksheet?
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39605869
Hi,

Private before your sub means that it can only be used in the module .

so change to

Sub NotifyUserGeneral()
If ActiveSheet.ProtectContents = True And Not mMessageDisplayed Then
    MsgBox "Except for the data cells shaded in light blue with black font the rest of this worksheet is protected!"
    mMessageDisplayed = True
End If
End Sub

Open in new window


Regards
0
 

Author Comment

by:Frank Freese
ID: 39605898
You nailed it!
Thanks
0
 

Author Closing Comment

by:Frank Freese
ID: 39605899
thank you very much
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question