Excel : Browse Internet in excel worksheet

You can browse or surf internet in excel worksheet......:)

without opening or clicking internet explorer you can surf web pages in excel worksheet.
you can chat on facebook access emails with in excel worksheet.

you can do work in one worksheet & simultaneously browse the web on another worksheet
Steps.
To make the following code work, you'll need to include the "Microsoft Internet Controls" library in your VBA references first.Go to your Visual Basic Editor, Menu Tools -> References select  "Microsoft Internet Controls

1. click on developer tab
2. Go to Control->Insert->ActiveX-Control->Click on More Control (Hammer Icon)

      3. you will get More Control window

      4. Select Microsoft Web Browser & Click ok 

    

       5.Go to Control->Insert->ActiveX-Control->Command Button

       6.Right Click on command button

       7.Select View code

       8.copy below code

       9. Exit design mode

Private Sub CommandButton1_Click()

'change you web site url here

WebBrowser1.Navigate "http://www.google.co.in/"

' Wait till the Browser is loaded

Do

DoEvents

Loop Until WebBrowser1.ReadyState = READYSTATE_COMPLETE

End Sub

Excel : Format Number Lakh Separator

Excel allows only thousand's separator not "lakh" separator
There is difference between Excel Numbering format between Indian & Western system
Both systems agree till 10,000. After that the 'problems' start.

10^5 = Hundred Thousand = 1 Lakh
10^6 = Million = 10 Lakh
10^7 = 10 Million = 100 Lakh = 1 Crore
10^8 = 100 million = 10 Crore
10^9 = 1 Billion = 100 Crore = 1 Arrab
10^10 = 10 Billion = 10 Arrab
10^11 = 100 Billion = 100 Arrab = 1 Kharrab
10^12 = 1 Trillion = 10 Kharrab
In India numerical value for lakh is 100,000 not 100,000 (one hundred thousands)


Here's a useful Excel tip for users in India and other places that use number formats like 100,00,00,000 instead of 1,000,000,000:

With 2 decimals:

