PDA

View Full Version : Solved: Excel Text Newsbar - Moving Text



Derek
10-31-2005, 04:07 AM
Hi Folks :hi:

I am trying to add a "newsbar" along the top of a spreadsheet, with information on it to be from a hidden linked cell in a separate workbook. This information will be in text form and longer then the display area so I would like the text to "scroll" within the cell automatically and continuously.

I have checked for formatting options for a cell or for a textbox and I can't find anything which will help. Is there code for this? Is it better in a formatted cell or in a textbox?

Please help if you can

mvidas
10-31-2005, 07:17 AM
Hi Derek, welcome to VBAX!

Sorry, I accidentally hit post before I finished this.

Unfortunately, I do not believe there is any kind of built-in component to do this. You can write your own code to make a 'scrolling newsbar', but I wouldn't recommend it really as you can't do anything else while it is running. I'm hoping someone else knows of a way, but the only way I know how is (this is using the statusbar, but you could easily change that to a cell reference or text box)Sub derek()
Dim LongText As String, i As Long, maxLen As Long, j As Long, IsBegin As Boolean

maxLen = 110 'characters to show
LongText = "I am trying to add a ""newsbar"" along the top of a spreadsheet, with" & _
" information on it to be from a hidden linked cell in a separate workbook. This" & _
" information will be in text form and longer then the display area so I would l" & _
"ike the text to ""scroll"" within the cell automatically and continuously."
LongText = Space$(maxLen) & LongText & Space$(maxLen)

Do
For i = maxLen To Len(LongText)
For j = 1 To 3000000
Next
Application.StatusBar = Mid$(LongText, IIf(i = maxLen, 1, i - maxLen), maxLen)
Next
Loop

Application.StatusBar = False
End SubMatt

Killian
10-31-2005, 11:48 AM
No, not something that's going to happen very easily...
The issue here is that you need a timer running that fires an event to update your cell text and to have some kind of animation feel to it, it will need to fire around 4 times a second.
The problem, as Matt pointed out, is the in VBA, there isn't a native way of doing that, a getting other things to go on at the same time is a problem.

It is possible to set up a suitable timer using a Win API function, but you can't edit the workbook while it's running (I tried it out and the scrolling text cell does look cool, but Excel crashes as soon as you edit the worksheet)
It is something that you could make work on a user form but to have text scrolling in a cell while your editing the worksheet - I don't believe so...

Killian
10-31-2005, 03:00 PM
OK, I had a think about this and it seems like a handy thing to have - some kind of live scrolling feed at the top of a worksheet...
I still don't think having it in a cell is going to happen - a modeless form on the other hand, now that I knew was possible. Also, it's nice and easy for poeple to add into a project, so I've kind of worked out some of the details in the attached workbook.

It's basically a non-modal form that is shown on the workbook activate event, which also triggers the active window to switch to normal mode and resize, leaving space along the top for the form to go into. (I added the tweak of removing the title bar and frame).
The deactivate event, unloads the form and maximizes the document window again.

The text for the feed is currently just from a cell on a worksheet - I guess it could come from anywhere, but bear in mind that each timer event (it's set for every 125ms here) has to re-parse the string, so if you have a large amount of text, you would want to work on a nice, efficient text parser that just uses the string variable as a buffer to the text to output or your overhead on each timer event will become too much.

It could do with some refinement, but the principle is sound and it seems to work. Hope it's of some use.

Derek
10-31-2005, 03:16 PM
Thank you so much for this. I will get to work on this tomorrow to see if I can use this method on my workbook. I will come back asap and let you know how I get on but I think you have something for me here!! :rotlaugh:

Ivan F Moala
10-31-2005, 11:53 PM
This is something more easily done via the Webbrowser control and some scrolling text code ........ have a look here for examples ....

http://www.xcelfiles.com/WebBrowserCtrl.html

Derek
11-01-2005, 04:30 AM
Thank you for your input. I have never used the webbrowser control. In fact I had never heard of the webbrowser control! :doh: How easy is it for a newbie like me to use? VBA code I can understand a little, but this looks like a whole new tool to use. Will it have similar results to the sample spreadsheet mentioned above? Is it less memory intensive? As flexible?

mvidas
11-01-2005, 06:03 AM
Good call Ivan!

Derek, paste the following code in blank module:Option Explicit
Private Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, ByVal _
hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
ByVal cy As Long, ByVal wFlags As Long) As Long
Sub IEProgressExample()
Dim IE As Object, Scroller As Object
StartIE IE
Set Scroller = IE.Document.All("Scroller")
Scroller.innerhtml = "I am trying to add a ""newsbar"" along the top of a " & _
"spreadsheet, with information on it to be from a hidden linked cell in a" & _
" separate workbook. This information will be in text form and longer then" & _
" the display area so I would like the text to ""scroll"" within the cell " & _
"automatically and continuously."
Set IE = Nothing
Set Scroller = Nothing
End Sub
Function StartIE(ByRef IE As Object) As Boolean
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Navigate2 "about:blank"
Do While .readyState <> 4
Loop
.Document.Title = "Progress"
.Document.Body.innerhtml = _
"<BODY><CENTER><FONT FACE='arial black' SIZE=2>" & vbLf & _
"<marquee><DIV id='Scroller' ALIGN='Left'></DIV></marquee>" & vbLf & _
"</FONT></CENTER></BODY>"
.Document.Body.Scroll = "no" 'scrollbars
.Toolbar = False
.StatusBar = False
.Resizable = False
.Width = 435
.Height = 75
.Left = 0
.Top = 0
.Visible = True
SetWindowPos .hWnd, -1, 0, 0, 0, 0, 3
End With
End FunctionSee how that works for you.

