Private Sub Worksheet

Private Sub Worksheet

New posts

New Excel articles

Latest activity

MrExcel Homepage

MrExcel Bookstore

MrExcel Seminars

Excel Consulting Services

Everywhere
Threads
This forum
This thread

If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .

Thread starter

Lord_B

Start date

Oct 17, 2018

Tags

corners

me.shapesrectangle

multiple actions

private sub worksheet

rounded

Private Sub Worksheet_Calculate()
If Range("a5").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 1").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 1").Visible = False
End If
End Sub

Private Sub Worksheet_Calculate()
If Range("a6").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 4").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 4").Visible = False
End If
End Sub

Private Sub Worksheet_Calculate()
If Range("a7").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 5").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 5").Visible = False
End If
End Sub

Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joined
Aug 1, 2002

Messages
65,121

Office Version

365

Platform

Windows

Private Sub Worksheet_Calculate()

If Range("a5").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 1").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 1").Visible = False
End If

If Range("a6").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 4").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 4").Visible = False
End If

If Range("a7").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 5").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 5").Visible = False
End If

End Sub

Joined
Jun 12, 2014

Messages
76,981

Office Version

365

Platform

Windows

Private Sub Worksheet_Calculate()
If Range("a5").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 1").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 1").Visible = False
End If
If Range("a6").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 4").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 4").Visible = False
End If
If Range("a7").Value = "a" Then
Me.Shapes("Rectangle: Rounded Corners 5").Visible = True
Else
Me.Shapes("Rectangle: Rounded Corners 5").Visible = False
End If
End Sub

Thanks Joe4 & Fluff for the warm welcome,

Wow! when you put the code like that it makes sense!

Thanks to both of you for your help. you make it look soooo simple

Joined
Jun 12, 2014

Messages
76,981

Office Version

365

Platform

Windows

Glad we could help & thanks for the feedback

pure vito
Aug 18, 2022

Excel Questions

mecerrato
Sep 17, 2021

Excel Questions

agentkramr
Jan 31, 2022

Excel Questions

agentkramr
Dec 27, 2021

Excel Questions

2

3

agentkramr
Feb 9, 2022

Excel Questions

Threads
1,173,846

Messages
5,888,789

Members
434,060

Latest member
Cfisher8

Do not share my Personal Information
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.
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.
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".
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.
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.

“AutoMacro is the best purchase I have made in a long time. This has helped me streamline work processes, making much of what I do much more efficient...”

VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!

© 2022 Spreadsheet Boot Camp LLC. All Rights Reserved.

AutoMacro: The Ultimate Add-in for VBA

Learn More

AutoMacro: The Ultimate Add-in for VBA

off - Sale!

Download our VBA Macro Code Examples Add-in

Download
100% Free

