• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro engaging but not working, and produces error message after running thorough the entire macro.

Eloise T

Eloise T

Active Member
I am trying to understand why the following error message appears after seemingly "successfully" stepping through the "StartFlashing()" macro with the VBEditor focused on the SEVENTH tab in the attached file. Running the macro directly results in the same error message. Either the flashing is happening too rapidly to see the flashing square root symbols or it's not working at all.

upload_2018-8-13_13-44-41.png

If anyone can explain why this happens and a suitable remedy I would certainly appreciate it.
 

Attachments

  • SANITIZED - Master Invoice.xlsm
    836.8 KB · Views: 17
The last line of your code says this
Code: 
Application.OnTime xTime, "StartFlashing", , True

Which is saying that after finishing, it will run again at a future time. It's this second time that's failing. Reason is that XL expects regular macros to be stored in code modules, not sheet or ThisWorkbook module. If you cut and paste all your code into one of your regular code modules, it'll work.
 
You hit the nail on the head. I don't know the difference between a code module and a sheet module or any other kind of module. How do I tell the difference? ...

When I "engage" VBA code, I hit ALT+F11, then F7, then paste the code there.

What should I do differently?

Thank you.
 
Last edited:
Clicking on F7 puts the code into the Sheet module. If you wish, as generally is the practice, to put it into the code module, click on the Insert button on the menu line and select module. Sheet and Workbook modules should generally be used for events that are triggered by actions, ie. Open, change, etc.
 

With a non general module you must insert the module name
and a dot before the procedure name …​
 
We've now discovered "general modules" in addition to code modules and sheet modules? Marc, please explain non general, etc. and give examples so my ignorant VBA mind can absorb this new information.

Here's what I've discovered...
I feel like I'm trying to help a friend trying to get over a bad cold.
I have to have the macros StartFlashing and StopFlashing in BOTH the ThisWorkbook module AND the Module 1 before they "work" without causing an error or other undesirable result. Removing the (identical) code from either ThisWorkbook module OR Module 1 causes convulsions, sneezing, wheezing, and uncontrolled coughing.

THAT'S the good news. The Bad news is the wrong cells in Column K are flashing. The VBA code (in the StartFlashing macro) is ignoring the √s and changing the otherwise black dashes to red and flashing the dashes and not the √s. I'm pulling my hair out.

Updated file uploaded.

Help...please.
 

Attachments

  • SANITIZED - Master Invoice.xlsm
    838.4 KB · Views: 7
Last edited:
Sorry for confusion Eloise. If you have the Project expolorer open in the VBE, it should look like this. If not, go to View - Project Explorer.

upload_2018-8-15_8-10-13.png

This is a list of all the "things" you have in your project. You can faintly see that the ThisWorkbook module is highlighted, indicating that this would be the module I'm looking at (not shown in picture). This is where you code is currently sitting. There's also sheet modules, for each of the Sheet1, Sheet2, etc. You don't have anything in there currently.

You'll see there's another group called Modules, with 3 regular code modules (Module1, Module2, etc.). You need to use one of these for your code. Double-click on the item in the Project explorer, and you'll go to that corresponding module.

Quick run down:

ThisWorkbook module should be used for Workbook level macros, stuff that's triggered by saving, closing, etc.

Sheet modules should have sheet dependent macros, like change events, or selection changes.

Regular modules (aka general modules, aka standard code modules) should have more generic code, and anything that needs to be referenced by other code.

additional reading: http://www.cpearson.com/excel/codemods.htm
 
Luke,
The additional reading helped. Thanks.

Please read (fully) response #6 and note updated upload.


I had to put the macros StartFlashing and StopFlashing in BOTH
the ThisWorkbook module AND the Module 1 before StartFlashing and StopFlashing "work" without causing an error or other undesirable result.

Removing the code from either ThisWorkbook module OR Module 1 caused Excelerated convulsions, sneezing, wheezing, and uncontrolled coughing.

I'll figure out the module game eventually. Thanks.

Any idea why I can't get the √s to flash? It doesn't seem to matter if they are "generated" by the underlying formula and purposely placed there by hand, the VBA doesn't seem to recognize them.
 
