Need help "stopping" yellow highlighting at the bottom of the data.

[Eloise T]

No as only the first one is for 64 bits so with PtrSafe …​

Compatibility Between the 32-bit and 64-bit Versions

Try with only the first Declare codeline but if it works,
it works only on 64 bits Office version instead of both versions …

Apparently it will only work as 64-bit as it doesn't like it when PtrSafe is missing.

Of course yes it is what I expected for
'cause Demo1 was still within a worksheet module (Sheet1) ‼
As any code won't disappear only if
it is not located in the worksheet to be deleted !

Just read again child level directions I wrote twice …

In this sample on VBE side

This is what I see:


Why does this order matter? ...or does it?
​

Sheet1 is the CodeName of the worksheet "abc" and
as it is grey highlighted so it is its code window which is active …
OK.

You just have to open ThisWorkbook module and paste code within
instead of any worksheet module window already opened …

So, I clicked on ThisWorkbook and now it is grey. Are you saying I need to move my VBA code from Sheet1(Ohio-1) to ThisWorkbook?



If you see CleanUp_8 procedure in the code window
so you are in the wrong place as this code is
located in the Sheet1 module of worksheet "Ohio-1" ‼
As you can visual check which module is grey highlighted …

Be sure to (Click to expand...) again above.

I moved the VBA code to ThisWorkbook from Ohio-1. The YellowHighlightAThroughHVERSION2 macro successfully runs but accomplishes nothing. What am I doing wrong?
Signed,
Frustrated

 

[Marc L]

If you say it does not like without trying to execute the kBeepDemo,
just try it before !

The CodeNames worksheets modules order does not matter …

My Demo1 procedure needs to be located in ThisWorkbook module
(or in any standard one but not within any worksheet module) …
​

 

[Eloise T]

I moved the VBA code to ThisWorkbook from Ohio-1. The YellowHighlightAThroughHVERSION2 (which is mostly your code from Demo1) macro successfully runs but accomplishes nothing. What am I doing wrong?

 

[Marc L]

A bad mod : where is the yellow color statement within your code ?!

Execute first my Demo1 procedure located in Thisworkbook module
as it rocks on my side so why not in yours ?
​

 

[Eloise T]

I cut and copied ALL VBA macros from Ohio-1 and pasted it into Thisworkbook module. Demo1 worked!

Am I able to delete all the macros listed under Ohio-1 now as they are all also listed under Thisworkbook?

...and how did all those macros get bunched under Ohio-1 in the first place?

 

[Marc L]

Am I able to delete all the macros listed under Ohio-1 now as they are all also listed under Thisworkbook

Yes only if you're sure and if each procedure still works …

how did all those macros get bunched under Ohio-1 in the first place?

As it is the place where someone pasted / wrote the procedures …
​

 

[Eloise T]

I deleted all the macros under Ohio-1 and made sure they're all now under ThisWorkbook. The problem is now ... referring back to the SANITIZED - Master Invoice file, depending on how I use the macro, e.g. ThisWorkbook.Swap_Tab_Color, I get:


...so how do I "register" all the macros so they work wherever and whenever I need them? ...save a copy in Ohio-1 AND ThisWorkbook? Surely there must be a way to have the whole Excel file point to "ThisWorkbook." macros just like they used to work when they were just "Sheet1."

 

[Marc L]

When you get this message, Demo1 still works ?
No message on my side, how do you launch your procedure ?
If it's via any button, you just forgot to reaffect to it
the procedure in its new location …

As I yet wrote, the place where stands a procedure can impact
its behavior as you already saw with my Demo1 …
And worse, even if there is no error, some result can differ !
​

 

[Eloise T]

Correct. Demo1 still works. Thank you.

All but two macros I launch with ALT + F8. Swap_Tab_Colors and Tab_Colors_Off are launched with buttons and would not work until I put them back into Sheet1(Ohio-1)

Is there a way to "reaffect them" into ThisWorkbook?

 

[Marc L]

Move first the procedures to ThisWorkbook module then the part
you forgot : right click on button to update the procedure …
​

 

[Eloise T]

Note the picture in "thread #34" above. Both macros appear in Sheet1(Ohio-1) and ThisWorkbook.

"Right clicked on button to update the procedure" as you suggested and now it works. Thank you again. ...unfortunately had two dozen buttons to fix...one for each tab.

 

[Eloise T]

The most important thing is for me to understand why Demo1 works.
What is the line of code in Demo1 that knows not to yellow highlight a row that has "NO INV..." in it? I suspect it is around "Rg(1).Row < 3" but if so, why?

