Marquee (moving text) in the excel

[Rahulkr]

Hi all Mentors,

Deeply appreciated any help. I have created one excel for stock entry, there are four sheets, in that; first is Home sheet where in Cell B5 there I want to one moving text from right to left. I was able to achieve that task, but when I am trying to navigate with other sheets within the excel, then it shows error. I tried a lot but still not able to achieve.

Please help me in this. Any help is highly appreciated.

Below is the code I am using for moving text or Marquee in a separate module.

Sub DoMarquee()

For i = 1 To 1
        marq = marq & " !! Please Select Proper Entry Type Before Doing Entry .!!" & Cells(i, 1)
    Next
    marq = WorksheetFunction.Rept("", 300 - Len(marq)) & marq
    Sheets("HOME").Range("b5") = marq

    Do
        For i = 1 To Len(marq)
            DoEvents
            For a = 1 To 4999999
                a = a + 1
            Next
            Sheets("HOME").Range("b5") = Left(marq, i)
        
            
        Next
    Loop
End Sub

And just I have called this function in This workbook module, which is actually working, but when navigating to another Sheet then I am getting the error.

Option Explicit
Private Sub Workbook_Open()
DoMarquee
End Sub

and the error what I am getting is this

Below is the link of raw file, you can download from here.
file
password I used is 1236 for sheets and project password is 7986.

 

[Dan_W]

Thank you for uploading the file and for providing the relevant passwords. That helped in assessing how the code operates in your workbook. As for the issues you've identified:

1. The error message
The code containing the line above that causes the error messages occurs every time you change a value on the HOME worksheet. Each time it runs, it looks for two shapes on the HOME worksheet - "P_Shape" and "S_OUT" - and will make the shape either visible or invisible depending on the value of the cell you've just changed. Besides being arguably inefficient/unnecessary, neither shape appears to exist anywhere in the workbook, much less on the HOME tab where the code appears to be looking for it. As it doesn't exist, VBA produces the error message you reproduced above.

If you're not using these shapes in the workbook anymore, then it seems to me that the entire WorkSheet_Change procedure can be removed, thus ending the error messages.

2. Marquee
The marquee code should run once you've addressed the point above, but note that the code limits it to the HOME tab. It wasn't clear to me whether that's what you wanted, or if you wanted the marquee to run on each of the sheets. In any event, I wonder whether you have considered different approaches to this code - as you've described it, this is a simple marquee code that appears to run from right to left. The issue with coding the solution the way it has been done is that it requires the PC to constantly be running this code on an endless loop - this is an extremely inefficient and potentially error prone approach to what is a very simple visual effect.

Off the top of my head, I would suggest that a better alternative might be to simply paste an animated GIF image file on each sheet you want it to appear on. These short animations will run on an endless loop and will require very little of your CPU. Importantly, they won't lock up Excel/VBA for any other computing tasks you may want it to do.

Basically, what I'm suggesting is that you can delete a lot of the code in your workbook, because from what I can see, what you're trying to accomplish doesn't really need VBA at all. I hope that makes sense, but please do let me know if I've misunderstood anything.

 

[Jaafar Tribak]

Hi,

This thread may interest you. See if you can adapt the code to your specific needs.

Since the code makes the actual text scroll on the DC layer (region) located over the cell, the scrolling is smoother and doesn't stop when editing the worksheet. You can move freely between sheets, zoom, scroll worksheet up & down, move the main excel window etc while the text is scrolling because the DC updated region adjusts itself accordingly.

I have slightly amended the initial code to fix a couple of small bugs.

Here is an updated workbook you can download for testing.

 

[Dan_W]

Hi,

This thread may interest you. See if you can adapt the code to your specific needs.

Since the code makes the actual text scroll on the DC layer (region) located over the cell, the scrolling is smoother and doesn't stop when editing the worksheet. You can move freely between sheets, zoom, scroll worksheet up & down, move the main excel window etc while the text is scrolling because the DC updated region adjusts itself accordingly.

I have slightly amended the initial code to fix a couple of small bugs.

Here is an updated workbook you can download for testing.

Would you believe I was actually thinking about your marquee code when I saw this thread title? Was wondering if I was going to stumble across an implementation of it in 'the wild' ... alas ...

 

