Delayed Posting of eMail

At times you want compose a mail , but you want to send the email only after certain date/time, you could this settings in the outlook using the following options

 

 

Grouping of Rows and Columns

To group columns or Rows

 

SHIFT + ALT + RIGHT ARROW KEY

 

 

 

To Ungroup

SHIFT + ALT + LEFTARROW KEY

 

 

Hide Ribbon

During the presentations and work in MS Office, you want the Top Ribbon to get hidden

 

 

Press Ctrl + F1 to get auto hidden

Here is the result

 

 

---

DISCLAIMER: The information contained and transmitted by this electronic mail (email) is proprietary to i-Vista Digital Solutions Pvt.Ltd (i-Vista) and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this email may not have been sent with the formal approval of i-Vista. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, disclosure, transmission, printing, copying, or dissemination of this information either whole or partial, in any way, or in any manner is strictly prohibited. If you have received this communication in error, please delete this email immediately & notify i-Vista at postmaster@ivistasolutions.com

Read the content without clutter

If you are using Mozilla browser, you have an option to read the content of the page, without any advertisement.

 

Try the following

 

Locate the Reading button at the right side of the address bar

 

 

You can use the same button to clear the reading view.

 

Try it on

Find Number of Characters in a Cell

There would be time where you want to find the number of occurrences of a text, you can use the following formula

 

 

Delete External links from an Excel file

There would be time where your file size is heavy or it is taking more time to load , because of some unkonow connections in the file from external source, try this to eliminate the links, the file shall work faster

 

Data >> Edit Links >> Break Link

Formulas >> Name Manager >> Delete

 

Display Hidden Data in chart

General if the cells are hidden, chart will not pull up that data

 

Use the following to make it active

1.      Click the chart in which you want to display hidden data.

2.      This displays the Chart Tools, adding the Design, Layout, and Format tabs.

3.      On the Design tab, in the Data group, click Select Data.

1.      Click Hidden and Empty Cells.

 

 

Enable/Disable Insert related functions

This macro can be used to enable / dis-able insert and other relevant controls. TRUE is for enable, false is for diable

 

    Application.EnableEvents = True

    With Application

        .Caption = ""

       

        'Cut

        .CommandBars("Worksheet Menu Bar").Controls("Cut").Controls("Rows").Enabled = True

        .CommandBars("Row").Controls("Cut").Enabled = True

       

        .CommandBars("Column").Controls("Cut").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Cut").Controls("Columns").Enabled = True

       

        .CommandBars("Standard").Controls.Item("Cut").Enabled = True

        .CommandBars("Edit").Controls.Item("Cut").Enabled = True

        .CommandBars("Cell").Controls.Item("Cut").Enabled = True

 

        'Insert

        .CommandBars("Row").Controls("Insert").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Rows").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert...").Controls("Rows").Enabled = True

       

        .CommandBars("Column").Controls("Insert").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert...").Controls("Columns").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Insert").Controls("Columns").Enabled = True

 

        .CommandBars("Cell").Controls.Item("Insert...").Enabled = True

       

        'Delete

        .CommandBars("Row").Controls("Delete").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete").Controls("Rows").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete...").Controls("Rows").Enabled = True

 

        .CommandBars("Column").Controls("Delete").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete").Controls("Columns").Enabled = True

        .CommandBars("Worksheet Menu Bar").Controls("Delete...").Controls("Columns").Enabled = True

        .CommandBars("Cell").Controls.Item("Delete...").Enabled = True

        

        'Other Properties Set

        .WindowState = xlMaximized

        .DisplayFormulaBar = True

        .Calculation = xlAutomatic

        .CellDragAndDrop = True

        .EnableEvents = True

        .MaxChange = 0.001

    End With

 

Missing Native Excel functions

At times because of some macros some native excel functions would get disabled, if you would want to reset that, you need to do the following steps

 

Locate the users setting of excel, generally here is the path for my system

C:\Users\venu\AppData\Roaming\Microsoft\Excel

 

Delete all the contents of the folder

 

When excel is opened it will restore the settings.

 

IN my system Insert function button (ALT + I + R) was not working, and now its taken care.

 

 

Alt + I + R (Or) Alt + I + C insert function not working

You can apply the following macro to activate the nativity function of insert rows columns

 

Sub activateInsertfunctions()

Call Allow_InsertColumn(True)

Call Allow_InsertRow(True)

End Sub

 

Function Allow_InsertRow(Allow As Boolean)

Dim ctl As CommandBarControl

For Each ctl In Application.CommandBars.FindControls(ID:=296)

ctl.Enabled = Allow

Next ctl

End Function

 

Function Allow_InsertColumn(Allow As Boolean) 'Allow user to or prevent user from inserting columns

Dim ctl As CommandBarControl

For Each ctl In Application.CommandBars.FindControls(ID:=297)

ctl.Enabled = Allow

Next ctl

End Function

 

Credit: Jos Dijkstra

 

Rename filename in XL VBA

Sub ChangeFilename()

Dim s1 As Worksheet

Dim strtpoint, end_point, i As Double

 

Set s1 = Worksheets("Data")

strtpoint = s1.Cells(2, 3)

end_point = s1.Cells(3, 3)

Dim FILEPATH As String

FILEPATH = s1.Cells(1, 3)

 

Dim oldfilename, newfilename As String

Dim filenum As String

 

For i = strtpoint To end_point

oldfilename = s1.Cells(i, 2)

newfilename = s1.Cells(i, 3)

 

 

    Name FILEPATH & oldfilename As FILEPATH & newfilename

  

Next i

End Sub

Refer Data from Other Sheets

One of the employee came to me with a file which has 96 sheets and basically it was all payroll data, he wanted a summary sheet with all the employee names and the month over month tax deductions.

 

I didn’t want to write a macro…..after a small thought here is the solutions

 

a)      Build Index of Sheets

=IFERROR(INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1),1),"")

 

b)      Refer the values from different sheets

=indirect( " ' "&Sheet Name&" '!"&Cell Name)

 

In one go all the data employee wise month wise is right in front of me…..