Monday, October 31, 2016

Creating A Scrollable List In Worksheet

         
          If you have too large table in sheet, in such a table ,It is difficult to examine the table and to distinguish the results . 
          We can create a scrolling table using scrollbar control to overcome this problem. This is a great way to allow more data in a small space. When a user changes the scrollbar, the data accordingly changes.

- Before ,a scrollbar is added to the worksheet. A scrollbar to add to the sheet :

   Go to Developer Tab –> Insert –> Scroll Bar (Form Control).

   Click on Scroll Bar (Form Control) button and click anywhere on your worksheet.
   Right click on the Scroll Bar and click on ‘Format Control’. This will open a Format Control dialogue box.
   In Format Control dialogue box go to ‘Control’ tab, and make the following changes:
                 Current Value: 1
                 Minimum Value: 1
·                                 Maximum Value: (It will be created with codes in worksheet module)
                  Incremental Change: 1
                  Page Change: 10
                 Cell Link: $K$2

      -  Column headings are entered with formulas starting from cell B2 (=Data!A1, =Data!B1)
- The following formula is entered in the first cell (B3) and copied it to fill all the other cells: 
   =OFFSET(Data!A2;$K$2;0;1;1)
   OFFSET formula is dependent on cell K2.

- Following Formula is entered to cell K4 :
   =COUNTA(Data!$A:$A)-1

- Lastly following codes are entered to worksheet module in VBA Window to create dynamic  scrollbar (for scrollbar max value) :
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Set Target = Range("K6")
    ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Max = Target.Value
  End Sub

Thursday, October 27, 2016

