Excel Vba :Finding The Sum Of Unique Elements In A Range

August 23, 2016 Leave a comment

In this study, we have worksheet that it has 9 columns .

We created  a VBA code to sum the values of unique elements in column “B”. Ago we listed items in column “B” as unique elements  into column “K” . Later  we entered the total of these items into column “L”.

For this purpose we created different two macros.

Macro 1 In The Example1 Sheet :

sum unique items in column

Macro 2 In The Example2 Sheet :

excel subtotal macro

Download Template

Opening New Userform According To The Clicked Listbox İtem

August 17, 2016 Leave a comment

Populating Text Boxes Based On The Listbox Clicked Item

We used a template we did earlier (Filtering On Listbox And Copying Filtered Data) in this exercise.

When item of listbox clicked,another userform opens. The opened userform’s text boxes are populated based on listbox clicked item value  :

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Load UserForm2

UserForm2.TextBox1 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 0)

UserForm2.TextBox2 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 1)

UserForm2.TextBox3 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 2)

UserForm2.TextBox4 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 3)

UserForm2.TextBox5 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 4)

UserForm2.TextBox6 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 5)

UserForm2.TextBox7 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 6)

UserForm2.TextBox8 = VBA.Format(UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 7), “#,##.00”)

UserForm2.TextBox9 = VBA.Format(UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 8), “dd.mm.yyyy”)

UserForm2.TextBox10 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 0)

Unload UserForm1

UserForm2.Show

End Sub

….

Populating Text Boxes Based On The Listbox Clicked Item

Download Template and Use It

Follow

Get every new post delivered to your Inbox.

Join 59 other followers