Commandbutton vba

Commandbutton vba DEFAULT

UserForm Controls - Label, TextBox and CommandButton

UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. Using ActiveX Controls on a Worksheet have been illustrated in detail, in the separate section of "Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet".

Also refer "2. UserForm and Controls - Properties" for properties common to the UserForm and most Controls

Note: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise.

-------------------------------

Contents:

Label

CommandButton

TextBox

-------------------------------

Label

A Label Control displays text and is used to describe other controls viz. it is often used to describe a TextBox, or if you want to display instructions on the form. Use the Caption property to display text, the Left & Top properties to position it, the TextAlign property to align text within the Label, the Font property for font name/style/size, the BackColor and ForeColor properties for background and text colors. You can format a Label either in the Properties Window or using a VBA statement like Label1.Caption = "Enter brief particulars" or using the vba WITH statement as shown below.

Private Sub CommandButton1_Click()

'clicking the command button in the UserForm will format the Label


With Label1

'to display text
.Caption = "Enter brief particulars"

'text alignment set to center

.TextAlign = fmTextAlignCenter

'wrap text
.WordWrap = True

'set font property
.Font.Name = "Arial"

.Font.Size = 12

.Font.Italic = True

'set font color to yellow and background color to red

.ForeColor = RGB(255, 255, 0)
.BackColor = RGB(255, 0, 0)    

End With


End Sub

----------------------------------------------------------------------------------------------------------------------

CommandButton

A CommandButton is typically used to execute a macro. The Click event of the CommandButton is used to attach vba code to a CommandButton of a UserForm. In VBE (Visual Basic Editor), if the UserForm is visible, you can double-click on the CommandButton to access the Click event, or else you can select the name of the CommandButton (in the code module for the UserForm) from the top-left dropdown of the code window and then select Click from the top-right dropdown. Clicking on the CommandButton will run the code which is attached to the Click event or you can insert the name of a macro to be run on clicking the CommandButton. See below example(s):

Example 1 - The Click event which unloads the UserForm on clicking the CommandButton:

Private Sub CommandButton2_Click()
'clicking on the button unloads the UserForm


'display message before closing
MsgBox "Closing UserForm!"


'unloads the UserForm
Unload Me


End Sub

Example 2 - Clicking the CommandButton will call (& execute) another macro whose name has been inserted - used typically when a set of codes are often or repeatedly required to be executed. Refer Images 5a (before Clear Button is clicked) & 5b (after Clear Button is clicked): 

Private Sub CommandButton1_Click()
'clicking on the "Clear" button will call another macro, named clearForm, which will get executed:


clearForm


End Sub

Private Sub clearForm()
'this macro is called on clicking the "Clear" button - clears all controls in the UserForm:


'clear all text appearing in TextBox
TextBox1.Value = ""


'clear the text area of the ComboBox (ie. user-entered or user-selected value)
ComboBox1.Value = ""


'deselect the CheckBox
CheckBox1.Value = False


'deselect the OptionButton
OptionButton1.Value = False


End Sub

--------------------------------------------------------------------------------------------------------------------

TextBox

A TextBox accepts text or data from the user. In addition to the common properties mentioned earlier, its key properties include:

AutoTab Property: A Boolean value (True/False) which specifies whether the tab moves automatically to the next control in the tab order after the the maximum allowable number of characters (as determined by the MaxLength property) are entered in the TextBox by the user. False value (is the default) indicates moving manually to the next control in the tab order when the user presses the Tab key. This property is particularly useful in a case where the TextBox accepts a 5-digit item code number and the tab moves on automatically after 5 digits are entered.

EnterKeyBehavior Property: A Boolean value (True/False) which determines the effect when a user presses the ENTER key in a TextBox. If MultiLine property is set to True, then the True value indicates creating a new line on pressing ENTER while the False value (Default) moves focus to the next control in the tab order. If MultiLine property is set to False, then focus is always moved to the next control in the tab order ignoring the EnterKeyBehavior Property.

MaxLength Property: Specifies the maximum number of characters which can be entered in a TextBox. Specifying a value of 0 indicates there is no maximum limit.

