June 26, 2010

Embedded Excel Web Control

Embedding a browser in a spreadsheet can be a fun way to send your friends and associates the browsing experiences you need to share them instead of creating a PowerPoint file. You can use this to aggregate your different blogs, file sharing, facebook, youtube and other accounts into a cohesive and sequential browsing.

In case your Excel is not yet updated version, I will share you the way to do this and still, you can use Excel 2007/2010 though the screen can be different a bit.

1) Open a blank worksheet. Right click any part of menu or icon and select cutomize and from the ensuing toolbar customization dialog- check 'Visual Basic'.

2) Now click the "Control Toolbox' icon (the one that looks like a hammer and a wrench) and then you have the option to embed various control using the last toolbox icon. Click it and scroll down to select "Microsoft Web Browser'. Then click the spreadsheet area to place it into. You may arrange and re size it, but leave 3 rows from the top.


3) Click the "Control Toolbox' icon and select the text box control. Place it on cell 'F1'. Right click it, and select view code. You will be taken to the coding window.

4) Paste the following lines of code:

Private Sub TextBox1_Change()
WebBrowser1.Navigate TextBox1.Text
End Sub

5) Now go back to the spreadsheet and select sheet2 tab and enter the following starting with cell 'A1':

Site Address
Google http://www.google.com/
Journey http://www.youtube.com/watch_popup?v=AO0LtxIpujM
PGT http://www.youtube.com/watch_popup?v=HwrcBVb_RJg
IndiArtist http://www.isound.com/kristen_rini

6) Now click the menu 'Insert', 'Name' and then select 'Define'. From the dialog, accept the name site and paste the following as the cell range: "=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1)". The range name "Site" will then be dynamically accommodating the list size of url as you edit, add, delete later. (The list must be a contiguous downward list).


7) Go back to sheet1 and select cell 'B2'. Click the menu ' Data' then select 'Validation'. On the dialog, select "List" on the dropdown box. Enter "=Site" on the box labeled 'Source'. Now 'B2' will have a validation that will list the sites from the dynamic range: "Site" (which is a list contained in sheet2).

8) Input the following formula (without quotation marks) in cell 'D2' : "=VLOOKUP(B2,Sheet2!A:B,2,FALSE)"

9) Right click the textbox control on cell 'F1' and select properties. On the list provided, select Linkedcell and input 'D2'. Also select the property 'Visible' and turn the value to "False"

10) Now click the icon named "Exit Design Mode" from the "Visual Basic Toolbar". Select cell 'B2' and change to see whether the browser control show the intended websites. Save the file after edit of the desired url listing on sheet2.

You may then prettify the workbook by changing the background color/picture, putting the necessary captions on cell 'B1' & 'D1', rename sheets, refine the validation rules, etc. You may also rename the browser control by going into design mode, but adjust the VB code accordingly.

Now whenever, you can say your point thru the web pages you view, then you can sell or share that point and let your friends experienced it being pulled from the web. Enjoy!

1 comment:

  1. Going back, I noticed I have missed giving the link for a ready made file of the aforementioned.

    So here it is:
    http://kvisit.com/S24F7

    ReplyDelete

Tell your concerns and alternate vista.