Not the actual webbrowser control, but an IE window with the marquee tag, which scrolls text.

Didn't even think of using that, again good call Ivan!
Matt

Derek
11-01-2005, 09:33 AM
Wow

More info than I can handle!! :bug:
I have been wroking on the first suggestion for a couple of hours now. It is just about there but the scrolling text seems mto start from the middle of the form. Any thoughts? I tried to copy and paste the code below into a new excel form but other than asking me to enable macros, the sheet opened up with nothing else on it. Would I need to add any refernces first?

Sorry if I am being a pest!! : pray2:

Derek

mvidas
11-01-2005, 10:02 AM
Hi Derek,

Not being a pest at all.

The code I posted was to create a separate IE window with your scrolling text. If you want to do this to your webbrowser control, use the following:Option Explicit
Sub ScrollingExample()
Dim WebB As Object, Scroller As Object
Set WebB = Sheet1.WebBrowser1 'your webbrowser object
StartScroll WebB
Set Scroller = WebB.Document.All("Scroller")
Scroller.innerhtml = "I am trying to add a ""newsbar"" along the top of a " & _
"spreadsheet, with information on it to be from a hidden linked cell in a" & _
" separate workbook. This information will be in text form and longer then" & _
" the display area so I would like the text to ""scroll"" within the cell " & _
"automatically and continuously."
Set WebB = Nothing
Set Scroller = Nothing
End Sub
Function StartScroll(ByRef WebB As Object) As Boolean
With WebB
.Navigate2 "about:blank"
Do While .ReadyState <> 4
DoEvents
Loop
.Document.Body.innerhtml = _
"<BODY><CENTER><FONT FACE='arial black' SIZE=2>" & vbLf & _
"<marquee><DIV id='Scroller' ALIGN='Left'></DIV></marquee>" & vbLf & _
"</FONT></CENTER></BODY>"
.Document.Body.Scroll = "no" 'scrollbars
End With
End FunctionJust run the ScrollingExample sub
Matt

Derek
11-02-2005, 10:35 AM
Thanks for everyone who helped on this. I, having some spent some hours on the first suggestion, have got this working a treat. I now have an easily updateable scrolling news bar on my spreadsheet-based programme......now as soon as I can figure out how to mark this as solved I will do!! :clap: :rotlaugh: :friends: :beerchug:

PS I will definitely be back for some help and will certainly be suugesting this site to friends & colleagues. Thanks again:)

mvidas
11-02-2005, 11:28 AM
Glad we could help!

Bob Phillips
11-02-2005, 12:41 PM
This is quite a nice use of callback's, but isn't the obvious place to put the text in the applicatuion caption bar?

mvidas
11-02-2005, 12:57 PM
This is quite a nice use of callback's, but isn't the obvious place to put the text in the applicatuion caption bar?
I think that just would have been a drain on resources, as the macro would have to continuously be running (wouldn't it?) like I used the status bar in http://www.vbaexpress.com/forum/showpost.php?p=48835&postcount=2 . Ivan's idea of using html to control it makes it scrolling without having running code. Though having it in the caption bar would make it easier to stand out

jolivanes
11-02-2005, 01:27 PM
Have a look at this.
All accolades go to Jaafar (rafaaj2000)
John

steve.t
12-12-2007, 06:18 PM
Hi all,

I like that last post with Scrolling Text.zip. I am new to VBA and not very proficient, so....

I wonder if anyone would take it upon themselves to modify the code slightly; what would help me would be if the scrolling text control could be created automatically on book open, and only appear on one worksheet.

Regards and thanks,
Steve

Apps
09-24-2008, 05:31 AM
Know that this is an old post but would just like to thank Mvidas for his example code which has just helped solve a problem I was having writing and displaying text as html (without the need for creating a .hmtl file first and referencing it) in webbrowser on an Excel Userform. I had previously done this ok in VB6 but trying to recreate it in an Excel Userform was giving me a real headache!! :banghead: lol
Thanks again. :)