Wednesday, March 30, 2016

Excel Vba Merge And Center - 2

            In our previous study, only in "column A"  we merged the same data  in the adjacent rows.

 In this template, we merged the same data in all columns of the active sheet.


Tuesday, March 29, 2016

Excel Vba Merge And Center

Merge & Center The Adjacent Rows With Same Data

          If there are same data in the adjacent rows, the same cells are merged into one cell and centered.

In this way the data looks more neat .


Tuesday, March 15, 2016

Right-Click Context Menu On Userform.Textbox


            Menu in sample is created as a popup commandbar (with class module).The menu can be activated from the MouseUp event of the textbox:

Private WithEvents TControl As MSForms.TextBox
Private Sub TControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   If Button = 2 And Shift = 0 Then
     cmdBar.ShowPopup
   End If
End Sub

These operations can be performed with the right click menu :
- Copy
- Paste
- Clear

Private Sub CreateBar()
    Set cmdBar = Application.CommandBars.Add(, msoBarPopup, 0, 1)
    Set cmdCopyButton = cmdBar.Controls.Add(, 19)
    Set cmdPasteButton = cmdBar.Controls.Add(, 22)
    Set cmdClearButton = cmdBar.Controls.Add(, 47)
End Sub



Thursday, March 10, 2016

Page Up & Page Down Scrolling With Spin Buttons

           The spin buttons can be used to scroll the worksheet instead of scroll bar.Spin buttons on userform and spin buttons in the page can be used for this process.

Also page is scrolled based on value of textbox.


Tuesday, March 8, 2016

Calendar With Event Planner (To-Do List)

            Data can be recorded with userform to selected date . Data on userform are transferred in the Excel sheet as automatically.       



In this agenda example ,we used "Calendar Class"  instead of calendar control .
Calendar Control is an ActiveX Control and it gives error on some excel versions.  
The Calendar Class works in all version of Office VBA (Excel, Word, PowerPoint, Outlook)

The Calendar Class itself can not be added directly to the form, we need to put it to the source, as a new member of userform.


Monday, March 7, 2016

Excel Vba Create New Folder & Copy Sheets To It

           New folder (if folder does not exist) is created at the same place with workbook.

Name of the created folder is assigned based on the workbook's name. The workbook's sheets as new workbooks are copied into this folder.


All processes with a button click.

Friday, March 4, 2016

Run Macro When A Specific Cell Selected

Trigger Macro Or Vba Code When A Specific Cell İs Selected

          In this sample, the background color of the cells was painted different colors with  macros.

When the cells in specific range (between AR4 and AR28 cells) are selected,the macros are triggered:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$AR$4": Call col_one
Case "$AR$5": Call col_two
Case "$AR$6": Call col_three
Case "$AR$7": Call col_four
...
End Sub