[Rahulkr]

Would you believe I was actually thinking about your marquee code when I saw this thread title? Was wondering if I was going to stumble across an implementation of it in 'the wild' ... alas ...

It's highly appreciated Dan_W, but I am very much new to the VBA and I m using office version 2010 and I tried a lot to implement it but I am getting compatible error and also I tried to implement it on workbook open, but not achieved. If possible can you please help me on that. It would be deeply appreciated. Thanks.

 

[Rahulkr]

Thank you for uploading the file and for providing the relevant passwords. That helped in assessing how the code operates in your workbook. As for the issues you've identified:

1. The error message
The code containing the line above that causes the error messages occurs every time you change a value on the HOME worksheet. Each time it runs, it looks for two shapes on the HOME worksheet - "P_Shape" and "S_OUT" - and will make the shape either visible or invisible depending on the value of the cell you've just changed. Besides being arguably inefficient/unnecessary, neither shape appears to exist anywhere in the workbook, much less on the HOME tab where the code appears to be looking for it. As it doesn't exist, VBA produces the error message you reproduced above.

If you're not using these shapes in the workbook anymore, then it seems to me that the entire WorkSheet_Change procedure can be removed, thus ending the error messages.

2. Marquee
The marquee code should run once you've addressed the point above, but note that the code limits it to the HOME tab. It wasn't clear to me whether that's what you wanted, or if you wanted the marquee to run on each of the sheets. In any event, I wonder whether you have considered different approaches to this code - as you've described it, this is a simple marquee code that appears to run from right to left. The issue with coding the solution the way it has been done is that it requires the PC to constantly be running this code on an endless loop - this is an extremely inefficient and potentially error prone approach to what is a very simple visual effect.

Off the top of my head, I would suggest that a better alternative might be to simply paste an animated GIF image file on each sheet you want it to appear on. These short animations will run on an endless loop and will require very little of your CPU. Importantly, they won't lock up Excel/VBA for any other computing tasks you may want it to do.

Basically, what I'm suggesting is that you can delete a lot of the code in your workbook, because from what I can see, what you're trying to accomplish doesn't really need VBA at all. I hope that makes sense, but please do let me know if I've misunderstood anything.

Dan_W firstly thanks a lot to responding on my post and I am totally agreed what you have replied. But the thing is that I am very new to the excel and vba and Denoting to your 2. Marquee point. I want the marquee to run on Home tab only and the rest of the code which I have used is worng then kindly please help me on that.

 

[Dan_W]

Dan_W firstly thanks a lot to responding on my post and I am totally agreed what you have replied. But the thing is that I am very new to the excel and vba and Denoting to your 2. Marquee point. I want the marquee to run on Home tab only and the rest of the code which I have used is worng then kindly please help me on that.

Hi

I can't open your workbook at the moment, unfortunately, because my personal computer with Excel on it is at the "computer hospital" at the moment . I checked to see if I could edit VBA with Excel online, but I can't.

Fortunately, you have helpfully provided a screen capture of the problematic code so it is an easy fix to identify for you.

To fix your workbook, all you need to do is:- delete all the code in this red box

The marquee text will then run on the Home tab the way you want it to.

Everything else that has been suggested are just alternative solutions - the code works fine once the above has been deleted.

 

[Dan_W]

@Rahulkr - how did you get on with this?

 

[Rahulkr]

@Rahulkr - how did you get on with this?

@Dan_W Hi, actually the thing which you have asked me to delete is actually the main thing for doing purchase entry and stockout entry, so I have removed the marquee code and using it without it. But still I am searching for it if possible do let me know.

Thank you very much Dan for responding..

 

[Dan_W]

That's strange, because in the workbook you kindly uploaded, nothing in the VBA or the worksheet relied on this code at all. Moreover, neither of the shapes exist in the entire workbook - this is the reason you're getting the error in the first place. It doesn't really matter what alternative might be available for your marquee text (such as Jaafar's above, which I can help implement if you prefer), this particular error needs to be resolved if the VBA code is to run at all.

Unless you are working with a different version of the workbook which contains additional code/shapes, I don't see how the code I suggested you that you delete would affect the operation of your spreadsheet or the data it contains. I would be happy to look at another/different version of the workbook if that would help.