CLOSE USERFORM ON ESCAPE KEY

Put a commandbutton on your userform and write the below code on command button Click event.


Private Sub CommandButton1_Click()
Unload Me
End Sub

Then set cancel to true in its property. See image below(Click on the image to enlarge it)

Now when you run userform and press escape key on your keyboard, userfrom will be closed.

Is the post useful? Please post your comment and follow my blog:)

PASTE NON BLANK CELLS ONLY USING EXCEL

Array Formula in B1:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($B$1:B1)))))      

Press Ctrl+Shift+Enter and drag it down to B10.

Tip:   This is also useful when you create a data validation list from a range with blank cells in it. In that case you can create a new range with non-blank cells using above formula and then use this new range for data validation.

MERGE TEXT BY VBA WITHOUT LOOSING DATA

Select the cells need to be merged and run the below code in the module.


Sub Merge_without_loosing_data()
Dim OutputText As String
Dim cell As Range
Const delim = " "

On Error Resume Next
For Each cell In Selection
OutputText = OutputText & cell.Value & delim
Next cell

With Selection
     .Clear
     .Cells(1).Value = OutputText
     .Merge
     .HorizontalAlignment = xlCenter
     .VerticalAlignment = xlCenter
     .WrapText = True
End With
End Sub

SPLIT TEXT BY VBA

Select the cell that you want to split, copy the below code and run it in module.


Sub split_text()
Dim splitval As Variant
Dim totalval As Long

splitval = Split(ActiveCell.Value, Chr(10))
totalval = UBound(splitval)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row _
, ActiveCell.Column + 1 + totalval)).Value = splitval
End Sub

COUNT "6" WORKING DAYS IN A WEEK

Write 07/08/2012 in A1 and 07/14/2012 in B1

If you use NETWORKDAYS function to count the working days between these two dates, it will give you "5". But what if your office works six days in a week. In that case use the following formula to count six working days in a week.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<7))

SORT DATA ON BACKGROUND COLOUR BY VBA

Copy below code and run it in the module


Sub sort_data_on_backgroud_color()
Sheets("Sheet1").Select
Range("B1") = "ColorIndex"
For i = 2 To ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B" & i).Value = ActiveSheet.Range("A" & i).Interior.ColorIndex
Next
ActiveSheet.Range("A1:B" & Range("A1").End(xlDown).Row).Sort key1:=ActiveSheet.Range("B:B"), order1:=xlAscending, Header:=xlYes
Columns("B:B").ClearContents
End Sub

QUICK TIP: CONVERT THE NUMBER INTO PERCENTILE

If we want to add "%" in each cell in a column, we generally select home tab, then go to % and it converts the number in the following format.

1000%
3300%
3700%
1200%
1100%
4200%
2400%
2400%

So, this is not a right way. The quick way to do this is


Write 100 in a cell & copy itSelect the data which you want to format (In this case A1:A8)
Right click and go to Paste Special
Select divide
Press OK
Go to Home tab and select Percent Style
Your data will change into the format shown in column B in the above image