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.



Read more ...

Excel VBA Merge And Center

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


Our macro to merge and center cells :
Sub merge_center()
Dim i, a As Long, one, two As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To Cells(Rows.Count, "A").End(3).Row
If Cells(i, "A") = Cells(i + 1, "A") Then
one = Cells(i, "A").Address
For a = i + 1 To Cells(Rows.Count, "A").End(3).Row
If Cells(a, "A") <> Cells(a + 1, "A") Then
two = Cells(a, "A").Address
Exit For
End If
Next a
Range(one & ":" & two).merge ' Same cells are merged
Range(one & ":" & two).Interior.ColorIndex = 34 ' Background is painted
Else
End If
Range("A2:A" & i).HorizontalAlignment = xlCenter
Range("A2:A" & i).VerticalAlignment = xlCenter
Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub


Read more ...

Excel Right-Click Context Menu On Userform Textbox

Right Click Menu On Userform Textbox


            Popup menu on textbox control is created using 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
     Run "RightClickMenuTextBox"
   End If
End Sub

These operations can be performed with the right click menu :
Copy
 Paste
☑ Cut
☑ Clear
☑ Telephone




Read more ...

Excel VBA Create New Folder & Copy Sheets To It

Excel VBA Create New Folder & Copy Sheets Of Workbook To It


       📁 A new folder with the same name as the workbook (if folder does not exist) is created at the same place with workbook.

The workbook's sheets as new workbooks are copied into this folder.



🔧 Our codes that we used in macro:
Sub createfolder()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&

Application.ScreenUpdating = False
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

With Application
.ScreenUpdating = False
.DisplayAlerts = False

On Error Resume Next
MkDir MyFilePath
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xlsx"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheets(1).Activate
Application.ScreenUpdating = True

MsgBox "The Process Was Carried Out Successfully", vbInformation
End Sub


All processes with a button click.


Read more ...

Excel Run Macro When A Specific Cell Selected

Trigger Macro Or VBA Code When A Specific Cell Is Selected


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

         When cells of specific range in "Example" worksheet  (between AR4 and AR28 cells) are selected,the macros are triggered .For this purpose, we have added the following codes to the page's Worksheet_SelectionChange method :
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
Case "$AR$8": Call col_five
Case "$AR$9": Call col_six
Case "$AR$10": Call col_seven
Case "$AR$11": Call col_eight
Case "$AR$12": Call col_nine
Case "$AR$13": Call col_ten
Case "$AR$14": Call col_eleven
Case "$AR$15": Call col_twelve
Case "$AR$16": Call col_thirteen
Case "$AR$17": Call col_fourteen
Case "$AR$18": Call col_fifteen
Case "$AR$19": Call col_sixteen
Case "$AR$20": Call col_seventeen
Case "$AR$21": Call col_eighteen
Case "$AR$22": Call col_nineteen
Case "$AR$23": Call col_twenty
Case "$AR$24": Call col_twentyone
Case "$AR$25": Call col_twentytwo
Case "$AR$26": Call col_twentythree
Case "$AR$27": Call col_twentyfour
Case "$AR$28": Call col_twentyfive
End Select
End Sub

The Call statement was used to call a Sub procedure.  

excel run macro when selected cell

We added sub procedures (macros) to the module.

excel vba procedures





Read more ...