• 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.

VBA procedure not updating sheet

D

David Epstein

New Member
Hello,

Briefly, I have 2 procedures in a module.

Procedure 1 - reads the sales files and updates the Sales sheet correctly.
Procedure 2 - reads the Sales sheet and, depending on the cell's value, applies the appropriate color.

When procedure 2 is called from procedure 1 (after completion) the colors are not applied (it does not work). However, if I run the 2 procedures separately or in the debug mode it works correctly.

I forced a recalculation on the sheet after procedure 1 finishes and at the very beginning of procedure 2, but nothing works. This includes doevents and setting the application.screenupdating = true, and forcing a recalcuation.

I am really puzzled and I would appreciate your help! Below is a copy of procedure 2. BEFORE you read the code, please remember that it works CORRECTLY if I call the code separately (not calling it from procedure 1). There is not a syntactical mistake.

>>> use code - tags <<<
Code: 
Sub UpdateSalesSheetCellColors()

Application.ScreenUpdating = False

If StartMonth = 99 Then
    beginmonth = 1
    endmonth = 12
Else
    beginmonth = StartMonth
    endmonth = StartMonth
End If

'update Report sheet cell colors
Worksheets("Reports").Select
   
ProductYear = Range("ProductYear").Text
Range("ProductYearFirstProduct").Select
   
Do While ActiveCell.Text <> ""
   
    productname = ActiveCell.Text
    For months = beginmonth To endmonth
       
        Worksheets("Forecast").Select
        'get forecast value for the product/month
        productforecast = WorksheetFunction.Index(Worksheets("Forecast").Range(Cells(2, months + 2), Cells(500, months + 2)), WorksheetFunction.Match(productname & ProductYear, Worksheets("Forecast").Range("P2:p500"), 0))
        productforecastlow = (1 - Worksheets("Control").Range("G2")) * productforecast
        productforecasthigh = (1 + Worksheets("Control").Range("G2")) * productforecast
       
        Worksheets("Reports").Select
        'find product name row
        Range("ProductYearFirstProduct").Select
        Range(Selection, Selection.End(xlDown)).Select
 
        Selection.Find(What:=productname, After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False, SearchFormat:=False).Activate
       
        found = ActiveCell.Row
                   
        'select the cell
        ActiveSheet.Cells(found, months + 1).Select
        salescount = ActiveCell.Value
               
        'apply cell background - green, red or no fill
        If ((salescount > 0) And (salescount >= productforecasthigh)) Then
            'ActiveCell.Interior.Color = RGB(0, 255, 0)
             With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        ElseIf ((salescount > 0) And (salescount <= productforecastlow)) Then
            'ActiveCell.Interior.Color = RGB(255, 0, 0)
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Else 'no background color
            'ActiveCell.Interior.Color = RGB(255, 255, 255)
            With Selection.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
       
    Next months
           
    Worksheets("Reports").Select
    ActiveCell.Offset(1, 0).Select
    Cells(ActiveCell.Row, 1).Select
   
Loop
   
my_cell = Range("ProductYear").Address
cell_row = Range(my_cell).Row
cell_col = Range(my_cell).Column

'moves to the top left corner
ActiveWindow.SmallScroll ToRight:=-9999
ActiveWindow.SmallScroll Up:=-99999

'moves to your active cell
ActiveWindow.ScrollRow = cell_row
ActiveWindow.ScrollColumn = cell_col

'selects your cell
Range(my_cell).Select
DoEvents

StartMonth = 99
MsgBox "The Sales sheet's colors were updated."
Application.ScreenUpdating = True
   

End Sub
 

David Epstein​

Your opened thread in Ask an Excel Question -forum.
Based Your written text, this should be in VBA Macros -forum.
In this time, this has moved to correct forum.
 

David Epstein​

# Do You use public variables?
eg where comes value for StartMonth? Is it same if Your run Procedure 2 from Procedure 1 or 'only' Procedure 2?
# Is ActiveCell in both ways same?
# Have You verified, which lines will run if You run Procedure 2 from Procedure 1?
eg if StartMonth is empty then For months = beginmonth To endmonth could give unexpected results.
 
Yes, StartMonth is a public variable that I use to only assign colors to the current month. When it is set to 99 then it process all (12) months.

I don't understand your comment, "Is ActiveCell in both ways same?".

The StartMonth is set to 99 or the current month. It can never be blank/0.
 

David Epstein​

One more time ...
# where comes value for StartMonth?
Your code do not give any idea - where it comes? ... which sets it to the current month?
Yes, if it's 99 then from 1 to 12, but ... if not? I won't guess - neither Excel.
# Is it same if Your run Procedure 2 from Procedure 1 or 'only' Procedure 2?
What is StartMonth's value just before You run Procedure 2 from Procedure 1?
# Have You verified it? Have You verified it in the beginning of Procedure 2?
I don't understand your comment, "Is ActiveCell in both ways same?".
# What is ActiveCell just before You run Procedure 2 from Procedure 1?
Have You verified - where is Your ActiveCell in the beginning of Procedure 2?
... especially before Do While ActiveCell.Text <> ""
# Have You verified, which lines will run if You run Procedure 2 from Procedure 1?
If You have - then You should know - what do it do? ... but You skipped to answer.
There can be case that none loops will run.
# Do You get MsgBox "The Sales sheet's colors were updated."?
 
Yes, StartMonth is a public variable that I use to only assign colors to the current month. When it is set to 99 then it process all (12) months.

I don't understand your comment, "Is ActiveCell in both ways same?".

The StartMonth is set to 99 or the current month. It can never be blank/0.
 

David Epstein​

I see that You pasted #4 reply again.
As well as
You skipped all my #5 questions.
If You cannot eg verify that which lines will run if You run Procedure 2 from Procedure 1?
It's a challenge to guess more.
 
Given your assertion that the code in procedure 2 must be correct since it works on its own, and that it only fails if run from procedure 1 (which would tend to suggest that the code is not in fact correct, otherwise it wouldn't fail), it would make sense to post procedure 1 as well, wouldn't it? ;)
 
You must log in or register to reply here.
Back
Top