MultiLine Property: A Boolean value (True/False) which determines if text will be displayed in multiple lines or not, in the TextBox. True indicates that the text is displayed in multiple lines, and this is also the default value. See the ScrollBars Property below for how a multiline TextBox can have vertical scroll bars and even a horizontal scroll bar under certain conditions. The WordWrap property is ignored in the single-line setting.

PasswordChar Property: Specifies what characters to display in TextBox instead of the characters actually entered or typed by the user. This property is useful to protect sensitive information or security codes, or to validate a user before allowing to proceed further.

ScrollBars Property: Specifies whether a TextBox has vertical and/or horizontal scroll bars, or none. There are 4 'self-explanatory' settings: (i) fmScrollBarsNone (value 0) - this is the default setting; (ii) fmScrollBarsHorizontal (value 1); (iii) fmScrollBarsVertical (value 2); and (iv) fmScrollBarsBoth (value 3). The scroll bar setting fmScrollBarsNone displays no scroll bar. If AutoSize is set to True, no scroll bar is diplayed because the TextBox enlarges itself to accommodate the additional text or data. If WordWrap is set to True, a multiline TexBox displays no horizontal scroll bar. The scroll bar settings fmScrollBarsHorizontal or fmScrollBarsBoth, display a horizontal scroll bar in a singleline TextBox if the text is longer than the edit region. The scroll bar settings fmScrollBarsVertical or fmScrollBarsBoth, display a vertical scroll bar in a multiline TextBox if the text is longer than the edit region and WordWrap is set to True. To display a horizontal scroll bar in a multiline TexBox, the scroll bar setting should be fmScrollBarsHorizontal, WordWrap should be set to False and the text should be longer than the edit region. Note 1: A horizontal (or vertical) scroll bar is visible only if the control has enough room to include the scroll bar under (or at the right edge of) its edit region.

Text Property: The text in a TextBox is returned or set by this property. A value assigned to the Text property gets automatically assigned to the Value property, and vice-versa.

Example 1: You can Add a TextBox in a UserForm and format it (ie. apply or set properties), either in the Properties Window, or using VBA code as shown below.

Private Sub CommandButton1_Click()
'click command button to create new TextBox and apply properties - a MultiLine TextBox with a vertical scroll bar


Dim txtSampleTextBox As MSForms.TextBox


Set txtSampleTextBox = Controls.Add("Forms.TextBox.1", "txtSampleTB")


With txtSampleTextBox

'set font property
.Font.Name = "Times New Roman"

.Font.Size = 10

'text alignment set to center
.TextAlign = fmTextAlignLeft

'specify size
.Width = 100
.Height = 50

'set position in the UserForm
.Left = 50
.Top = 75

'set behaviour
.MultiLine = True
.WordWrap = True
.AutoSize = False
.ScrollBars = 2

'set focus
.SetFocus

End With


End Sub

Example 2: Enabled Property of a TextBox is particularly useful where you do not want to allow the user to type directly into the TextBox which should be filled only per the user-selected option, say from a ListBox - see below vba code(s):

Private Sub UserForm_Initialize()
'set properties for ListBox and TextBox on initialization of UserForm

'populating ListBox with numbers 1 to 10; display a ControlTip instructing the user to select from ListBox.
With ListBox1

For i = 1 To 10

.AddItem i

Next i


.ControlTipText = "Select Number from ListBox, to enter in TextBox."

End With

'set Enabled Property to False so that the user cannot type directly; Note: ControlTip cannot be displayed when Enabled is set to False
Me.TextBox1.Enabled = False


End Sub

Private Sub ListBox1_Click()
'ListBox click event

'fills TextBox on ListBox selection
TextBox1.Text = ListBox1.Value

End Sub

Example 3: Use TextBox to set up a password - set multiple username-password matches to enable multi-user access.

In this Example we use the PasswordChar Property to check if username and password match, in which case the user is allowed to proceed and UserForm1 gets loaded. UserForm2 (Refer Image 6) contains TextBox1 for entering username and TextBox2 for entering password, and CommandButton1 which on being clicked matches the username & password, and on validating a match the UserForm1 is loaded allowing the user to proceed.

Private Sub UserForm_Initialize()
'set properties for TextBox2 on initialization of UserForm2.

'set maximum length for password field
TextBox2.MaxLength = 5

