re-Check the password entered

If you want to see what is the password that was entered in browser, here are simple steps

 

a)      Right click on the password box and select Inspect element

b)     You will see a text like this

 

 

c)      Change type=”password” to type=”text”

 

 

Magic , you have the password typed on the screen

 

Get Sheet Name or File Name or File Path

If you want to get some data use the following

 

=CELL("filename")

 

Then you can use ‘find’ function to trim and get what ever informaiton you want to extract

 

Trace Circular Reference

In case if you are lost with the values and there is a circular reference, you can check the same using this in the formula tab

 

 

Menu Bar Control

You can enable / disable items in the Menu Bar back in VBA. The following is the Control Number to enable / Disable

 

CommandBar            Control                         ID

   --------------------------------------------------------------------

   Worksheet Menu Bar    &File                        30002

   Worksheet Menu Bar    &Edit                        30003

   Worksheet Menu Bar    &View                        30004

   Worksheet Menu Bar    &Insert                      30005

   Worksheet Menu Bar    F&ormat                      30006

   Worksheet Menu Bar    &Tools                       30007

   Worksheet Menu Bar    &Data                        30011

   Worksheet Menu Bar    &Window                      30009

   Worksheet Menu Bar    &Help                        30010

 

   Chart Menu Bar        &File                        30002

   Chart Menu Bar        &Edit                        30003

   Chart Menu Bar        &View                        30004

   Chart Menu Bar        &Insert                      30005

   Chart Menu Bar        F&ormat                      30006

   Chart Menu Bar        &Tools                       30007

   Chart Menu Bar        &Chart                       30022

   Chart Menu Bar        &Window                      30009

   Chart Menu Bar        &Help                        30010

 

 

Check Formulas

If you want to check which are the cells with formulas and which are not, you can use this simple quick highlighter to show you, you can find this in the Home TAB, right corner bottom

 

Find the nth Occurrence of a Character in a cell

If you want to find

 

A1 = This World is Very Big

 

To find the place of 3^rd I Use the following formula :  FIND("i",A1,FIND("i",A1)+3)

 

Here 3 represent nth occurrence

Convert XLAM to XLSM

If you have any XL Add in file and you want to convert back into a Macro Enabled File, please do the following

 

Go to File properties >> change the IsAddin to  FALSE

 

 

Enjoy editing Macros

Connect Tally from Excel

If you are trying to connect Tally from Excel and you do the following process.

 

First try connecting a new Query

 

 

Select Tally ODBC

 

 

 

 

If you don’t find the same then ODBC is not enabled in the Tally. Kindly enable by doing the following

 

1.      On the Gateway of Tally, go to configuration F12

 

 

2.      Select Advance configuration

 

 

 

3.      Enable the ODBC

 

 

 

 

 

 

 

 

 

Generate Random Names

In Excel you can always generate Random Numbers, but for some reason you need Random NAMES quickly, use this link here

http://listofrandomnames.com/

Reverse the Text in Excel

Use this code

 

Function Reversestr(str As String) As String

    Reversestr = StrReverse(Trim(str))

End Function

 

Forgot Password of Tally Company

In Case if you have the tally data, but do not have the login details, you could do the following

 

a)      Look at the name of the company

b)      See the data till when the entries are there

c)      Create a new company in tally with the same name

d)      From the original tally data folder, copy all the files except “Company.900”

e)      You will be able to now see the company.

 

Note: You will not be able to drill down to the transactions, however PnL and BS can be seen

 

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