UserForm VBA Course English

[Yasser Fathi Albanna]

السادة أعضاء المنتدى الأفاضل

منقول للفائدة شرح UserForm VBA Course English

لعله يفيد

To add a UserForm, do exactly as you would if you were adding a new module

 

 

The UserForm window and "Toolbox" will appear

 

If you don't see the Properties window, make sure that it is shown and then start by editing the name of the UserForm (so that you can easily find it later on

 

A UserForm has its own events, just like a workbook or a worksheet. To add events, double click on the UserForm window :

 

Now let's create two events as an example of how they work. The first event will define the initial dimensions of the UserForm, and the second will increase each of its dimensions by 50 pixels when the user clicks

The event UserForm_Initialize will fire when the UserForm is launched

 

Private Sub UserForm_Initialize()
    my_userform.Height = 100
    my_userform.Width = 100
End Sub

To simplify the code, we can use Me instead of the name of the UserForm (since this code is within the UserForm that we're working with

Private Sub UserForm_Initialize()
    Me.Height = 100
    Me.Width = 100
End Sub

The second event will fire when the user clicks on the UserForm

Private Sub UserForm_Initialize()
    Me.Height = 100
    Me.Width = 100
End Sub

Private Sub UserForm_Click()
    Me.Height = Me.Height + 50
    Me.Width = Me.Width + 50
End Sub

Launch a UserForm

To launch a UserForm in a procedure, use Show

Sub show_userform()
    my_userform.Show
End Sub

Controls have all sorts of properties, and the events associated with them vary, but for now we will only look at a few of the many possible uses of controls in VBA coding

Let's start by adding the following 3 controls : a Label, a TextBox and a CommandButton

Now let's edit the names and properties of the controls (using the Caption property, which contains the text). We want the following result

For now, when we enter a number and press OK, nothing happens

To make something happen, we'll start by adding an event that puts the value of the text box into cell A1 and closes the UserForm

You can access the options that you see immediately below this text by double clicking on a control

The drop-down list contains different controls and the UserForm

Select a button and an event Click

Private Sub CommandButton_validate_Click()

    Range("A1") = Textbox_number.Value
    'Textbox_number is the name of the text box
    'Value is the property that contains the value of the text box
   
    Unload Me
    'Unload closes the UserForm
    'We are using Me in place of the name of the UserForm (because this code is within the UserForm that we want to close)
End Sub

The value will now be saved in cell A1 before the closing of the UserForm

Add a second Label and edit the following properties : Caption, Forecolor (color : red) and Visible (False, to hide the control by default

Now let's add an event that will be fired when the value of the text box is changed, which will display an error message if the value is not numerical

Private Sub Textbox_number_Change()
    If IsNumeric(Textbox_number.Value) Then 'IF numerical value ...
        Label_error.Visible = False 'Label hidden
    Else 'OTHERWISE ...
        Label_error.Visible = True 'Label shown
    End If
End Sub

The value will be tested each time a character is entered

We still need to prevent the validation of the form if the value is not numerical

Private Sub CommandButton_validate_Click()
    If IsNumeric(Textbox_number.Value) Then 'IF numerical value ...
        Range("A1") = Textbox_number.Value 'Copy to A1
        Unload Me 'Closing
    Else 'OTHERWISE ...
        MsgBox "Incorrect value"
    End If
End Sub

So as not to leave the right-hand side of the UserForm blank when there isn't any error, we can reduce its size by modifying the UserForm's Width property

Private Sub Textbox_number_Change()
    If IsNumeric(Textbox_number.Value) Then 'IF numerical value ...
        Label_error.Visible = False 'Label hidden
        Me.Width = 156 'UserForm Width
    Else 'OTHERWISE ...
        Label_error.Visible = True 'Label shown
        Me.Width = 244 'UserForm Width
    End If
End Sub

See result below

Checkboxes

Here is an example of how to use the CheckBox

When a checkbox is checked/unchecked, the value of the corresponding cell can be modified by using the Click event

Private Sub CheckBox1_Click() 'Number 1
    If CheckBox1.Value = True Then 'If checked ...
       Range("A2") = "Checked"
    Else 'If not checked ...
       Range("A2") = "Unchecked"
    End If
End Sub

Private Sub CheckBox2_Click() 'Number 2
    If CheckBox2.Value = True Then 'If checked ...
       Range("B2") = "Checked"
    Else 'If not checked ...
       Range("B2") = "Unchecked"
    End If
End Sub

Private Sub CheckBox3_Click() 'Number 3
    If CheckBox3.Value = True Then 'If checked ...
       Range("C2") = "Checked"
    Else 'If not checked ...
       Range("C2") = "Unchecked"
    End If
End Sub

In this example, the checkboxes start out unchecked when the UserForm is first opened
To check the boxes when the value of the corresponding cell is "Checked", we'll run a test when the UserForm is activated, using UserForm_Initialize

Private Sub UserForm_Initialize() 'Check box if "Checked"
    If Range("A2") = "Checked" Then
        CheckBox1.Value = True
    End If
   
    If Range("B2") = "Checked" Then
        CheckBox2.Value = True
    End If
   
    If Range("C2") = "Checked" Then
        CheckBox3.Value = True
    End If
End Sub

The Option Buttons

The user may only select one Option Button per "group", which is not the case with checkboxes

To create a group, first insert a Frame and then OptionButton

 

Once the form has been submitted, we will enter data into the cell that corresponds to the column numbers and row_value chosen

In order to know which option button was chosen, we could do the same as in the previous example (with the checkboxes) but we will do it with a loop to reduce the length of the code

We're going to use a For Each loop this time, a kind of loop that we haven't yet introduced. This kind of loop makes it possible to execute instructions for each object in an "object group"

Private Sub CommandButton1_Click()
    Dim column_value As String, row_value As String
   
    'Loop for each Frame_column control
    For Each column_button In Frame_column.Controls
        'If the value of the control  = True (then, if checked) ...
        If column_button.Value Then
           'The variable "column_value" takes the value of the button text
           column_value = column_button.Caption
        End If
    Next
   
    'Loop for the other frame
    For Each row_button In Frame_row.Controls
        If row_button.Value Then
            row_value = row_button.Caption
        End If
    Next

    Range(column_value & row_value) = "Cell chosen !"
    Unload Me
End Sub

Now this form enters the value "Cell chosen !" into the cell that has been chosen (provided that the form is complete

To avoid a bug, we need to check to make sure that the user has chosen correctly from the two sets of option buttons

In this example, when the form is incomplete, the "Confirm" button will appear in gray (deactived). This is not the simplest solution, but it's a good example of why functions/procedures are useful within a UserForm

Edit the text as well as the Enabled property to deactivate the button

The result will be

In the preceding code, we used 2 For Each loops to retrieve the values of the option buttons. We will now need to use these same values for the "Confirm" button and the Click events for the ten option buttons

So that we don't have to copy the loops for each event, we'll call them using a function

Starting from the preceding code and modifying it, we will achieve this result

Private Function column_value()
'The function returns the value of the text for the button chosen (column_value)
    For Each column_button In Frame_column.Controls
        If column_button.Value Then
            column_value = column_button.Caption
        End If
    Next
End Function

Private Function row_value()
'The function returns the value of the text for the button chosen (row_value)
    For Each row_button In Frame_row.Controls
        If row_button.Value Then
            row_value = row_button.Caption
        End If
    Next
End Function

Private Sub CommandButton1_Click() 'Action that is taken when you click "Confirm your selection"
    Range(column_value & row_value) = "Cell chosen !"
    'column_value and row_value are the values returned by the functions
    Unload Me
End Sub

All we have left to do is to create a procedure that verifies that the buttons have been checked correctly (by calling the two functions), and which will activate the button if necessary

Here again, the test is performed in a separate procedure to avoid copying the code 10x for each of the option button events

Private Sub activate_button()
'Activating the button if the condition is verified
    If column_value <> "" And row_value <> "" Then
    'column_value and row_value are the values returned by the functions
        CommandButton1.Enabled = True
        CommandButton1.Caption = "Confirm your selection"
    End If
End Sub

Private Sub OptionButton11_Click()
    activate_button 'Run the "activate_button" procedure
End Sub
Private Sub OptionButton12_Click()
    activate_button
End Sub
Private Sub OptionButton13_Click()
    activate_button
End Sub
Private Sub OptionButton14_Click()
    activate_button
End Sub
Private Sub OptionButton15_Click()
    activate_button
End Sub
Private Sub OptionButton16_Click()
    activate_button
End Sub
Private Sub OptionButton17_Click()
    activate_button
End Sub
Private Sub OptionButton18_Click()
    activate_button
End Sub
Private Sub OptionButton19_Click()
    activate_button
End Sub
Private Sub OptionButton20_Click()
    activate_button
End Sub

 

أتمنى أن يفيد حضراتكم

ولسه فى باقى

 

[Yasser Fathi Albanna]

نستكمل باقى الكورس

 

ScrollBar

Controls can be used outside of UserForms. In this example, we'll use controls right on a worksheet

Please note that "Creation Mode" must be activated in order to modify a control that is placed on a worksheet (and likewise deactivated in order to use the control

In versions d'Excel lower than 2007 : the button is on the "Controls Toolbox" toolbar

Before we go into detail with this example, please have a look at this

Our goal is to add a background color to a cell and select it based on the position of the scroll bars in the defined 30 row x 10 column area

The properties of the vertical scroll bar

• Min : 1
• Max : 30 (because there are 30 rows)
• Value : the position of the bar (in this case, between 1 and 30)

The horizontal bar is exactly the same except that it has a Max of 10

Here is the code that will run each time there is a change to the Value of the vertical scrollbar

'Gray background color in the cells
Cells.Interior.Color = RGB(240, 240, 240)

'Applying color and selecting the cell
With Cells(ScrollBar_vertical.Value, ActiveCell.Column) 'Identifying the cell using Value
    .Interior.Color = RGB(255, 220, 100) 'Applying Orange Color
    .Select 'Selecting the cell
End With

This code will run when the Change and Scroll events are fired and will execute the instructions no matter which part of the scrollbar is clicked

Here is the code for the vertical scrollbar

Private Sub vertical_bar()
    'Applying gray background color to the cells
    Cells.Interior.Color = RGB(240, 240, 240)
   
    'Applying background color and selecting the cell
    With Cells(ScrollBar_vertical.Value, ActiveCell.Column)
        .Interior.Color = RGB(255, 220, 100) 'Orange
        .Select 'Selecting the cell
    End With
End Sub

Private Sub ScrollBar_vertical_Change()
    vertical_bar
End Sub

Private Sub ScrollBar_vertical_Scroll()
    vertical_bar
End Sub

And here is the code for the horizontal scrollbar

Private Sub horizontal_bar()
    'Applying gray background color to the cells
    Cells.Interior.Color = RGB(240, 240, 240)

    'Applying background color and selecting cell
    With Cells(ActiveCell.Row, ScrollBar_horizontal.Value)
        .Interior.Color = RGB(255, 220, 100) 'Orange
        .Select 'Selecting the cell
    End With
End Sub
Private Sub ScrollBar_horizontal_Change()
    horizontal_bar
End Sub

Private Sub ScrollBar_horizontal_Scroll()
    horizontal_bar
End Sub

ComboBox and ListBox

This is the starting point for our next example

 

When the UserForm is launched, we want the 4 countries to be loaded into the drop-down list (using the AddItem method

Private Sub UserForm_Initialize()
    For i = 1 To 4 ' => to list the 4 countries
       ComboBox_Country.AddItem Cells(1, i) 'Add the values of cells A1 through A4 using the loop
   Next
End Sub

When the value of the drop-down list changes, we want to add the cities in the chosen country using a loop similar to the previous one

In order to do this, we need the column number and the row number

The property ListIndex contains the number of the selection in the drop-down list (unlike Value, which contains the value of the list item). Please note that ListIndex begins with the number 0

So the column number is given by

column_number = ComboBox_Country.ListIndex + 1

To obtain the number of rows in the chosen country's column, we can search for the row number of the last in a sequence of non-empty cells

rows_number = Cells(1, column_number).End(xlDown).Row

Using this information, it is now possible to create a loop to add the cities to the list area

Private Sub ComboBox_Country_Change()
    'Emptied list area (otherwise the cities are added immediately)
    ListBox_Cities.Clear
   
    Dim column_number As Integer, rows_number As Integer
   
    'The number of the selection (ListIndex starts with 0) :
    column_number = ComboBox_Country.ListIndex + 1
    'Number of rows in the chosen country's column :
    rows_number = Cells(1, column_number).End(xlDown).Row

    For i = 2 To rows_number ' => to list cities
       ListBox_Cities.AddItem Cells(i, column_number)
    Next
End Sub

Note : we could have abbreviated the code above, but we haven't because that would make it much less readable

Private Sub ComboBox_Country_Change()
    ListBox_Cities.Clear
    For i = 2 To Cells(1, ComboBox_Country.ListIndex + 1).End(xlDown).Row
        ListBox_Cities.AddItem Cells(i, ComboBox_Country.ListIndex + 1)
    Next
End Sub

The city that is chosen will then be entered into the text area

Private Sub ListBox_Cities_Click()
    TextBox_Choice.Value = ListBox_Cities.Value
End Sub

 

 

لسه فى باقى

تم تعديل يناير 14, 2015 بواسطه Eng : Yasser Fathi Albanna

[ياسر خليل أبو البراء]

جزاكم الله خيرا أخي الحبيب ياسر

ولكن رويدا رويدا كل يوم شوية عشان اللي عايز يتعلم يقدر يستفيد

تقبل تحياتي

[Yasser Fathi Albanna]

نستكمل

 

Now let's try a little exercise to practice using controls

This is how we start the exercise

 

By now it should be clear that the goal here is to fill the table using the form

A few thinks to keep in mind

• List the countries based on the list on the second worksheet
• Verify the contents of the controls before adding a new contact
• After inserting a value, reinitialize the values of the controls without closing the form

Take a moment to work through this exercise before looking at the solution

This is one way that you could solve this problem

Our first action should be to increase the Zoom property of the UserForm to 120 to make it easier to use the form

We have already covered option button tests (in the first controls page), so we are using a simpler solution here

"Mrs" is chosen by default (Value property : True), which means that we don't have to verify that a salutation has been chosen

The "Close" button

Private Sub CommandButton_Close_Click()
    Unload Me
End Sub

The contents of the drop-down list

Private Sub UserForm_Initialize() 'Loading the list when the UserForm is opened
   For i = 1 To 252 'List of 252 countries froim the "Country" worksheet
      ComboBox_Country.AddItem Sheets("Country").Cells(i, 1)
   Next
End Sub

 

 

Verification of controls

One simple solution would be to display a dialog box if any of the controls is empty

Private Sub CommandButton_Add_Click()
    If TextBox_Last_Name.Value = "" Or TextBox_First_Name.Value = "" Or TextBox_Address.Value = "" Or TextBox_Place.Value = "" Or ComboBox_Country.Value = "" Then
        MsgBox "Form incomplete"
    Else
        'Instructions for inserting a contact here ...
    End If
End Sub

But to make things a bit more complicated, each control should be tested individually, and if any of them is empty, its Label text color should be changed to red

Private Sub CommandButton_Add_Click()
    'Setting Label text color to black
    Label_Last_Name.ForeColor = RGB(0, 0, 0)
    Label_First_Name.ForeColor = RGB(0, 0, 0)
    Label_Address.ForeColor = RGB(0, 0, 0)
    Label_Place.ForeColor = RGB(0, 0, 0)
    Label_Country.ForeColor = RGB(0, 0, 0)

    'Content controls
    If TextBox_Last_Name.Value = "" Then 'IF no "name" provided ...
        Label_Last_Name.ForeColor = RGB(255, 0, 0) 'Set Label "name" color to red
    ElseIf TextBox_First_Name.Value = "" Then
        Label_First_Name.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Address.Value = "" Then
        Label_Address.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Place.Value = "" Then
        Label_Place.ForeColor = RGB(255, 0, 0)
    ElseIf ComboBox_Country.Value = "" Then
        Label_Country.ForeColor = RGB(255, 0, 0)
    Else
        'Instructions for inserting a contact here ...
    End If
End Sub

Inserting data

The following code should be inserted in the place indicated in the code above (see commentary

Dim row_number As Integer, salutation As String

'Choice of Salutation
For Each salutation_button In Frame_Salutation.Controls
    If salutation_button.Value Then
        salutation = salutation_button.Caption 'Salutation chosen
    End If
Next

'row_number = row number of the last non-empty cell in the column +1
row_number = Range("A65536").End(xlUp).Row + 1

'Inserting values into the worksheet
Cells(row_number, 1) = salutation
Cells(row_number, 2) = TextBox_Last_Name.Value
Cells(row_number, 3) = TextBox_First_Name.Value
Cells(row_number, 4) = TextBox_Address.Value
Cells(row_number, 5) = TextBox_Place.Value
Cells(row_number, 6) = ComboBox_Country.Value

'After insertion, the initial values are replaced
OptionButton1.Value = True
TextBox_Last_Name.Value = ""
TextBox_First_Name.Value = ""
TextBox_Address.Value = ""
TextBox_Place.Value = ""
ComboBox_Country.ListIndex = -1

Overall view

That's all, and here you have the complete code for the exercise and the downloadable Excel file

Private Sub CommandButton_Close_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize() 'List of 252 countries on the "Country" worksheet
   For i = 1 To 252
       ComboBox_Country.AddItem Sheets("Country").Cells(i, 1)
   Next
End Sub

Private Sub CommandButton_Add_Click()
    'Setting Label color to black
    Label_Last_Name.ForeColor = RGB(0, 0, 0)
    Label_First_Name.ForeColor = RGB(0, 0, 0)
    Label_Address.ForeColor = RGB(0, 0, 0)
    Label_Place.ForeColor = RGB(0, 0, 0)
    Label_Country.ForeColor = RGB(0, 0, 0)

    'Content controls
    If TextBox_Last_Name.Value = "" Then 'If no "name" provided ...
        Label_Last_Name.ForeColor = RGB(255, 0, 0) 'Set Label "name" color to red
    ElseIf TextBox_First_Name.Value = "" Then
        Label_First_Name.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Address.Value = "" Then
        Label_Address.ForeColor = RGB(255, 0, 0)
    ElseIf TextBox_Place.Value = "" Then
        Label_Place.ForeColor = RGB(255, 0, 0)
    ElseIf ComboBox_Country.Value = "" Then
        Label_Country.ForeColor = RGB(255, 0, 0)
    Else
        'If the form is complete, the values will be inserted onto the worksheet
        Dim row_number As Integer, salutation As String
       
        'Choice of salutation
        For Each salutation_button In Frame_Salutation.Controls
            If salutation_button.Value Then
                salutation = salutation_button.Caption
            End If
        Next

        'row_number = row number of the last non-empty cell in column +1
        row_number = Range("A65536").End(xlUp).Row + 1

        'Inserting values onto the worksheet
        Cells(row_number, 1) = salutation
        Cells(row_number, 2) = TextBox_Last_Name.Value
        Cells(row_number, 3) = TextBox_First_Name.Value
        Cells(row_number, 4) = TextBox_Address.Value
        Cells(row_number, 5) = TextBox_Place.Value
        Cells(row_number, 6) = ComboBox_Country.Value
       
        'After insert, we replace the initial values
        OptionButton1.Value = True
        TextBox_Last_Name.Value = ""
        TextBox_First_Name.Value = ""
        TextBox_Address.Value = ""
        TextBox_Place.Value = ""
        ComboBox_Country.ListIndex = -1
    End If
End Sub

لسة فى باقية

 

تم تعديل يناير 14, 2015 بواسطه Eng : Yasser Fathi Albanna

[Yasser Fathi Albanna]

نستكمل

Why use arrays

Imagine that you are trying to write a procedure in which you will need to store 500 values. If you had to create 500 individual variables to do this, it would be extremely difficult. With an array, storing and working with these values will be much easier

A second reason to use arrays is their "speed". It takes much less time to retrieve data from arrays than from "tables" (made up of cells) on an Excel worksheet

So here is an example that will make this all a little clearer

On the first worksheet ("DS") you will find : a 5000 row by 3 column data set

On the second worksheet you will find : a summary table which accounts for all the "YES" responses by year and by client

In this case, the procedure will use a loop to process the data set and record the number of "YES" responses for each year and each client number, then enter this data into the corresponding cells

Without using arrays, it would take Excel 131.44 seconds to execute this procedure

But by first storing the data (from worksheet "DS") in an array and then carrying out the same calculations (using the array instead of the data set from worksheet "DS"), it will only take 1.74 seconds for the procedure to execute

And if we decided to optimize the procedure by storing only the data containing "YES" responses in the array (which is about 3/4 of the data), it would take only 1.02 seconds

This is a good example of how using an array makes it possible to execute a procedure about 128x faster. This improvement would be even greater if we were working with multiple data sets at once

Now it's easy to see that using arrays makes all the difference in the world

We'll go into the details of this example at the end of the lesson

 

Declaring an array

Here are a few examples of array declarations (if the first 2 don't make sense to you, reread this

'Sample declaration of a 1 dimensional array
Dim array1(4)

'Sample declaration of a 2 dimensional array
Dim array2(6, 1)

'Sample declaration of a dynamic array
Dim array3()

If you can't enter fixed values when you declare an array (because they will depend on the size of the data set, for example), leave the parentheses empty

You don't have to declare a type (string, long, etc.), although in many cases this will slow down the execution of your procedure

Storing data in an array

Let's begin by storing some data in an array :

We want to store 11 x 1 values in this case, so we need to create a 1 dimensional array

'Declaration
Dim array_example(10)

Don't forget that array element numbering begins with 0 (this is standard in programming, so it's a good idea to get in the habit of working this way, even though it is actually possible to change this convention in VBA

Each element in the array will now receive its value

'Storing values in the array
array_example(0) = Range("A2")
array_example(1) = Range("A3")
array_example(2) = Range("A4")
array_example(3) = Range("A5")
array_example(4) = Range("A6")
array_example(5) = Range("A7")
array_example(6) = Range("A8")
array_example(7) = Range("A9")
array_example(8) = Range("A10")
array_example(9) = Range("A11")
array_example(10) = Range("A12")

You can work with or modify each element of the array as though it were a variable

Here is an example in which we use array_example(8

Sub example()
    'Declaration
    Dim array_example(10)
   
    'Storing values in the array
    array_example(0) = Range("A2")
    array_example(1) = Range("A3")
    array_example(2) = Range("A4")
    array_example(3) = Range("A5")
    array_example(4) = Range("A6")
    array_example(5) = Range("A7")
    array_example(6) = Range("A8")
    array_example(7) = Range("A9")
    array_example(8) = Range("A10")
    array_example(9) = Range("A11")
    array_example(10) = Range("A12")
   
    'Test 1
    MsgBox array_example(8) '=> returns : 02.04.2016
   
    'Changing one of the values
    array_example(8) = Year(array_example(8))
   
    'Test 2
    MsgBox array_example(8) '=> returns : 2016
End Sub

A For loop would be an effective way to stock the array faster

'Declaration
Dim array_example(10)

'Storing values in the array
For i = 0 To 10
    array_example(i) = Range("A" & i + 2)
Next

لسه فى باقية

[Yasser Fathi Albanna]

 

جزاكم الله خيرا أخي الحبيب ياسر

ولكن رويدا رويدا كل يوم شوية عشان اللي عايز يتعلم يقدر يستفيد

تقبل تحياتي

 

حاضر يا أخى الحبيب ياسر

يشرفنى أن تكون من أول المشاركين والمشجعين لى

[ahmeditoo]

Eng : Yasser Fathi Albanna

ربنا يجزيك خير ع كل معلومة بتفيدنا بيها 

حاول تعمل قناة ع اليوتيوب وتعمل فيديو افضب يكتير للمبتدئيين زيي :)  

[ماجد2014]

ممتاذ جدا وقد حاولت الاستفاده على قدر معفتى باللغه الانجليزيه نرجوا المساعده بالشرح بالعربى

[Yasser Fathi Albanna]

Eng : Yasser Fathi Albanna

ربنا يجزيك خير ع كل معلومة بتفيدنا بيها 

حاول تعمل قناة ع اليوتيوب وتعمل فيديو افضب يكتير للمبتدئيين زيي :)

أخى ahmeditoo سيتم إنشاء الله فى اقريب العاجل عمل المطلوب

شكرا لك

[Yasser Fathi Albanna]

ممتاذ جدا وقد حاولت الاستفاده على قدر معفتى باللغه الانجليزيه نرجوا المساعده بالشرح بالعربى

حاضر أخى ماجد2014 من عنيا