'specify characters displayed in TextBox instead of the characters actually entered
TextBox2.PasswordChar = "*"

'set yellow as background color
TextBox2.BackColor = RGB(255, 255, 0)


End Sub

Private Sub CommandButton1_Click()
'use TextBox to set up a password; click command button to validate password to proceed to UserForm1.


Dim password As String


'set matching usernames and passwords - (Angelina & 12345), (Brad & 23456) & (George & 34567)

If TextBox1.Text = "Angelina" And TextBox2.Text = "12345" Then

password = "True"

ElseIf TextBox1.Text = "Brad" And TextBox2.Text = "23456" Then

password = "True"

ElseIf TextBox1.Text = "George" And TextBox2.Text = "34567" Then

password = "True"

End If

'if username and password match, the user is allowed to proceed and UserForm1 gets loaded:

If password = "True" Then

MsgBox "Password Validated! Please Continue."

Unload Me

UserForm1.Show

'if if username and password do not match, user is requested to try again:

Else

MsgBox "Incorrect UserName/Password. Try Again."

'clear both TextBox

TextBox1.Text = vbNullString

TextBox2.Text = vbNullString

'set focus to TextBox1

TextBox1.SetFocus

End If


End Sub

Sours: https://www.excelanytime.com/excel/index.php?option=com_content&view=article&id=127:excel-userform-controls-label-textbox-and-commandbutton&catid=79&Itemid=475

How to hide or unhide a Command Button based on specified cell value in Excel?

Supposing you are using a Command Button to trigger a VBA script in your worksheet. When the VBA script is unused in some cases, you need to make the Command Button hidden instead of displaying in the worksheet. And display it again when the VBA script is needed. This article is talking about hiding or showing a Command Button based on a specified cell value in Excel. Please do as follows.

Hide or unhide a Command Button based on specified cell value with VBA code


Hide or unhide a Command Button based on specified cell value with VBA code


You can run the below VBA code to hide or unhide a Command Button based on specified cell value in Excel.

1. Right click the sheet tab which contains the Command Button you need to show hide, then click View Code from the right-clicking menu.

2. In the popping up Microsoft Visual Basic for Applications window, copy and paste the below VBA code into the Code window.

VBA code: Hide or unhide a Command Button based on specified cell value

Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Cells(1, 1).Value <> "1" Then Me.CommandButton1.Visible = True Else Me.CommandButton1.Visible = False End If Application.ScreenUpdating = True End Sub

Note: in the code, Cells(1, 1), 1 and CommandButton1 indicate that the CommandButton1 will be hidden when cell A1 contains number 1, and displayed if cell A1 contains any other values except number 1. See below screenshot. And you can change them based on your need.


Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Read More...Free Download...Purchase... 


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Sours: https://www.extendoffice.com/documents/excel/4161-excel-hide-button-based-on-cell-value.html
  1. Plex recording failed
  2. Reviews dish network
  3. 2019 f250 recall

Create a Macro

Developer Tab | Command Button | Assign a Macro | Visual Basic Editor

With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro which will be executed after clicking on a command button. First, turn on the Developer tab.

Developer Tab

To turn on the Developer tab, execute the following steps.

1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

Customize the Ribbon in Excel

2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).

3. Check the Developer check box.

Turn on the Developer Tab

4. Click OK.

5. You can find the Developer tab next to the View tab.

Developer Tab

Command Button

To place a command button on your worksheet, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Command Button.

Insert a command button control

3. Drag a command button on your worksheet.

Assign a Macro

To assign a macro (one or more code lines) to the command button, execute the following steps.

1. Right click CommandButton1 (make sure Design Mode is selected).

2. Click View Code.

View Code

The Visual Basic Editor appears.

3. Place your cursor between Private Sub CommandButton1_Click() and End Sub.

4. Add the code line shown below.

Add Code Line

Note: the window on the left with the names Sheet1 (Sheet1) and ThisWorkbook is called the Project Explorer. If the Project Explorer is not visible, click View, Project Explorer. If the Code window for Sheet1 is not visible, click Sheet1 (Sheet1). You can ignore the Option Explicit statement for now.

5. Close the Visual Basic Editor.

6. Click the command button on the sheet (make sure Design Mode is deselected).

