Saturday, August 27, 2016

Compare Two Columns In Different Worksheets & Add Found Results

         Vba Worksheet Function : Countif

           Two columns in different worksheets were compared in this template. Found different results as entire row were copied to second worksheet. 

Also new row was highlighted (background color).

Monday, August 22, 2016

Excel Finding The Sum Of Unique Elements In A Selected Range

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 :

Macro 2 In The Example2 Sheet :

Tuesday, August 16, 2016

Loading New Userform According To The Clicked Listbox Item

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

When any item of listbox clicked,another userform opens. The opened userform's text boxes are populated based on listbox clicked item value .Vba codes that supply the loading of the userform and the filling of the text boxes when double-clicking on the listbox :

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), "")
UserForm2.TextBox10 = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 0)
Unload UserForm1
End Sub

Wednesday, August 10, 2016

Excel Vba Context (Right-Click) Menu

Creating Popup Menu in All Versions of Excel 

With the context menu that created by the macro:

- The date can be entered to selected cell with date form.

- Text in cell can be converted easily to uppercase-lowercase-propercase

-  Between worksheets of workbook can be navigated with combobox.

- Texts in the cells can be ordered as  ascending /descending.

- Row height can be changed.

- Column width can be changed.

When the workbook is opened context menu is created with Workbook Activate Event :
Private Sub Workbook_Activate()
    Call AddToCellMenu
End Sub

When the workbook is closed, context menu is deleted with Workbook Deactivate Event :
Private Sub Workbook_Deactivate()
    Call DeleteFromCellMenu
End Sub

If  "Deactivate Event" (above code) is deleted before workbook is closed ,context menu can be used in all excel file.

Friday, August 5, 2016

Excel Vba Find And Delete

             Ago value that you want to delete is found with macro.

With the opened msgbox ,found value's address is reported ,and you are asked whether you want to delete.
If you click "Yes" button on the msgbox, the row that contain value is deleted as whole.

Xlpart was selected  as the search method of "Find Method":

Set bul = Range(Cells(3, 1), Cells(Cells(Rows.Count, 1)._
End(xlUp).Row, LastColumn)).Find(WhatToFind, LookAt:=xlPart)

The macro searchs the value since the third row, because first row is empty and second row is header row.