[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00

Without decimals:

[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0

For Lakhs and crores (+ve)

[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00

For Lakhs and crores (-ve)

[<-9999999](##\,##\,##\,##0.00);[<-99999](##\,##\,##0.00);##,##0.00

Or

For numbers upto 100 Crores or 1 Arab..

[>9999999]##\,##\,##\,###;[>99999]#\,##\,###;###,###


There is an easier way to change the global settings from Settings > Control Panel > Regional Settings > Numbers, but that will affect all your Excel sheets and the change will be visible only on your system and not on your files which you send to others.


with Vb Code (By : Graham aka parry )


Private Sub Worksheet_Change(ByVal Target As Range)
     '//Written by parry
    Dim c
    If Target.Cells.Count = 1 Then
        Select Case Target.Value
        Case Is >= 1000000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
        Case Is >= 10000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""000.00"
        Case Is >= 100000
            Target.Cells.NumberFormat = "##"",""00"",""000.00"
        Case Else
            Target.Cells.NumberFormat = "##,###.00"
        End Select
    Else
        For Each c In Target
            Select Case c.Value
            Case Is >= 1000000000
                c.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
            Case Is >= 10000000
                c.NumberFormat = "##"",""00"",""00"",""000.00"
            Case Is >= 100000
                c.NumberFormat = "##"",""00"",""000.00"
            Case Else
                c.NumberFormat = "##,###.00"
            End Select
        Next c
    End If
End Sub

Excel : Custom Cell Formatting

Right Click on Cell-->Select Format Cells-->Select Custom


"#" is used as a placeholder for permitted digits/Digit placeholder. This code does not display extra zeros.
"?" indicates either a digit/Digit placeholder. This code leaves a space for insignificant zeros but does not display them
, (comma) Thousands separator. A comma followed by a placeholder scales the number by a thousand.

You can design your own custom number formats

Excel : Display Credit Card Number

To Display Credit Card Number in Cell you have to format the cell
if last digit of your credit card number is Zero then you can format cell as 0000 0000 0000 0000
if last digit is not zero than assuming your credit card number is in cell A2 Try below formula
'=LEFT(A2,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&RIGHT(A2,4)

Microsoft Excel retains 15 significant digits. To display all 15 digits, you must use a number format (custom or built-in) other than General. The General number format displays up to 11 numeric characters, with the decimal point counting as a numeric character. Therefore, if the number contains a decimal point, Excel can display up to 10 significant digits, but if the number does not contain a decimal point, Excel can display up to 11 significant digits Adding more numbers to the left of the decimal point causes the number to appear in exponential notation.

Create Expiry for Excel Workbook

Some times you have to set expiry date for your excel workbook. which will restrict use of workbook after certain period of time.  

  Steps:

1. Press "Alt+F11" to launch the Visual Basic Editor from Excel
2. Right-click "ThisWorkbook" in the "Project Explorer" window. Select "View Code" from the list of available options
3. copy below code

Private Sub Workbook_Open()

'If Sheets("sheet1").Range("a1").Value < Date Then ' you can set value on worksheet

If doe < Date Then 'You can define date as doe

MsgBox "This tool has expired, please contact Admin.", vbCritical, "Expired"

ActiveWorkbook.Close False

End If

End Sub

MS PowerPoint- Play Video with Timer

This requirement came from my one of my MBA college friend.
show the people that they have never seen\ heard before during presentation
You can play video in Excel and PowerPoint as well.
Many option's are available to get it done. If you use youtube video URL then you require internet connectivity during the presentation. If you use windows media player option you have to save the video on particular drive & carry the video during the presentation.

Best way i found is to convert your video in swf format & insert it in PowerPoint or Excel.
Software Link : http://www.dvdvideosoft.com/products/
download presentation : http://www.mediafire.com/file/n43cez8fic2itrc/Powerpoint-Play Video with Timer.ppt

Steps :

1. Go to developer tab
2. On the control group click on insert
3. Then click on more control (icon like hammer)
4. You will get More control box ; Select shockwave flash object & click ok
5. You will get cross sign (+) drag it on Powerpoint slide & select the area for video
6. Right click on square
7. Then select properties
8. Enter the full path to the Shockwave file (SWF) in the Movie property text-box. (e.g "C:\Users\MAHESH\Desktop\Download\abc.swf")
9. Set the EmbedMovie property to True
10. Run the slide show

For Countdown timer :

Amazing Excel VBA Creativity

I found below excel workbooks during learning excel VBA
you can download workbook from below links

• Painting in Excel 

         http://www.mediafire.com/?4aythpdtaj7irkm

         http://www.mediafire.com/file/hoe2pxsx1hdd0q6/Excel VBA_Painting In Excel.xls

       

• Excel VBA MP3 Player

         http://www.mediafire.com/file/q9hjmticqx0cyc1/Excel VBA-MP3-Player.xls

• Find Internet Browsing History in Excel sheet

        http://www.mediafire.com/file/bz7q8jf47b57za9/Excel VBA -Find Internet History.xlsb

Greetings in Excel

I have received amazing  response for New year greeting wishes in excel from all who seen them around the world

• valentine's day greeting cards in Excel

          http://www.mediafire.com/?7f4lhrwya97xzlf

• happy new year greetings in Excel

          http://www.mediafire.com/?9filaap09h4gqgq

          http://www.mediafire.com/?a5k4acw114g90s6

Excel : Find duplicate entry while entering data in column

For any column in worksheet .This will give you alert while entering duplicate entry with msg box & column header name. highlight the duplicate entry

Right click on sheet tab-> view code->copy below code


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Application.WorksheetFunction.CountIf(Range(Cells(2, Target.Column), Cells(Target.Row, Target.Column)), Target) > 1 Then
     Alert = MsgBox(Cells(1, Target.Column) & " Already Exist - Click Yes do delete", vbYesNo)
     If Alert = vbYes Then
     Application.Undo
     Else
     Target.Interior.ColorIndex = 6
     Exit Sub
     End If
End If
End Sub

Excel Find and Highlight Maximum Value from Each Column

this will highlight maximum value from each column in worksheet

Sub Mtest()
Dim c As Range, rng As Range
Dim lCol As Long, LR As Long
With ActiveSheet
    For lCol = 1 To .UsedRange.Columns.Count
      LR = .Cells(.Rows.Count, lCol).End(xlUp).Row
      Set rng = .Range(.Cells(1, lCol), .Cells(LR, lCol))
      For Each c In rng
        If c.Value = Application.WorksheetFunction.Max(rng) Then
          c.Interior.ColorIndex = 3
        Else
          c.Interior.ColorIndex = xlNone
        End If
      Next
    Next lCol
  End With
End Sub

Excel : Open list of website & download data

this requirement came from my friend. He used to open list of websites & download the NAV.
you can download data from web page through web query


Sub Mtest()
Dim page As New InternetExplorer
For i = 2 To Sheets("websitelist").Range("A" & Rows.Count).End(xlUp).Row
URL = Sheets("websitelist").Cells(i, 1)
page.Navigate URL '
page.Visible = True
Do
Loop Until page.ReadyState = READYSTATE_COMPLETE
page.Refresh
'=============================
'call your web query here
'=============================
page.Quit
'if you dont use below line you will get error like
'The remote server machine does not exist or is unavailable
'This quits page and releases the object.
Set page = Nothing
Next i
End Sub

Excel : Login to Gmail /Yahoomail with Vba

Press Alt + F11
Go to tools->References
select below references & click ok

• Microsoft Internet Controls
• Microsoft HTML Object Liabrary

Gmail

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_GMAIL()

Dim oHTML_Element As IHTMLElement
Dim sURL As String
On Error GoTo Err_Clear
sURL = "https://www.gmail.com"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.document
HTMLDoc.all.Email.Value = "abc@gmail.com" 'Enter your gmail id here
HTMLDoc.all.passwd.Value = "abc@1234" 'Enter your password here
For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
'Debug.Assert Err = 0
Err.Clear
Resume Next
End If
End Sub

'Open login page of Gmail and go to view source you will get below javascript
'<label>
  '<strong class="email-label">Username</strong>
  '<input type="text" spellcheck="false" name="Email" id="Email" value="">
'</label>
'<label>
  '<strong class="passwd-label">Password</strong>
  '<input type="password" name="Passwd" id="Passwd">
'</label>

Yahoomail


Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_YAHOO()

Dim oHTML_Element As IHTMLElement
Dim sURL As String
On Error GoTo Err_Clear
sURL = "https://login.yahoo.com"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True
Do
Loop Until oBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = oBrowser.document
HTMLDoc.all.login.Value = "mahesh" 'Enter your yahoo id here
HTMLDoc.all.passwd.Value = "abc" 'Enter your password here
For Each oHTML_Element In HTMLDoc.getElementsByTagName("button")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub
'<label for='username'>Yahoo! ID</label>
'<input name='login' id='username' maxlength='96' tabindex='1' value=''>
'<label for='passwd'>Password</label>
'<input name='passwd' id='passwd' type='password' maxlength='64' tabindex='2'>

Excel VBA Learning - (MOS) 2010 Study Guide for Microsoft® Excel

• Excel 2007 VBA Macro Programming 

• Microsoft Office Specialist (MOS) 2010 Study Guide for Microsoft® Excel 

• Vba_for_Excel_Made_Simple_(Made_Simple_Programming)