Result:

Excel Macro Result

Congratulations. You've just created a macro in Excel!

Visual Basic Editor

To open the Visual Basic Editor, on the Developer tab, click Visual Basic.

Open the Visual Basic Editor

The Visual Basic Editor appears.

Visual Basic Editor

Sours: https://www.excel-easy.com/vba/create-a-macro.html
# Aula 29 Curso de Excel VBA: CommandButton (botões do menu)

View Page in My Language

Attaching macros to command button in Excel First let’s have a look at the multiple macros from the last video.

  1. Click on Tools in the menu bar of the opened file.
  2. Select Macro and the Visual Basic Editor from the pop-up options
  3. Double-click on module to view the macros firstmacro(), secondmacro() and runmultiplemacros()
  4. Close the VB editor window
  5. Click on view in the menu bar, select Toolbars and Control ToolBox from the pop-up options
  6. From the various icons in the toolbox select the command button
  7. Click, drag and draw on your Excel worksheet a command button of an appropriate size
  8. Right-Click on the button and select ‘properties’
  9. In the properties window rename the caption to Run Multiple Macros
  10. Close the properties window
  11. Right-click the command button again. From the options menu select ‘view code’
  12. Enter the name of the macro that runs multiple macros ‘runmultiplemacros’ between the two already existing lines of code
  13. Close the code window
  14. Click on ‘exit design mode’ icon of the control toolbox
  15. Click on the command button to see how both the firstmacro() and the secondmacro() are executed

The Excel training video demonstrates the complete process.

 

Further reading: Add a button and assign a macro to it in a worksheet

Sours: https://www.exceltrainingvideos.com/attach-vba-code-to-command-button/

Vba commandbutton

Referring to a Control on a Worksheet

Last Updated on Fri, 08 Oct 2021 | Excel VBA Macros

Fortunately, Excel lets us refer to an ActiveX control on a worksheet by using its name, without reference to the OLEObjects collection. For instance, if we place a command button on a worksheet, Excel will give it the default name CommandButton1. Both of the following lines set the height of this command button to 20 points:

ActiveSheet.OLEObjects("CommandButton1").Height = 20 ActiveSheet.CommandButtonl.Height = 20

Unfortunately, however, the properties and methods that we access in this manner are the properties and methods of the OLEObject, not the control itself. These properties are shown in Table 14-4.

Thus, for instance, while we can set the Height property of the command button, we cannot set its Caption property in this way. That is, the code:

ActiveSheet.OLEObjects("CommandButton1").Caption = "ClickMe" will generate an error.

The way to reach the members of the control itself is to use the Object property of an OLEObject object, which returns the underlying control, and makes its properties and methods accessible. Thus, the following two lines each set the button's caption:

ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "ClickMe" ActiveSheet.CommandButton1.Object.Caption = "ClickMe"

In addition to the standard properties available for ActiveX controls, the following properties can be used with ActiveX controls embedded in sheets in Microsoft Excel:

BottomRightCell

Returns a Range object that represents the cell that lies under the lower-right corner of the object.

LinkedCell

Returns or sets the worksheet range that is linked to the value of the control. Thus, if we place a value in the linked cell, the control will assume this value, and vice-versa.

ListFillRange

Returns or sets the worksheet range that is used to fill a list box control.

Placement

Returns or sets the way that the control is attached to the cells below it. The possible values are the XlPlacement constants: xlMoveAndSize, xlMove, and xlFreeFloating.

PrintObject

Prints the control when the worksheet is printed if this property is set to True. TopLeftCell

Returns a Range object that represents the cell that lies under the top-left corner of the object.

ZOrder

Returns the ZOrder position of the control.

Note also that Table 14-4 has some properties that are not properties of controls themselves. They relate to the OLEObject, which is the container for the control, and thus to the control's relationship with the worksheet. For instance, the code:

