VBA example - Excel 2007: VBA GUI example


VBA GUI example

This example is a small program to demonstrate the uses of GUI components in Excel 2007. The program contains only one form. It is a form for searching student information. On the form, other controls that we need are a Label, a TextBox, a CommandButton, tow OptionButtons, and a ListBox.
-The Label named Label1 is to display the text: Enter text
-The TextBox named txtsearch is for the user to enter text to search for
-The CommandButton named cmdsearch works to find what the user wants to look for.
-The two OptionButtons--one named OptId and another named OptPhone are used to enable the user to select the search option. We provide two options or choices for search. One is searching by ID and another is searching by Phone Number. The default option or choice is searching by ID.
-The ListBox is used to display the result of searching. It displays information of students that meet the search criteria, otherwise it displays nothing.

The information of students are stored in the Excel worksheet(sheet1) as shown below.
Id Name Sex DOB POB Tel
1 Seyha M 9/7/1983 Kampong Thom 012 330 390
2 Channa M 9/7/1983 Kampong Thom 012 330 390
3 Lavay F 8/8/1985 Kampong Cham 098 765 444
4 Rirak Pong M 9/8/1985 Phnom Penh 089 876 900
5 Chan Ry F 9/8/1985 Phnom Penh 098 765 543
6 Sok Chea F 9/9/1984 Phnom Penh 090 765 780



VBA for Excel: Form for searching information

The VBA code for this example is shown below:
  

Private Sub cmdsearch_Click()
If Optid.Value = True Then
searchByid (txtsearch.Text)
ElseIf OptPhone.Value = True Then
searchByPhone (txtsearch.Text)
End If
End Sub Sub searchByid(id As String)
Dim i As Integer
i = 1

lstresult.Clear
While Cells(i, 1) <> ""
If Cells(i, 1) = txtsearch.Text Then
lstresult.AddItem Cells(i, 1) & Space(5) & Cells(i, 2) & Space(5) & Cells(i, 3) & Space(5) & Cells(i, 4) & Space(5) & Cells(i, 5) & Space(5) & Cells(i, 6)
End If
i = i + 1
Wend End Sub Sub searchByPhone(phone As String)
Dim i As Integer
i = 1

lstresult.Clear
While Cells(i, 1) <> ""
If Cells(i, 6) = txtsearch.Text Then
lstresult.AddItem Cells(i, 1) & Space(5) & Cells(i, 2) & Space(5) & Cells(i, 3) & Space(5) & Cells(i, 4) & Space(5) & Cells(i, 5) & Space(5) & Cells(i, 6)
End If
i = i + 1
Wend End Sub
Private Sub UserForm_Initialize()
Optid.Value = True End Sub


style="display:inline-block;width:336px;height:280px"
data-ad-client="ca-pub-4338096487622707"
data-ad-slot="6318036677">

Comments

remilia comment

 remilia

The Listbox1 named lstresult


2018-05-15
aaa comment

 aaa

11


2017-02-10
Flavio comment

 Flavio

flavio.alqueiroz@yahoo.com.br

i would like to know how i can create a form like this.. because i don´t have idea.
DO you have a video tutorial about how create this?


2015-09-10
Mati comment

 Mati

I made a few changes and it works. This is the first tutorial I have ever read in VBA so I'm not sure if the changes are correct.
1) change the WHILE loop with a FOR loop (the while loop will exit after getting to the right cell without executing the if)
2) in the scheet where you have copy-paste the data make sure to edit the format of the data. Right click on the ID column> something like "edit cells?" (my excel is not in english) and choose the format you wish. String will be easier but you have to write the spaces for the phone numbers.


2015-09-09
devendra comment

 devendra

Sub searchByid(id As String)
Dim i As Integer
i = 1

lstresult.Clear
While Cells(i, 1) <> ""
If Cells(i, 1) = txtsearch.Text Then
//1stresult is not working //
why is not working and what is error this lone .please confirm any.


2014-10-13




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.