Private Sub Click

Private Sub Click

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 .

UserForms: Private Sub CmdCancel_Click()

Thread starter

94mustang

Start date

Apr 10, 2014

Private Sub OKButton_Click()
'The App.EnableEvents code below prevents the
'Run-time error '400': Form already displayed; can't show modally
Application.EnableEvents = False
Range("B3").Value = Val(txtpval.Text)
Range("B4").Value = Val(txtnval.Text)

'If p value is empty and n has a value, highlight p value text box.
If Trim(Me.txtpval.Value) = "" And Trim(Me.txtnval.Value) = "" Then
Me.txtpval.SetFocus
MsgBox "Please enter a whole number for both p and n.", , "Empty Fields"
txtnval.BackColor = RGB(255, 159, 159)
txtpval.BackColor = RGB(255, 159, 159)
Exit Sub
ElseIf Trim(Me.txtpval.Value) <> "" And Trim(Me.txtnval.Value) = "" Then
Me.txtnval.SetFocus
MsgBox "Please enter a whole number for n.", , "Empty Field"
txtpval.BackColor = RGB(255, 255, 255)
txtnval.BackColor = RGB(255, 159, 159)
Exit Sub
ElseIf Trim(Me.txtnval.Value) <> "" And Trim(Me.txtpval.Value) = "" Then
Me.txtpval.SetFocus
MsgBox "Please enter a whole number for p.", , "Empty Field"
txtnval.BackColor = RGB(255, 255, 255)
txtpval.BackColor = RGB(255, 159, 159)
Exit Sub
End If

'Checks to see if p value was exceeded.
If Trim(Me.txtpval.Value) < 2 Or Trim(Me.txtpval.Value) > 1000 Then
Me.txtpval.SetFocus
MsgBox "Enter p value >= 2 and <= 1,000", , "Number Exceeded"
txtpval.BackColor = RGB(255, 159, 159)
txtpval.Value = ""
Exit Sub
End If

'Checks to see if n value was exceeded.
If Trim(Me.txtnval.Value) < 2 Or Trim(Me.txtnval.Value) > 10000 Then
Me.txtnval.SetFocus
MsgBox "Enter n value >=2 and <= 10,000", , "Number Exceeded"
txtnval.BackColor = RGB(255, 159, 159)
txtnval.Value = ""
Exit Sub
End If

'The App.EnableEvents code below prevents the
'Run-time error '400' (see first comment)
Application.EnableEvents = True
Unload Me
End Sub
-----------------------------------------------------------------------------
Private Sub txtpval_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Then
KeyAscii = KeyAscii
Else
KeyAscii = 2
MsgBox "Only whole numbers are accepted for p.", , "Whole Numbers Only"
txtpval.BackColor = RGB(255, 159, 159)
End If
End Sub
-------------------------------------------------------------------------------
Private Sub txtnval_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Then
KeyAscii = KeyAscii
Else
KeyAscii = 2
MsgBox "Only whole numbers are accepted for n.", , "Whole Numbers Only"
txtnval.BackColor = RGB(255, 159, 159)
End If
End Sub
-------------------------------------------------------------------------------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
MsgBox "Use the Cancel button.", vbCritical, "Invalid Click"
End If
End Sub
---------------------------------------------------------------------------
Private Sub CmdCancel_Click()
txtpval.Value = ""
txtnval.Value = ""
Unload Me
MsgBox "Summaries will NOT be created.", vbExclamation, "WARNING"
End Sub

Not everything I do at work revolves around Excel. Only the fun parts.

dosman
Mar 20, 2022

Excel Questions

WildBurrow
Jun 8, 2022

Excel Questions

SamarthSalunkhe
Sep 15, 2021

Excel Questions

2

3

KDS14589
Aug 8, 2021

Excel Questions

Threads
1,173,846

Messages
5,888,786

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.

Table of contents

Exit focus mode

Light

Dark

High contrast

Light

Dark

High contrast