Excel Vba Random Coloring The Duplicate Values

         
             Sample workbook contains two sheet and different two example macro.Dictionary Collection Object was used in each two macro - Set Evn = CreateObject("Scripting.Dictionary" -

In first example ago,the used range columns are sorted ascending according to cell A2 .Used codes :

"ActiveSheet.Cells(2, Cells(Rows.Count, lst_column).End(xlUp).Row).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom "

Later duplicate values background are filled by same color (according to the desired colors) . Color index number can be selected between 1 to 56. This numbers are assigned to array :

"Colors = Array(2, 4, 6, 7, 8, 12, 15, 16, 17, 19, 20, 22, 24, 27, 28, 33, 34, 35, 36, 37, 38, 39, 42, 43, 44, 45, 46, 48)
Clr = Colors(Int((UBound(Colors) - LBound(Colors) + 1) * Rnd))"

In second example , only duplicate values' background in Column A are filled by same color . Unique value's background color doesn't change (white color).


Monday, October 17, 2016

Magnifying The Selected Cell

Excel Zoom In Cell


For to magnify the cell's view the following solution can be applied :
- The font size of selected cell can be changed with macro.
  
- This macro that added to the worksheet module, looks at the currently selected cell and increases its font size.
 " Zoom_In = 1.75"

- Later the value of the cell is displayed on the shape.
   "sel.CopyPicture Appearance:=xlScreen, Format:=xlPicture      'Create zoom picture
    ActiveSheet.Pictures.Paste.Select
        With Selection
        .Name = "Zoom_Cells"
        With .ShapeRange
            .ScaleWidth Zoom_In, msoFalse, msoScaleFromTopLeft
            .ScaleHeight Zoom_In, msoFalse, msoScaleFromTopLeft
            With .Fill
                .ForeColor.SchemeColor = 44
                .Visible = msoTrue
                .Solid
                .Transparency = 0
            End With
        End With
    End With"

Wednesday, October 12, 2016

Excel Vba Column Hiding-Unhiding With Horizontal Form – 2

          In the previous template, we were finding the last used column according to the first row of columns. The used code that to find last used column : 

"lst_column = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column"

          Any value in the first row may not always be . Values may be in bottom rows.Therefore, we created the following code to find last used column in this template :

"lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column"


Tuesday, October 11, 2016

Excel Yatay Userform İle Sutun Yönetimi (Gizleme - Gösterme)

                Özellikle çok sutunlu dosyalarda çalışmak sutun sayısının fazlalığından dolayı güç olabilir . Çünkü bütün sutunlardaki bilgileri görebilmek için sayfayı devamlı sağa doğru kaydırmak zorundasınızdır.Bu sutunlardan bazılarını önemli görmez ve kapatmak isteyebilirsiniz.

Bu amaçla Excel 'de jstenmeyen sutunları gizleme-gizleneni gösterme amacı ile bir çalışma yaptık. 

Dosya açılışında sayfadaki kullanılan sutun sayısı kadar checkbox otomatik olarak oluşturulur ve yatay olarak yanyana sıralanır. Bu checkbox denetimleri sayesinde istediğiniz sutunu gizleyebilir yada görüntüleyebilirsiniz.

Bu formu kendi çalışmalarınıza da rahatlıkla ekleyebilirsiniz.

 İlgili video da bunun yolu da gösterilmektedir.


Monday, October 10, 2016

Excel Dynamically Adding Controls To Userform - Task Assignment To Controls

           In this tutorial , check boxes are automatically created based on the used column count when userform opens. The created check boxes are sorted horizontally at regular intervals :

"lst_column = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To 1                                   'Creating check boxes
For j = 1 To lst_column
    Set chkBox = Frm_Controls.Controls.Add("Forms.CheckBox.1", "CheckBox" & j)
    With chkBox
        .Top = i * 18
        .Left = (j * 70) - 65
        .BackColor = vbGreen
        .Font.Size = 11
        .Caption = Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value
    End With
    chkbx_width = (lst_column * 70) + 15
    'MsgBox chkbx_width
    If chkbx_width > Me.InsideWidth Then
    With Me
    .ScrollBars = fmScrollBarsHorizontal           'This will create a horizantal scrollbar
    .ScrollWidth = chkbx_width + 50
     End With
     Else
     Me.ScrollBars = fmScrollBarsNone
     End If
Next j
Next i
.."



Check boxes are rearranged (they are removed and recreated) depending on the selected worksheet from the drop-down list :

"For Each ctl In Frm_Controls.Controls                    'Removing old check boxes
        If TypeName(ctl) = "CheckBox" Then
            Frm_Controls.Controls.Remove ctl.Name
        End If
    Next ctl
.."

Column hiding-unhiding tasks are appointed to the check boxes :

"Public WithEvents fd As MSForms.CheckBox
Private Sub fd_Click()
Dim a As Integer
If fd.Value = True Then
a = Replace(fd.Name, "CheckBox", "")
Sheets(Frm_Controls.ComboBox1.Value).Cells(1, a).EntireColumn.Hidden = True
Else
a = Replace(fd.Name, "CheckBox", "")
Sheets(Frm_Controls.ComboBox1.Value).Cells(1, a).EntireColumn.Hidden = False
End If
End Sub
"
You can easily add own excel file this userform and can use it. For this :
- Close userform .
- Press Alt +F11 keys to open VBE (Visual Basic Editor) Window .
- Open your own file .
- Drag module,class and userform in this template to the part of your own files .
- Save changes and restart your file.

Friday, October 7, 2016

Excel Automated Invoice Template

Useful And Multifunctional An Invoice Example

Today's date is added automatically to date cell.Invoice number is automatically added (number is increased one) to number's cell after the prepared invoice is recorded.

When a product is selected from the drop-down lists in sheet's cells between A17-A33, Excel automatically fills the selected product's info into concerned cells (unit price ,tax).

When a customer is selected from the drop-down list in A10 cell, Excel automatically fills the customer info into concerned cells. This info :
– Customer’s name and address,
– Customer’s company name.
– Customer’s Id,

Product prices and the grand total is calculated by formulas and grand total is converted to text.

Invoices can be saved to the  selected worksheet from the userform. The wanted value can be searched in the recorded data by other UserForm.


Wednesday, October 5, 2016

Excel Vba Column Management (Hide & Unhide)

          In this template ,the userform opens automatically when workbook is opened and userform is displayed in the upper right corner of the screen.

          Sheets of workbook is added to drop-down list. Can be navigated between pages with this drop-down list.
         The used columns of sheets with column headers are listed on the listbox based on selected sheet from drop-down list. The selected columns from listbox are hidden. All items of listbox can be selected with checkbox at same time.


User can easily add own excel file this userform and can use it. For this :
- Close userform .
- Press Alt +F11 keys to open VBE (Visual Basic Editor) Window .
- Open your own file .
- Drag module and userform in this template to the part of your own files .
- Save changes and restart your file.

Sunday, October 2, 2016

Excel Animation Macro - Rotating Text

Excel Rotating Text

Shape (WordArt Text) on the worksheet turns 360 degree. Macro codes :

"...
For i = 1 To 36
    Selection.ShapeRange.IncrementRotation 10#
    DoEvents
  Next i
...
"