Sat 25 Nov 2017

VBA - Create an Index (Summary, Table of Contents) Sheet Macro

By |Saturday, November 25th, 2017|Categories: VBA|Tags: , , , , , |0 Comments

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

(more…)

Sat 09 Jul 2016

Tips & Tricks 140 - Multiple Hyperlinks within Excel Text Box

By |Saturday, July 09th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You created a Text Box and you put many words inside that say Yahoo, IBM, Microsoft etc...Now, you have given respective hyperlinks to them. But if you try to click on any hyperlink inside the text box, it will always open only one hyperlink.

It is possible to circumvent this behavior by work-around. You can execute following steps

1. Create a Text Box where you want to put all Hyperlinks.
2. Create many new Text Boxes.
3. Put the words in Text Boxes which you created in step 2 and give them Hyperlinks.
4. Drag the Text Boxes of Step 3 into Step 1 Text Box.
5. Align them properly and format them to remove borders.
6. Select all Text Boxes along with Step 1 Text Box > Page Layout > Group
7. Now, all individual Hyperlinks can be clicked separately.

 

Sat 14 Nov 2015

Tips & Tricks 118 - Stop Auto Creation to Hyperlinks

By |Saturday, November 14th, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

We all know that if you enter a web address like www.microsoft.com, http://www.microsoft.com or even e mail address like admin@eforexcel.com, Excel converts these to Hyperlinks which can be clicked. Sometimes, we don't want this behaviour and we want to stop this -

Trick 1 - After entering a web address / e mail address, press CTRL+Z. The Hyperlink will get removed.

Trick 2 - Enter the web address / e mail address with a leading apostrophe

Trick 3 - File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type tab and uncheck "Internet and network paths with hyperlinks"

1

Sat 03 Oct 2015

Tips & Tricks 111 - Extract URLs from Hyperlinks

By |Saturday, October 03rd, 2015|Categories: Tips and Tricks, VBA|Tags: , , , , , |0 Comments

In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster

Now, how to extract this. If there are very few entries like this, you can simply right click the cell > Edit Hyperlink > Copy the URL

But, you have a bunch of entries like this and you need to extract the URLs. For this, the only method is to use a simple piece of code. Don't be frighten even if you have never touched VBA before. Simply follow the following steps -

1. ALT+F11 or Right Click on tab name > View Code to open VBA window.
2. Now locate your workbook on the left side in Project Explorer window though it should be visible by default.
3. Right click on your Workbook Name in Project Explorer window > Insert Module
4. Double click on the module inserted and copy and paste following code

5. Now, in your sheet you can use GetURL as a function like a regular function. If your Hyperlink is in A1, you can write =GetURL(A1) to extract

Credit - http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/