ActiveSheet.CommandButtonl.TopLeftCell.Address returns the address of the top-left cell of the worksheet that lies under the control (or rather, the control's container: the OLEObject).

As another example, the following code will locate the top-left cell under the command button and then scroll the active window so that this cell (and therefore the command button) is at the upper-left corner of the window:

Dim rng As Range

Set rng = ActiveSheet.CommandButtonl.TopLeftCell With ActiveWindow

.ScrollRow = rng.Row .ScrollColumn = rng.Column End With

It is important to note that some properties and methods of some Excel objects are disabled when an ActiveX control has the focus. For example, the Sort method of the Range object cannot be used when a control is active. Since a control on a worksheet remains active after it is clicked, the following code will fail:

Private Sub CommandButtonl Click

Range("A:A").Sort Key1:=Range("A:A") End Sub

(We will discuss the sort method in Chapter 19. Don't worry about that now.) This is one disadvantage of placing controls directly on worksheets.

Of course, one way to avoid this problem is to activate another object before calling the sort method. For instance, we can amend the previous code as follows:

Private Sub CommandButtonl Click Range("A:A").Activate Range("A:A").Sort Key1:=Range("A:A") CommandButtonl.Activate ' Optional

End Sub

It is also worth mentioning that if you save an Excel 97 or Excel 2000 workbook in Excel 5.0/95 Workbook file format, all ActiveX control information will be lost.

Continue reading here: Adding a Control to a Worksheet Programmatically

Was this article helpful?

Related Categories

Excel 2007, Excel Will

Sours: https://www.engram9.info/excel-vba-macros/referring-to-a-control-on-a-worksheet.html
Excel VBA - Command Button

CommandButton object (Access)

This object corresponds to a command button. A command button on a form can start an action or a set of actions. For example, you could create a command button that opens another form. To make a command button do something, you write a macro or event procedure and attach it to the button's OnClick property.

ControlTool
Command buttonCommand button

You can display text on a command button by setting its Caption property, or you can display a picture by setting its Picture property.

Note

You can create over 30 different types of command buttons with the Command Button Wizard. When you use the Command Button Wizard, Microsoft Access creates the button and the event procedure for you.

Events

Methods

Properties

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Sours: https://docs.microsoft.com/en-us/office/vba/api/access.commandbutton

Now discussing:

CommandButton (btn)

CommandButton - This control allows the user to perform a specific action and will run a separate subroutine.


If you want to have multiple command buttons executing the same procedure, this can be done using a class module.
You should consider making the Cancel button the default for forms that support operations that can't be undone (such as delete).
You can always add one and not make it visible
Font - Ms Sans Serif, bold, 10


Arrows
Use Wingding3
Left Arrow - (t) - Alt + 116
Right Arrow - (u) - Alt + 117
Up Arrow - (p) - Alt + 112
Down Arrow (q) - Alt + 113
font size - regular 10, can change colour to Desktop
size (20 by 20)


Default BackColor = System > Button Face
Default ForeColor = System > Button Text


RGB(169,19,19) = 1250217
You should always have the Cancel button on the right hand side of your dialog boxes


OLD COLOUR = RGB(178,34,34) = 2237102 [glCOLOURRED]

Properties

Accelerator Lets you specify a character that will be underlined in the caption to provide a shortcut key
AutoSizeAllows the commandbutton to resize automatically to display its entire contents
BackColorThe background colour of the button
BackStyleThe background style of the button
CancelIf you want your dialog box to close when you press Esc then change the Cancel property to True for the command button that closes the form.
CaptionThe text that appears on the command button
DefaultYou should always set a command button to be pressed when the user presses Enter. Set it to True
Font 
ForeColorThe foreground colour of the button
LockedSpecifies whether a control can be edited. When a control is locked and enabled, it can still initiate events and can still receive the focus.
MouseIconThe full path of the custom icon
MousePointerSpecifies the type of pointer displayed when the user positions the mouse over the button
Name 
PictureThe full path of a bitmap file to display on the button
PicturePositionThe location of the picture relative to its caption
TakeFocusOnClickSpecifies whether the control takes focus when clicked.
WordWrapIf you want to write more than one line of text then just set the WordWrap property to True

Events

BeforeDragOver 
BeforeDropOrPaste 
Click 
DblClick 
Enter 
Error 
Exit 
KeyDown 
KeyPress 
KeyUp 
MouseDown 
MouseMove 
MouseUp 

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions LimitedTopPrevNext
Sours: https://bettersolutions.com/vba/controls/commandbutton.htm


1474 1475 1476 1477 1478