Sub Demo1()

          Dim Ws As Worksheet, Rg(1) As Range
          Application.ScreenUpdating = False
    For Each Ws In Worksheets
              Set Rg(1) = Ws.Cells(Rows.Count, 1).End(xlUp)
        Do Until Rg(1).Interior.Color = vbYellow Or Rg(1).Row < 3
              If Rg(1)(0).Value = "" Or Rg(1)(0).Interior.Color = vbYellow Then
                  Set Rg(0) = Rg(1)
              Else
                  Set Rg(0) = Rg(1).End(xlUp)
                   If Rg(0).Row < 3 Then Set Rg(0) = Ws.[A3]
              End If
            With Range(Rg(0), Rg(1)(1, 8)).Columns
                .Interior.Color = vbYellow
                .Borders.LineStyle = xlContinuous
                .Item("E:F").HorizontalAlignment = xlCenter
                .Item(7).NumberFormat = "m/d/yyyy"
                .Item(8).NumberFormat = """$""#,##0"
            End With
              Set Rg(1) = Rg(0).End(xlUp)
        Loop
    Next
          Erase Rg
          Application.ScreenUpdating = True

End Sub

 

[Marc L]

If I were kidding, I would answer « None ‼ »

In fact it just uses an Excel basics inner feature :
from your attachment worksheet Paul, directly go on cell A1000.
Once cell A1000 selected, do a keyboard combo : CTRL ↑
so now the active cell is A576. Notice row # 577 is yet avoided …
Same combo again so the active cell is now A574.
So we have a block from row# 574 to 576. [Rg(0) to Rg(1)]

Again same combo : active cell is A572 so the row #573 is avoided …
It's the way the code works as the combo is End(xlUp) …
​

 

[Eloise T]

OK, so CTRL + up arrow went col A, row 1000 to 576 then 574 then 572 then 561! Why did it jump more than 2? In fact, when continuing further, it followed the pattern of the existing yellow highlighting. Now I'm really bewildered.

 

[Marc L]

It was just to show you the main idea but if you look at the code
you should see the tests upon yellow color, row # and value …

Follow the code execution in step by step mode and
check each range address …
​

 

[Eloise T]

I know how to follow the code execution in step-by-step mode but how/where do you check each range address?

 

[Marc L]

Two ways :

• Manually in the VBE Immediate window : ?rg(1).address and hit Enter key

• Add a codeline Debug.Print Rg(1).Address (or via MsgBox) …
​

 

[Eloise T]

Any special place I should put the code line if I go that route?

 

[Marc L]

Just before the With codeline where the entire range is located …​

 

[Eloise T]

OK, thanks. Your quick responses are appreciated. I've got to catch up with some other things before I can implement it on my end. Hopefully I can do that soon.

Just before the With codeline where the entire range is located …​

 

[Eloise T]

One more odd question. Every time I open up the "Master file"
and hit ALT + F8 I see:



Note where 'Master Technicians TABBED Invoice.xslm'!Demo1 is located.

If I click [Cancel] and hit ALT + F8 again, and every time thereafter, the "Demo1" has "graduated" to ThisWorkbook.Demo1 and I see:



If I exit the file and reopen, the process starts again. Any ideas why?

 

[Marc L]

Maybe a Microsoft "ghost" but don't worry !

To anticipate a question the only thing not yet in VBA inner help :

Rg(1)(1, 8) is the same as Rg(1).Offset(, 7) …
​

 

[Eloise T]

Thank you.

 

[Eloise T]

No as only the first one is for 64 bits so with PtrSafe …

Compatibility Between the 32-bit and 64-bit Versions

Try with only the first Declare codeline but if it works,
it works only on 64 bits Office version instead of both versions …


Of course yes it is what I expected for
'cause Demo1 was still within a worksheet module (Sheet1) ‼
As any code won't disappear only if
it is not located in the worksheet to be deleted !

Just read again child level directions I wrote twice …

In this sample on VBE side

Sheet1 is the CodeName of the worksheet "abc" and
as it is grey highlighted so it is its code window which is active …

You just have to open ThisWorkbook module and paste code within
instead of any worsheet module window already opened …

If you see CleanUp_8 procedure in the code window
so you are in the wrong place as this code is
located in the Sheet1 module of worksheet "Ohio-1" ‼
As you can visual check which module is grey highlighted …​

I "thought" I understood the placement of VBA modules in the "correct" sheets as listed in the VBE. But alas, I do not.

Moving the VBAs to ThisWorkbook module made a difference, with the "real" workbook, but somehow in the SANITIZED workbook, it's taken on a difference persona.

In the attached file (SANITIZED), the VBAs work, but they are in the mMain module (wherever that came from) and when I transfer them to the ThisWorkbook module in the "real" workbook, the VBA chokes.

What's the problem?

 

[Marc L]

The issue you met in post #11 was 'cause my procedure wasn't written
to be in a worksheet class module but just for ThisWorkbook
or either a general / standard module …
Of course I could mod it in order to work within the worksheet module
but it wasn't necessary and safer in case of the worksheet deleted …

★ ★
​

As your code use a timer event to launch a procedure,
the easy way is to let this procedure in a standard module.

If you want to move it in ThisWorkbook module (but why ?!)
you must mod each codeline calling this procedure by adding
the reference to ThisWorkbook …
Even if it works, you may have an issue with your global variable xTime
which may require some mod too …
So as yet written, the easy way is to let it as it is, in a general module …

My original Demo1 procedure works equally in ThisWorkbook module
or either in a normal module.

★ ★​

Running any VBA procedure when a timer event procedure may raise
could be a mess so I try to never use such timer procedure
when within the workbook there are other VBA procedures
particularly in case of several worksheets within the workbook …
I prefer to flash temporarily and highlight the cell after.
​