This tutorial will explain the difference between public and private declarations in VBA and how to specify modules as private.
Procedures (Sub and Functions) can be declared either Private or Public in VBA. If they are Public, it means that you would be able to see them from within the Excel Macro Window and they can be called from anywhere within your VBA Project. If they are Private, they cannot be seen in the Excel Macro Window and are only available to be used within the Module in which they are declared (using normal methods, see the bottom of this article for ways to access private procedures from other modules).
Public functions can be called like built-in Excel functions in the Excel worksheet.
Note: Variables and Constants can also be Public or Private.
By default, Excel Macros (most VBA Procedures) are visible to workbook users in the Macro Window:
These are considered Public procedures. You can explicitly define procedures as public by adding “Public” before the Sub statement:
If you don’t define the procedure as Public, it will be assumed Public.
To declare a procedure as Private, simply add “Private” before the procedure sub statement:
The second procedure would not be visible in the Macro window to Excel users, but can still by used in your VBA code.
Sub procedures can have arguments. Arguments are inputs to the sub procedure:
If a sub procedure has arguments, it will never appear in the Macro Window regardless of if its declared Public because there is no way to declare the arguments.
Functions also will never appear in the Macro Window, regardless of if they are declared Public.
Public functions in Excel are able to be used directly in a worksheet as a ‘ User Defined Function ’ (UDF). This is basically a custom formula that can be called directly in a worksheet. They can be found in the ‘User Defined’ category in the ‘Insert Function window or can be typed directly into a cell.
Public procedures can be called from any module or form within your VBA Project.
Attempting to call a private procedure from a different module will result in an error (Note: see bottom of this article for a work around).
Note: Public procedures and variables in class modules behave slightly differently and are outside the scope of this article.
Different modules, can store procedures with the same name, provided they are both private.
If two or more procedures have the same name and are declared public you will get an ‘Ambiguous Name detected’ compile error when running code.
To make a module private, you put the following keyword at the top of the module.
If you declare a module as private, then any procedures in the module will not be visible to Excel users. Function procedures will not appear in the Insert Function window but can still be used in the Excel sheet as long as the user knows the name of the function!
Sub procedures will not appear in the Macro Window but will still be available to be used within the VBA project.
As mentioned above, Private Procedures are inaccessible in other code modules by “normal” methods. However, you can access private procedures by using the Application.Run command available in VBA.
Module 2 is a Private Module with a Public Sub Procedure, whereas Module3 is Public module with a Private Sub Procedure.
In Module1, we can call Hello World – the Option Private Module at the top does not prevent us from calling the Sub Procedure – all it serves to do is hide the Sub Procedure in the Macro Window.
We also do not need the Call statement – it is there to make the code easier to read.
The code could also look like this below:
We can also run the HelloWorld Sub Procedure by using the VBA Application.Run command.
In Module3 however, the GoodMorningWorld procedure has been declared Private. You cannot call it from another module using ‘normal’ means ie the Call statement.
You have to use Application.RunCommand to run a Private Sub from another module.
Notice the when you use the Application.RunCommand command, you have to put the Sub Procedure name within inverted commas.
If we do try to use the Call statement to run the GoodMorningWorld Sub Procedure, an error would occur.
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
AutoMacro is an add-in for VBA that installs directly into the Visual Basic Editor. It comes loaded with code generators, an extensive code library, the ability to create your own code library, and many other helpful time-saving tools and utilities.

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module.
On social media you can to set parts of your profile so that everybody can see it (Public), or only those you allow, such as friends or followers, to see it (Private). The Private vs. Public concept in VBA is similar, but since we’re talking about VBA here, it’s not quite as straight forward.
Before we launch into the difference between Public and Private, we first need to understand what Modules are, and how they function.
Modules are the place where we enter and store VBA code.
Worksheet Modules are generally used to trigger code which relate to that specific worksheet. Each worksheet contains its own module, so if there are 6 worksheets, then you will have 6 Worksheet Modules.
In the screenshot above, the VBA code is contained within the Worksheet Module of Sheet1 and is triggered only when Sheet1 is activated. Any code in a Worksheet Module based on worksheet events applies only to the worksheet in which the code is stored.
The Workbook Module is generally used to trigger code which relates to workbook level events.
In the screenshot above, the VBA code will run when a workbook is opened. Every workbook has its own module.
UserForm Modules generally contains code which relates to UserForm events. Each UserForm contains its own module.
In the screenshot above, the VBA code will run when the user clicks on the button in the UserForm.
Standard Modules are not related to any specific objects and do not have any events related to them. Therefore, Standard Modules are not triggered by User interaction. If we are relying on events being triggered, then the Workbook, Worksheet or UserForm Modules may call a macro within a Standard Module.
The screenshot above shows a code which when run, will password protect the ActiveSheet, no matter which workbook or worksheet it is.
The final type of VBA module available is a Class Module. These are for creating custom objects and operate very differently to the other module types. Class Modules are outside the scope of this post.
The terms Public and Private are used in relation to Modules. The basic concept is that Public variables, subs or functions can be seen and used by all modules in the workbook while Private variables, subs and functions can only be used by code within the same module.
Let’s look at each of these in turn.
When thinking about the difference between Public and Private subs, the two main areas to consider:
Where Private or Public is excluded, VBA will always treat the sub as if it were Public.
One of the most important features of Private subs is that they do not appear in Excel’s Macro window.
Let’s assume Module1 contains the following two macros:
The Macro window will only display the Public sub.
I don’t want you to jump to the conclusion that all Public Subs will appear in the Macro window. Any Public sub which requires arguments , will also not appear in this window, but it can still be executed if we know how to reference it.
Can the code be run from another macro
When we think about Private subs, it is best to view them as VBA code which can only be called by other VBA code within the same module. For example, if Module1 contains a Private Sub, it cannot be called by any code in another module.
Using a simple example here is a Private Sub in Module1:
Now let’s try to call the ShowMessage macro from Module2.
It will generate an error, as the two macros are in different modules.
But don’t worry, there are many ways to run a macro from another macro . If we use the Application.Run command, it will happily run a Private sub. Let’s change the code in Module2 to include the Application.Run command:
Instead of an error, the code above will execute the ShowMessage macro.
Working with object based module events
Excel creates the Worksheet, Workbook and UserForm Module events as Private by default, but they don’t need to be. If they are changed to Public, they can be called from other modules. Lets look at an example.
Enter the following code into the Workbook Module (notice that I have changed it to a Publc sub).
We can call this from another macro by using the name of the object followed by the name of Public sub.
This means that we can run the Workbook_Open event without needing to actually open the workbook. If the sub in the Workbook Module is Private, we can still use the Application.Run method noted above.
VBA functions are used to return calculated values. They have two main uses:
Functions created without the Private or Public declaration are treated as if they are Public.
Calculating values within the worksheet (User Defined Functions)
User Defined Functions are worksheet formulas which operate the same way as other Excel functions, such as SUMIF or VLOOKUP.
The following code snippets are included within Module1:
If we now look at the Insert Function window, the IAmVisible function is available for use:
Functions must be declared in a Standard Module to be used as User Defined Functions in Excel.
Functions used within VBA code operate in the same way as subs; Private functions should only be visible from within the same module. Once again, we can revert to the Application.Run command to use a Private function from another module.
The code above will happily call the NotVisible private function from Module1.
Variables are used to hold values or references to objects which change while the macro runs. Variables come in 3 varieties, Public, Private and Dim.
Public variables must be declared at the top of the code module, directly after the Option Explicit statement (if you have one) and before any subs or functions. The following is incorrect and will create an error if we try to use the Public Variable.
The correct approach in Module1 is this (The Public variable is declared before any subs or functions):
As it is a Public variable, we can use and change the variable from any module (of any type) in the workbook. Look at this example code below, which could run from Module2:
Private Variables can only be accessed and changed by subs and functions within the same Module. They too must also be declared at the top of the VBA code.
The following demonstrates an acceptable usage of a Private variable.
Most of us learn to create variables by placing the word Dim at the start. Dim variables behave differently depending on how they are declared.
Dim variables declared within a sub or function can only be used within that sub or function. In the example below the Dim has been declared in a sub called CreateDim, but used within a sub called UseDim. If we run the UseDim code, it cannot find the Dim variable and will error.
If a Dim variable is created at the top of the module, before all the subs or functions, it will operate like a Private variable. The following code will run correctly.
You might be thinking that it sounds easier to create everything as Public, then it can be used anywhere. A logical, but dangerous conclusion. It is much better to control all sections of the code. Ask yourself, if somebody were to use your macro from Excel’s Macro window, should it work? Or if somebody ran your function as a User Defined Function should it work? Answers to these questions are a good guiding principle to help decide between Public and Private.
It is always much better to limit the scope of your subs, functions and variables initially, then expand them when required in specific circumstances.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
If you’ve fo
Bexa Pierce Porn
Lana Naked
Voyeur Fat Bbw Granny Pee Pussy Parc

Private Sub Worksheet

Report Page