Pages

Friday, April 13, 2012

Adding Marquee on Excel Worksheet

Adding Marquee in Excel worksheet


Step 1


Step 2


Example 1 Continuous scrolling



Add below code module1 or any new module

Sub run_marquee()
' EXAMPLE 1
Dim mbody As String
' add the text of amrquee and creat a html webpage
mbody = "<p style=""font-size:18px;""><marquee behavior=""scroll"" direction=""left"">www.excelvbamacros.com</marquee>"
Open ThisWorkbook.Path & "\marquee_sample1.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample1.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 30
Sheets(1).WebBrowser1.Width = 800
End Sub

Add below code to worksheet module

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 1


Example 2 Bouncing Marquee

Add below code to module1 or any new module

Sub run_marquee()
' EXAMPLE 2
Dim mbody As String
' add the text of amrquee and creat a html webpage
mbody = "<p style=""font-size:18px;""><marquee behavior=""alternate"" direction=""left"">www.excelvbamacros.com</marquee>"
Open ThisWorkbook.Path & "\marquee_sample2.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample2.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 30
Sheets(1).WebBrowser1.Width = 800
End Sub


Add below code to worksheet module

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 2


Example 3 Change Scrolling Speed

Add below code to module1 or any new module

Sub run_marquee()
' EXAMPLE 2
Dim mbody As String
' add the text of amrquee and creat a html webpage
mbody = "<p style=""font-size:18px;""><marquee behavior=""scroll"" direction=""left"" scrollamount=""10""> www.excelvbamacros.com</marquee>"
Open ThisWorkbook.Path & "\marquee_sample3.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample3.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 30
Sheets(1).WebBrowser1.Width = 800
End Sub

add below code to worksheet


Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 3


Example 4 Image Marquee

Add below code to module1 or any new module

Sub run_marquee()
' EXAMPLE 4
Dim mbody As String
' add image source in img src weblink of the image
mbody = "<marquee behavior=""scroll"" direction=""left""><img src=""http://thgsoft.ch/Excel.gif""/></marquee>"
Open ThisWorkbook.Path & "\marquee_sample4.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample4.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 60
Sheets(1).WebBrowser1.Width = 800
End Sub

Add below code to worksheet


Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 4


Example 5 Stop over mouse

add below code to module1 or any new module

Sub run_marquee()
' EXAMPLE 4
Dim mbody As String
' add image source in img src weblink of the image
mbody = "<p style=""font-size:18px;""><marquee behavior=""alternate"" direction=""left"" onmouseover=""this.stop()"" onmouseout=""this.start()"" scrollamount=""3"" scrolldelay=""50""> www.excelvbamacros.com</marquee>"
Open ThisWorkbook.Path & "\marquee_sample5.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample5.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 30
Sheets(1).WebBrowser1.Width = 800
End Sub

add below code to worksheet


Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 5


Example 6 Stop on Mouse Over and Add hyperlink



Add below code to module1 or any new module

Sub run_marquee()
' EXAMPLE 4
Dim mbody As String
' add image source in img src weblink of the image
mbody = "<p style=""font-size:18px;""><marquee behavior=""scroll"" align=""middle"" direction=""left"" scrollamount=""4"" onmouseover=""this.stop()"" onmouseout=""this.start()""><a href=""http://www.excelvbamacros.com/2012/01/my-menu-functions-and-help.html"" target=""_blank"">Download Free Excel Addin</a></marquee>"
Open ThisWorkbook.Path & "\marquee_sample6.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample6.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 30
Sheets(1).WebBrowser1.Width = 800
End Sub


Add below code to workhseet

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 6


Example 7 Show the cell value, hide the borders of box , chage font size, type, color etc

Add below code to module1 or any new module

Sub run_marquee()
' EXAMPLE 7
Dim mbody As String
' For html color lsiting visit'http://www.w3schools.com/html/html_colornames.asp
' bgcolor to set background color
' p style=""font-size:17px;color: to set font size znd color

' add image source in img src weblink of the image
mbody = "<html><head><script language=""javascript"">function noScroll(){document.body.scroll=""no"";}" & _
"</script><STYLE>BODY { border-style:none;}</STYLE></head><body onload=javascript:noScroll(); topmargin=""0"" leftmargin=""0"">" & _
"<body bgcolor=""#FFFAFA""><p style=""font-size:17px;color:#FF0000;font-family: courier new"" ><marquee behavior=""scroll"" direction=""left"">" & _
Range("A1").Value & _
"</marquee></body></html>"
Open ThisWorkbook.Path & "\marquee_sample7.htm" For Output As #1
Print #1, mbody
Close #1
' add the webpage to webbrowser
Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample7.htm"
' reduce the size of webbrowser
Sheets(1).WebBrowser1.Height = 30
Sheets(1).WebBrowser1.Width = 800
End Sub


Add below code to worksheet module

Private Sub WebBrowser1_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' to remove the scroll bars
On Error Resume Next
Me.WebBrowser1.Document.body.scroll = "no"
End Sub

Download Example 7

11 comments:

  1. Download The working file/ macro here
    http://www.box.com/s/aca50ecc7c45a21d5830

    ReplyDelete
  2. You can also add font and background color to marquee box

    mbody = "<body bgcolor=""#95B9C7""><p style=""font-size:18px;color:#153E7E"" ><marquee behavior=""scroll"" direction=""left"">www.excelvbamacros.com</marquee>"

    html color list

    http://www.w3schools.com/html/html_colornames.asp

    ReplyDelete
  3. Hi Ashish,

    I downloaded the workbook, extracted the .BAS, added the Microsoft WebBrowser and played the macro, actualizing the webbrowser objects of the sheet. But nothing happens.
    Have to say I'm under 2003 version.

    I would really like to see this running under 2003 version (although is a software 10 years old, is still very popular and powerfull enough)

    Best regards

    ReplyDelete
  4. @Xoan.ninguen can you send me the file on koul.ashish@gmail.com

    ReplyDelete
  5. Hi Ashish,
    Brilliant work here.
    I want to take the user input as marquee message and also want to delete the HTML when I close the sheet. Please advise how to do this.
    Regards
    Vishal

    ReplyDelete
  6. VERY NICE JOB YAAR
    TRUELY THIS IS TOO GOOD

    ReplyDelete
  7. Also thank you Ashish! for giving us your valuable thought.

    ReplyDelete
  8. A task I've been striving for its accomplishment. I run the example1 macro but it flagged error with " object didn't support this property or method..at line

    Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample1.htm"
    Please what should I do?

    ReplyDelete
  9. Thank you. Please when I run Example1 above, it flagged errors that "Object didn't support this property or method" at line:
    Sheets(1).WebBrowser1.Navigate ThisWorkbook.Path & "\marquee_sample1.htm"
    Please, what do I do?

    ReplyDelete