A Simple And Useful Userform (Address Book)

Excel Userform - Address Book   


I updated the userform that I did previously to run it in all version of Excel.

With this VBA userform ;
☑️ Data can be added
☑️ Data can be deleted
☑️ Data can be edited
☑️ Data can be searched on the sheet
☑️ Listbox can be filled and emptied with button.
☑️ Listbox can be scrolled with the spin button.

         Id numbers are generated automatically  when new record is added to the worksheet and  when record is removed.
The listbox is empty when the userform is opened.
The entered value in the searching box can be searched on the worksheet. Listbox are filled with the found results .

excel address book

         

VBA Codes of “Save” button : 
Private Sub CommandButton1_Click()
Dim new_reg As Long
If TextBox1.Text = Empty Or TextBox3.Text = Empty Then
MsgBox "Incomplete Data", vbCritical, ""
TextBox1.SetFocus
Exit Sub
End If

For new_reg = 2 To Cells(Rows.Count, 2).End(xlUp).Row  'It is checked whether the record already exists, if there is the record, a warning is given.
If Cells(new_reg, "B") = TextBox1 Then
MsgBox "This name is already registered !", vbInformation, ""
TextBox1 = Empty
Exit Sub: End If: Next

sonsat = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(sonsat, 1) = sonsat - 1
Cells(sonsat, 2) = TextBox1
Cells(sonsat, 3) = TextBox2
Cells(sonsat, 4) = TextBox3
Cells(sonsat, 5) = TextBox4
Cells(sonsat, 6) = TextBox5
Cells(sonsat, 7) = TextBox6
Cells(sonsat, 8) = TextBox7
Cells(sonsat, 9) = TextBox8
Range("A" & sonsat & ":I" & sonsat).Font.ColorIndex = 11  'The font color of the record is set.

MsgBox "Registration is successful", vbInformation
Range("A" & sonsat & ":I" & sonsat).Interior.ColorIndex = 25 'The background color of the record is set.
Call sort_id
Call text_boxes_clear
End Sub
The listbox column widths are automatically adjusted according to the widths of the sheet’s column using VBA codes :
Private Sub CommandButton7_Click() 'Fill All Records To Listbox button
sat = Cells(Rows.Count, "A").End(xlUp).Row
ListBox1.List = Sheets("Main").Range("B2:I" & sat).Value
With ListBox1
        For i = 1 To 8
            deg = deg & CLng(Columns(i + 1).Width) & ";"
        Next i
        .ColumnWidths = deg
End With
ListBox1.ColumnCount = 8
...



Read more ...

Adding Data Into The Closed Workbook With Userform

Simultaneously Adding Record  To Two Different Workbooks (Open And Closed Workbooks) With The Userform

           For his process ,we have used Ado Connection again.

With userform in the first workbook,the data can be added easily into the closed workbook without opening workbook.



Read more ...

Excel VBA Cascading Dependent Drop Down Lists

Excel Filter With Dependent Combo Boxes And Pull Filtered Results Into Listbox


excel vba dependent drop down list

          We have used 4 dependent combo boxes (dependent drop down lists)  in this study again. We filtered the data on the sheet with combo boxes,and pulled filtered results into listbox. If desired ,the filtered data can be copied with button to other pages.

In the drop-down lists, the unique values  are sorted in  ascending order. We used ADO Connection to do all these operations quickly.

excel cascading dependent drop down list

✔️ To be able to faster the filtering process, we used ADO  (ActiveX Data Objects). ADO is a subset of the Visual Basic programming language specifically designed for communicating with databases.

✔️ To avoid compatibility problems between 32bit and 64bit ,we organized codes as follows :

excel dependent drop down list


✔️ When the cursor hovers on combo boxes, they are opened as automatically. For this ,following codes :
excel vba dependent combobox

✔️ Codes that allow conditional cascading dependent drop-down lists to run:
Private Sub Userform_initialize()
Set con = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=no"""
#Else
con.Open "provider=Microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=no"""
#End If
Call Combo("")
End Sub