This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Declares the name, parameters, and code that define a Sub procedure.
Optional. Indicates definition of a partial method. See Partial Methods .
Optional. Can be one of the following:
Optional. Can be one of the following:
Required. Name of the procedure. See Declared Element Names . To create a constructor procedure for a class, set the name of a Sub procedure to the New keyword. For more information, see Object Lifetime: How Objects Are Created and Destroyed .
Optional. List of type parameters for a generic procedure. See Type List .
Optional. List of local variable names representing the parameters of this procedure. See Parameter List .
Optional. Indicates that this procedure implements one or more Sub procedures, each one defined in an interface implemented by this procedure's containing class or structure. See Implements Statement .
Required if Implements is supplied. List of Sub procedures being implemented.
implementedprocedure [ , implementedprocedure ... ]
Each implementedprocedure has the following syntax and parts:
Optional. Indicates that this procedure can handle one or more specific events. See Handles .
Required if Handles is supplied. List of events this procedure handles.
eventspecifier [ , eventspecifier ... ]
Each eventspecifier has the following syntax and parts:
Optional. Block of statements to run within this procedure.
Terminates the definition of this procedure.
All executable code must be inside a procedure. Use a Sub procedure when you don't want to return a value to the calling code. Use a Function procedure when you want to return a value.
You can define a Sub procedure only at the module level. The declaration context for a sub procedure must, therefore, be a class, a structure, a module, or an interface and can't be a source file, a namespace, a procedure, or a block. For more information, see Declaration Contexts and Default Access Levels .
Sub procedures default to public access. You can adjust their access levels by using the access modifiers.
If the procedure uses the Implements keyword, the containing class or structure must have an Implements statement that immediately follows its Class or Structure statement. The Implements statement must include each interface that's specified in implementslist . However, the name by which an interface defines the Sub (in definedname ) doesn't have to match the name of this procedure (in name ).
When a Sub procedure returns to the calling code, execution continues with the statement after the statement that called it.
The following example shows a return from a Sub procedure.
The Exit Sub and Return statements cause an immediate exit from a Sub procedure. Any number of Exit Sub and Return statements can appear anywhere in the procedure, and you can mix Exit Sub and Return statements.
You call a Sub procedure by using the procedure name in a statement and then following that name with its argument list in parentheses. You can omit the parentheses only if you don't supply any arguments. However, your code is more readable if you always include the parentheses.
A Sub procedure and a Function procedure can have parameters and perform a series of statements. However, a Function procedure returns a value, and a Sub procedure doesn't. Therefore, you can't use a Sub procedure in an expression.
You can use the Call keyword when you call a Sub procedure, but that keyword isn't recommended for most uses. For more information, see Call Statement .
Visual Basic sometimes rearranges arithmetic expressions to increase internal efficiency. For that reason, if your argument list includes expressions that call other procedures, you shouldn't assume that those expressions will be called in a particular order.
By using the Async feature, you can invoke asynchronous functions without using explicit callbacks or manually splitting your code across multiple functions or lambda expressions.
If you mark a procedure with the Async modifier, you can use the Await operator in the procedure. When control reaches an Await expression in the Async procedure, control returns to the caller, and progress in the procedure is suspended until the awaited task completes. When the task is complete, execution can resume in the procedure.
An Async procedure returns to the caller when either the first awaited object that’s not yet complete is encountered or the end of the Async procedure is reached, whichever occurs first.
You can also mark a Function Statement with the Async modifier. An Async function can have a return type of Task or Task . An example later in this topic shows an Async function that has a return type of Task .
Async Sub procedures are primarily used for event handlers, where a value can't be returned. An Async Sub procedure can't be awaited, and the caller of an Async Sub procedure can't catch exceptions that the Sub procedure throws.
An Async procedure can't declare any ByRef parameters.
The following example uses the Sub statement to define the name, parameters, and code that form the body of a Sub procedure.
In the following example, DelayAsync is an Async Function that has a return type of Task . DelayAsync has a Return statement that returns an integer. Therefore, the function declaration of DelayAsync must have a return type of Task(Of Integer) . Because the return type is Task(Of Integer) , the evaluation of the Await expression in DoSomethingAsync produces an integer, as the following statement shows: Dim result As Integer = Await delayTask .
The startButton_Click procedure is an example of an Async Sub procedure. Because DoSomethingAsync is an Async function, the task for the call to DoSomethingAsync must be awaited, as the following statement shows: Await DoSomethingAsync() . The startButton_Click Sub procedure must be defined with the Async modifier because it has an Await expression.
Required. Name of an interface implemented by this procedure's containing class or structure.
Required. Name by which the procedure is defined in interface .
Required. Object variable declared with the data type of the class or structure that raises the event.
Required. Name of the event this procedure handles.

Sign up or log in to customize your list.

more stack exchange communities

company blog

Stack Overflow for Teams
– Start collaborating and sharing organizational knowledge.

Create a free Team
Why Teams?

Asked
9 years, 1 month ago

Modified
9 years, 1 month ago

29 2 2 silver badges 10 10 bronze badges

Mature Eva
Taiwan Girl Sexy Lingerie Show
Sexy Ass Girl

Private Sub Click

Report Page