Last edited:
Please see attachment to follow up with the above #8 reply.
Thank you.

Code: 
Sub StartFlashing()
    Dim Ws As Worksheet, xCell As Range, c As Range, t As Double
    For Each Ws In ThisWorkbook.Worksheets
    If Ws.Index > 6 Then
            Set xCell = Ws.Range("K3:K15")
            For Each c In xCell

                If c = WorksheetFunction.Unichar(8730) Then 'a square root symbol
'                  THE ABOVE LINE SEEMS TO BE IGNORED.

                   If c.Font.Color = vbRed Then
                      c.Font.Color = RGB(238, 236, 225) 'tan background color
                    Else
                      c.Font.Color = vbRed
                   End If

                End If

            Next c
        End If
    Next Ws
    xTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime xTime, "StartFlashing", , True
End Sub
 

Attachments

  • SANITIZED - Master Invoice.xlsm
    847.2 KB · Views: 1
Your conditional formatting rule is preventing the flashing - remove it.

The only code that should be in the ThisWorkbook module, is this:

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error resume next
    StopFlashing
End Sub

Private Sub Workbook_Open()
    Call StartFlashing
End Sub

All the rest of it should be in a normal module.
 

Attachments

  • SANITIZED - Master Invoice.xlsm
    786.3 KB · Views: 3
Debaser,

*B*R*I*L*L*I*A*N*T*!* Thank you!

iu


...and to all the other contributors, a big thanks as well.
 
Last edited:
Debaser said:
I still feel slightly unclean helping with such things, but you're welcome.
Click to expand...
I don't understand "unclean." Why so?

Some background...I was thrust into a new position at work about three years ago (to date) with just about zero Excel experience (no, I had zero previous Excel experience) and stumbling onto Chandoo.org saved my bacon (read: my job). The company would not pay to send me to any Excel classes to get me up to speed.

My entire Excel experience has come from contributors like yourself and reading other bloggers problems exclusively at this website.
 
Last edited:
Debaser said:
Anyone who sent me a workbook with flashing cells in it would come to regret that decision. I can't abide them! Worse even than pie charts. Though maybe not 3D pie charts.
Click to expand...
I understand. That seems to be a general consensus...and understandably so.

There should only be zero to three flashing cells (√s) per week alerting me to a potential and nasty faux pas and once they are noted, they are deleted as they have served their purpose.

It's a job-saving safety check and control device to make sure nothing slips through the cracks which has happened once or twice in the past.
 
Last edited:
I still feel slightly unclean helping with such things, but you're welcome.
Click to expand...
I don't understand "unclean." Why so?
Click to expand...

I think Debaser is referring to the idea of flashing cells in general being unattractive and a visually offensive solution, an opinion I find myself agreeing with - I tend to get a headache.
With regards to your question, how you framed your question, the pursuit of learning, or the source of your learning material, I have no issues there, and I don't think Debaser has either.

I'm glad you found a solution from an problem solving perspective, I hope you don't have to work with repeatedly flashing cells again in the future - I'm not sure I could handle it!

Stevie

Edit: I believe we replied almost simultaneously. Your response renders mine unnecessary, I'm relieved that you aren't subjected to flashing cells all day!
 
Stevie said:
I think Debaser is referring to the idea of flashing cells in general being unattractive and a visually offensive solution, an opinion I find myself agreeing with - I tend to get a headache.
With regards to your question, how you framed your question, the pursuit of learning, or the source of your learning material, I have no issues there, and I don't think Debaser has either.

I'm glad you found a solution from an problem solving perspective, I hope you don't have to work with repeatedly flashing cells again in the future - I'm not sure I could handle it!

Stevie

Edit: I believe we replied almost simultaneously. Your response renders mine unnecessary, I'm relieved that you aren't subjected to flashing cells all day!
Click to expand...
I fully understand. Thanks for your input.
 
My 2 cents ppv : permanent flashing in Excel is a mess
as it was not designed for that.
I just use a background color to highlight any cell
(whatever via a conditional formatting or via a procedure)
and / or even a popup message.
In a few cases I have designed a temporary flashing
but never stay with a permanent one
as it could raise too many cases of issue …
​
 
You must log in or register to reply here.
Back
Top