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

         The listbox column widths is automatically adjusted according to the widths of the sheet's column with Vba codes.

excel userform address book

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 Dependent Drop Down Lists


In this example, we will create a userform that contains dependent combo boxes and a textbox. We will use  this userform to enter the data  into cells as fastly.

The UserForm will be opened automatically when any cell in "Column A" is selected.
The important point in this template ; by creating a dependency between combo boxes,  selectively choices can be limited. 




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 ...