Private Sub ComboBox1_Change()
If Not ComboBox1.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox2_Change()
If Not ComboBox2.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox3_Change()
If Not ComboBox3.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub
Private Sub ComboBox4_Change()
If Not ComboBox4.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox3_Change()
If Not ComboBox3.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Private Sub ComboBox4_Change()
If Not ComboBox4.Text = "" Then
    Call Listbox
    Call Combo(sql)
End If
End Sub

Our template is ready for use .


excel vba dependent drop down list


Read more ...

Excel Dependent Combo Boxes

           Excel Create Dependent Drop Down Lists


We have a Database sheet with 4 columns consisting of supplier, category, product, price columns. To the other sheet (Sample sheet), we want to get the price information of the product we selected according to the supplier from this Database sheet.In the database, there are categories for each supplier ,products separated by these categories.

It should be a tool where we can easily see the categories belonging to the supplier and the products belonging to this category and the price of this product.
For this purpose, we created a userform, we added drop-down lists for each of them to list suppliers, categories and products to the userform, added a textbox control for the price.

When a supplier is selected from the first drop-down list, the second drop-down list should automatically list the categories belonging to this supplier, and the products belonging to this category in the third drop-down list.

These drop-down lists (combo boxes) are called dependent drop down lists(cascading drop down lists).

To create dynamic dependent drop-down lists in Excel, we first need to create dynamic range names. We created 4 dynamic range names based on the column headings of Database sheet :
Supplier | Category | Product | Price

1️⃣ Supplier name refers to : =OFFSET(Database!$A$2,0,0,COUNTA(Database!$A:$A)-1)
2️⃣ Category name refers to : =OFFSET(Database!$B$2,0,0,COUNTA(Database!$B:$B)-1)
3️⃣ Product name refers to : =OFFSET(Database!$C$2,0,0,COUNTA(Database!$C:$C)-1)
4️⃣ Price name refers to : =OFFSET(Database!$D$2,0,0,COUNTA(Database!$D:$D)-1)

We defined the names we created as array in the Userform_Initialize procedure and created the items to be listed in combobox1 using the Dictionary object :
Private Sub UserForm_Initialize()
Dim k As Byte, x As Variant
Me.BackColor = 15658720
For k = 1 To 4
Controls("Frame" & k).BackColor = 15658720
Next
Supplier = Application.Transpose(Range("Supplier"))
Category = Application.Transpose(Range("Category"))
Product = Application.Transpose(Range("Product"))
Price = Application.Transpose(Range("Price"))
Set SD = CreateObject("Scripting.Dictionary")
For Each x In Supplier
SD(x) = ""
Next x
ComboBox1.List = SD.keys
End Sub

We have added the necessary VBA codes to Combobox_Change procedures for to run combo boxes as dependent .
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 And IsError(Application.Match(ComboBox1, Supplier, 0)) Then
Set SD = CreateObject("Scripting.Dictionary")
bul = ComboBox1 & "*"
For Each c In Supplier:
If c Like bul Then SD(c) = ""
Next c
ComboBox1.List = SD.keys
ComboBox1.DropDown
Else
Evn = ComboBox1
If Evn = "" Then Exit Sub
Set d2 = CreateObject("Scripting.Dictionary")
For i = LBound(Category) To UBound(Category)
If Supplier(i) = Evn Then d2(Category(i)) = ""
Next i
tablo2 = d2.keys
ComboBox2.Clear
ComboBox2.List = tablo2
ComboBox2.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
ComboBox1.BackColor = &HC0FFFF
End If
i = Empty
Set d2 = Nothing
End Sub

Private Sub ComboBox2_Change()
If ComboBox1 <> "" Then
If ComboBox2.ListIndex = -1 And IsError(Application.Match(ComboBox2, Category, 0)) Then
Set SD = CreateObject("Scripting.Dictionary")
bul = UCase(ComboBox2) & "*"
For Each c In tablo2
If UCase(c) Like bul Then SD(c) = ""
Next c
ComboBox2.List = SD.keys
ComboBox2.DropDown
Else
Set d3 = CreateObject("Scripting.Dictionary")
If ComboBox1 = "" Or ComboBox2 = "" Then Exit Sub
For i = LBound(Product) To UBound(Product)
If Supplier(i) = ComboBox1 And Category(i) = ComboBox2 Then
d3(Product(i)) = ""
End If
Next i
tablo3 = d3.keys
ComboBox3.Clear
ComboBox3.List = tablo3
ComboBox3.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
End If
ComboBox2.BackColor = &HC0FFFF
End If
i = Empty
Set d3 = Nothing
End Sub

