Antipyretics for children are prescribed by a pediatrician. But there are emergency situations for fever when the child needs to be given medicine immediately. Then the parents take responsibility and use antipyretic drugs. What is allowed to give to infants? How can you bring down the temperature in older children? What medicines are the safest?
Let's look at the VBA TextBox class, which allows you to place a text box on a form. The vba TextBox class allows you to create both multi-line and single-line text input areas, although in the latter case it is more convenient to use the MsgBox function. You can also add scrollbars and define the maximum length of input characters. The TextBox can also be used as a password entry component.
Let's first look at the main properties of the TextBox class and its base event, and then write an example.
TextBox VBA language class events
value or Text- the text that is entered in the TextBox
visible– allows you to hide (false) or show again (true) the element.
multiline– this property allows you to specify whether the text field will be single-line (false) or multi-line (true).
word wrap- the property is relevant to use if MultiLine contains the value true, in this case, if WordWrap is set to true, then the text will automatically wrap to a new line when the border of the TextBox text field is reached.
scrollbars– allows you to specify whether or not scrollbars will be displayed. The property can take the following values:
- 0 – fmScrollBarsNone(scrollbars are missing)
- 1– fmScrollBarsHorizontal(horizontal scrollbar)
- 2 – fmScrollBarsVertical(vertical scrollbar)
- 3 – fmScrollBarsBoth(horizontal and vertical scrolling)
PasswordChar- allows you to set the character that will be displayed instead of the input data. The property is relevant when entering a password.
MaxLength- allows you to specify the maximum number of characters that can be entered in the text field. The default is 0, which means there is no limit.
Like other controls, the TextBox class has events, the main event for a TextBox is change- it occurs every time a character is entered into the text field. You may have seen this picture: when entering a verification code, until its length reaches the specified one (for example, 12 characters), the “Check” button will not be available. This can also be done here.
Okay, now it's time to practice. I will not delve into all the details again, as in previous articles, so I will say it straight: add a new module and a new form to the project. Select the Caption () control from the ToolBox window, add it to the form, let it be at the top, put a text box (TextBox) under it, and at the very bottom - let there be a button ( labeled “Check”). Well, as before, in the code editor for the module, we write the procedure:
Here we are processing a single click on the button, when the button is clicked, the Caption property of the Label1 object (Inscription) will be assigned the contents of the TextBox1 text field (Text property).
| Private Sub TextBox1_Change() Dim LenText As Byte LenText = Len(TextBox1.Text) If LenText = 12 Then bCheck.Enabled = True Else bCheck.Enabled = False End If End Sub |
Here the Change event is processed for the text field of the TextBox1 object of the vba TextBox class. The LenText variable will store the length of the entered text, each time you enter data, the length will be checked, if it is equal to 12, then the button will be activated, otherwise the 0 button will be inactive.
In this procedure, the initial values are set, some of them can also be set in the Properties window, but for clarity, I decided to write everything in the program code. There is nothing to worry about here, as soon as the form is loaded into memory, the “Check” button will be inactive, the font size for the text field and label is set to 20, we also set the maximum text length for the TextBox VBA class of the language and its single line.
Option 5
Create custom view form:
Create a private subroutine procedure in the form module that is launched when the button labeled "Calculation" is clicked. The procedure should calculate the maximum amount accrued for the daily work of any of the employees of the position, the name of which is entered in the text field Field1, on the day of the week, the number of which is entered in Field2. The accrued amount is calculated as the product of mining and the rate. The procedure should place the result of the calculation in Field3.
To create a user form, go to the Visual Basic Code Editor (Alt+F11) and select Insert->UserForm from the menu. Then you need to add the necessary components and configure all the parameters of the form and components - size, font, titles.
In this case, three TextBox elements are needed (TextDolgn - position, TextDen - day of the week, TextMaks - maximum accrued amount), two buttons (Calculate and Exit) and 4 labels. All properties can be set in the Properties panel.
If you double-click the Calculation button, the editor will create a procedure template. Let's add text to it:
Private Sub btnCalc_Click()There are a lot of checks here. First, check if all the data is entered. If not, we will inform the user about this, set the input focus to a blank field (for the convenience of the user, so that he does not poke the mouse once again) and exit the procedure.
Dim r As Integer "line number
Dim c As Integer "column number
Dim maxSum As Integer "the maximum accrued amount
Dim rng As Range "custom range
textMaks.Text = "" "clearing the field from the previous search
"if the title of the position is not entered, then the message and exit from the procedure
If textDolgn.Text = Empty Then
MsgBox "Enter job title!"
textDolgn.SetFocus "focus on the input field
exit sub
End If
"if the day number is not entered, then the message and exit from the procedure
If textDen.Text = Empty Then
MsgBox "Enter the number of the day of the week (1 to 7)!"
textDen.SetFocus
exit sub
End If
"Non-numeric day value entered
If Not (IsNumeric(textDen.Text)) Then
MsgBox "Non-numeric day of the week (required 1 to 7)!"
textDen.SetFocus
exit sub
"Invalid weekday number entered
ElseIf(CInt(textDen.Text)< 1) Or (CInt(textDen.Text) >7) Then
MsgBox "Invalid day of the week (required from 1 to 7)!"
textDen.SetFocus
textDen.Text = "" "clear field
exit sub
End If
"start loop from line 3
r=3
"day number from input field
c = CInt(textDen.Text)
Set rng = Sheets(1).UsedRange
maxSum = -1
For r = 3 To rng.Rows.Count
"if the specified job title is found in the cell
If InStr(1, Cells(r, 2), textDolgn.Text, vbTextCompare) > 0 Then
"if the sum is greater than the maximum, then change the value of the maximum to a new one
If CInt(Cells(r, 3)) * CInt(Cells(r, c + 3)) > maxSum Then maxSum = CInt(Cells(r, 3)) * CInt(Cells(r, c + 3))
End If
Next r
If maxSum = -1 Then
MsgBox "The specified position was not found"
textMax.Text = "-"
Else
textMaks.Text = CStr(maxSum)
End If
Set rng = Nothing "freeing memory
end sub
Then we check if the day number is entered correctly. Firstly, it must be a number, and secondly, a number from 1 to 7. If something is wrong, we clear the input field, set focus on it and exit the procedure, again informing the user about an unsuccessful attempt.
If everything is fine, we start searching for the desired values. Let's define a custom range to be independent of the number of rows. Then you need to find the maximum amount in the loop. Cell values need to be compared with something. Here we cannot simply substitute the first cell in a row or column, then what needs to be selected by the parameter - position. But we see that the accrued amount cannot be less than zero - the rate cannot be negative, and the number of working hours too. Well, in this case, we do not consider the possibility of a "red reversal" - that is, corrections for previous periods. So let's take the initial maximum amount equal to -1 (minus one).
We start a loop through the cells of the column with the specified number of the day (this is the value of the input field plus 3).
See if the cell contains the specified job title. You can simply compare two strings, but this is inconvenient, since you will need to enter the position in full and correctly. Therefore, we use the InStr function, which returns a value greater than 0 if the specified substring is found in the string. You could use the Like function, which also compares two strings. It's up to the developer.
If the position in the cell is found, we multiply the rate by the number of hours worked and compare the result with the maximum amount. If the result is greater, then change it to a new value.
Don't forget to convert string values to numbers and vice versa where necessary. Even if Excel itself understands where what is contained, it is better to do this for reliability.
After going through the cycle, we look, if the maximum amount is -1 (initial value), then there is no such position. We display a message, and put a dash in the field for clarity.
If the sum is not equal to -1, then we place the found value in the textMaks field.
We free the memory of the object at the end of the program.
There is also text for the Exit button:
Private Sub btnExit_Click()To run the form, place a button on the data sheet (the "Developer" tab, the "Controls" section, the "Insert" list - select the button from the form controls) and assign the Button1_Click macro to it. Then, in the editor, write the code for this button:
"form upload
Unload Me
end sub
Sub Button1_Click()Everything, you can check the work of the form.
UserForm1.Show
end sub
I have given only 5 options here. If you need another, you can ask in the VK group (see the contact page). But in general, all options are similar. There are only minor differences, especially if you need to output a string with multiple text values. Then you can use the textbox property - Multiline to display multiple lines, and use line break characters to separate.
Text fields are intended for entering and editing text - the TextBox element. Just like with the Label element, the text of the TextBox element can be set or retrieved using the Text property.
By default, dragging an item from a toolbar creates a single-line text box. To display large amounts of information in a text field, you need to use its Multiline and ScrollBars properties. When you set the Multiline property to true, any excess characters that fall outside the margin will wrap to a new line.
You can also make a text box scroll by setting its ScrollBars property to one of the following values:
None : no scrolling (default)
Horizontal : Creates a horizontal scroll when the line length is greater than the width of the text field
Vertical : creates a vertical scroll if the lines don't fit in the text field
Both : creates vertical and horizontal scrolling
The TextBox element has enough power to create an autocomplete field. To do this, we need to bind the property AutoCompleteCustomSource of the TextBox element to some collection from which the data is taken to fill the field.
So, let's add a text field to the form and write the following lines in the load event code:
Public partial class Form1: Form ( public Form1() ( InitializeComponent(); AutoCompleteStringCollection source = new AutoCompleteStringCollection() ( "Kuznetsov", "Ivanov", "Petrov", "Kustov" ); textBox1.AutoCompleteCustomSource = source; textBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource; ) )
The autocomplete mode, represented by the AutoCompleteMode property, has several possible values:
None : no autocompletion
Suggest : Suggests suggestions for input, but doesn't complete
append : appends the entered value to a string from the list, but does not offer options for selection
SuggestAppend : both suggest suggestions for autocomplete and complete the value entered by the user
Word wrap
In order for the text in the TextBox element to wrap words, you need to set the property word wrap set to true . That is, if one word does not fit on the line, then it is transferred to the next one. This property will only work for multiline text fields.
Password entry
Also, this element has properties that allow you to make it a field for entering a password. So, for this you need to use PasswordChar and UseSystemPasswordChar .
The PasswordChar property has no value by default, if we set it to any character, then this character will be displayed when entering any characters in the text field.
The UseSystemPasswordChar property has a similar effect. If we set its value to true , then instead of the entered characters, the password character accepted in the system, for example, a dot, will be displayed in the text field.
Of all the events of the TextBox element, the TextChanged event should be noted, which fires when the text in the element changes. For example, let's place a label on the form in addition to the text field and make it so that when the text in the text field changes, the text on the label also changes:
Public partial class Form1: Form ( public Form1() ( InitializeComponent(); textBox1.TextChanged += textBox1_TextChanged; ) private void textBox1_TextChanged(object sender, EventArgs e) ( label1.Text = textBox1.Text; ) )
This example uses Dialog window- UserForm1 as well as controls Text field- TextBox1, TextBox2, TextBox3 for which the context menu is created. Note that the name of the context menu, which is the same as the name of the form (optional) must be unique. if a toolbar with the same name already exists, you will get an error when trying to create a new toolbar. This error can be avoided by checking whether there is a panel with this name before creating the panel, or by not using the optional Name argument of the.Add method (see similar example2)Option II. If a previously download the control iemenu.ocx (IE Popup Menu) and add it to the list of available links, then you can create a context menu using this control (see example 3)
Option I
In order to fill cells with the data of a text field that has its MultiLine property set to True , you can use line-by-line reading. If the cells are already filled, then delete the contents and formats of these cells (column), after checking whether they are protected
Option I(line by line reading)
Option II.(no line-by-line reading)
If there is a need to enter only unique (previously non-repeating) characters in the text field. In other words, you need to prohibit the input of characters that are already present in the text, then use the option below, where the TextBox1_KeyPress event is used to prohibit the input of duplicates (repetitions), and the TextBox1_KeyDown event to prohibit the insertion of copied data that may contain duplicates (repetitions)
Option I
You can use the following option to prevent the entry of unnecessary characters, such as characters that cannot be used in a file name. Where the TextBox1_KeyPress event is used to prevent the input of unnecessary characters, and the TextBox1_KeyDown event is used to prevent the insertion of copied data, which may contain prohibited characters.
In order to determine which of the SHIFT, CTRL, ALT keys were pressed when working with a text name named TextBox1, you can use the following option. The display of information in the header of the userform on which the text field is located is used only for clarity and, of course, is not mandatory.
Note: In a similar way, you can change the font for other controls, incl. RefEdit, Label, ComboBox, ListBox ...If, in the course of work, you need to be able to select the necessary workbooks, for example, using a standard dialog box, and also get the path (or just the file name) of the selected workbook, and without opening it, then simply create a text box with the name TextBox1 , and use the button on the right as needed (the button will appear without your participation)