Private Sub ComboBox3_Change()
If ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 <> "" Then
If ComboBox3.ListIndex = -1 And IsError(Application.Match(ComboBox3, Product, 0)) Then
Set SD = CreateObject("Scripting.Dictionary")
bul = UCase(ComboBox3) & "*"
For Each c In tablo3
If c Like bul Then SD(c) = ""
Next c
ComboBox3.List = SD.keys
ComboBox3.DropDown
Else
For i = LBound(Product) To UBound(Product)
If Supplier(i) = ComboBox1 And Category(i) = ComboBox2 And Product(i) = ComboBox3 Then
TextBox1.Value = Price(i)
End If
Next i
End If
ComboBox3.BackColor = &HC0FFFF
End If
i = Empty
End Sub


When any new record(new row) is added to the Database sheet, it will automatically be listed in the dependent drop-down lists on userform.This is the purpose of being dynamic dependent combo boxes.

We have added the following codes to the Worksheet_SelectionChange procedure to display the userform when any cell is selected in column A on Sample sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Not Intersect(Range("A:A"), Target) Is Nothing And Target.Count = 1 Then
UserForm1.Left = Target.Left + 25
UserForm1.Top = Target.Top + 30 - Cells(ActiveWindow.ScrollRow, 1).Top
UserForm1.Show
End If
End Sub


In order to avoid any problems while retrieving data from Database sheet, if there are blank rows between cells, it should be deleted. For this purpose, we have added the following codes that run when leaving this sheet :
Private Sub Worksheet_Deactivate()
Dim LastRow As Long
LastRow = Sheets("Database").Range("A" & Sheets("Database").Rows.Count).End(xlUp).Row
With Sheets("Database").Range("A2:A" & LastRow)
If WorksheetFunction.CountBlank(.Cells) > 0 Then
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End With
End Sub


Price Quote With Userform That Contains Dependent Drop-Down Lists


Products can be quickly and easily entered into the price quote template using the userform that contains dependent(cascading) drop-down lists.
The userform is displayed when clicked any cell in the range A13: A39 on the “Offer” sheet . Product information is fetched from the “Database” sheet with dependent drop-down lists.


Read more ...

Inserting An Animated Gif Into Worksheet

Displaying An Animated Gif Image In Web Browser Control


          The animated gif can be added in Excel sheet.
This can be done through Control Toolbox, Design Mode and “Microsoft Web Browser” Control.



To insert the control:

1️⃣  Go to View > Toolbars > Control Toolbox

2️⃣  Activate Design mode

3️⃣  Choose the last button (hammer picture), then select "Microsoft Web Browser" 

4️⃣  Draw a frame for the image (animated gif)

5️⃣  Disable Design mode and the toolbar

6️⃣  Paste the following code in the VBA editor:

Private Sub Worksheet_Activate()
WebBrowser1.Navigate "about:<html><body scroll='no'><img src=" & ThisWorkbook.Path & "\ani.gif> </img></body></html>"
End Sub

🔹 We need to construct a image file path as correctly.So you need something like this:
WebBrowser1.Navigate "about:<html><body scroll='no'><img src=' C:\animated.gif '> </img></body></html>"

🔹 Change the active sheet of the workbook, and then turn on the sheet containing the animation, to see  if it works.

       🔹 The animated gif image can be added from any web address.For this,the following code can be used :  
Private Sub Worksheet_Activate()
 WebBrowser1.Navigate "about:<html><body scroll='no'><img src='http://http://www.animatedgif.net/animals/birds/birdtoon_e0.gif'> </img></body></html>"
End Sub


Read more ...