VBA Cells.Formula won't change value in hidden sheet

Andy L 0
Viewed 26

I'm designing a new item sheet, which will include a different number of tabs depending on the items created.

In order to prevent #REF errors, I have the hidden worksheets ("SINFO", "PINFO", and "DINFO") filled in with formulas all referencing "NEW ITEM (1)"

I'll then run a macro, which updates in a few steps,

Step1: Add sheet names for everything that starts with "NEW ITEM (" into "Hidden Info" sheet range $A$100:$A$200

Step2: (non-vba) excel formulas in column A of sheets "SINFO", "PINFO", and "DINFO", they are checking:

IF(ISNUMBER(MATCH("NEW ITEM ("&ROW()-1&")",'Hidden Info'!$A$100:$A$200,0)),ROW()-1,"-")

(eg. Checks if "NEW ITEM (2)" exists, if so, results 2, else results "-")

I pasted an image below to help show what this data looks like and the formula I'm using in column B.

enter image description here

Step3: If column A <> "-" then will proceed to last step below, where rng = 'Hidden Info'!$A$100:$A$200, and a = the value of column A on "SINFO" tab

(same code is repeated for "SINFO", "PINFO" and "DINFO" tabs)

    str = "NEW ITEM (" & Cells(a, 1).Value & ")"
    With rng
        Set rfind = .Find(What:=str, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rfind Is Nothing Then
            Cells(a, 2).Formula2 = Replace(Cells(a, 2).Formula, "NEW ITEM (1)", str)
        End If
    End With

So far, I haven't had any troubles with this macro running in any of my tests.

The Formula2.Replace function seems to be working perfectly when the sheet is unhidden.

However, for some reason it seems that when the sheet is hidden, it will not update the formulas.

I've tried to add a step into the VBA to do Application.ScreenUpdating = False, unhide all 3 sheets, then at the end of the VBA hide them all again and resume screen updating, but upon opening the sheets it did not update as well.

I've also tried to add a step between adding sheet names to 'Hidden Info' and running this step to check if the Application.CalculationStatus <> xlDone, then DoEvents, because I was thinking it may have been caused by the Excel formulas not calculating in time, but it did not seem to help either.

I'm a bit lost and not sure how to fix this one.

When I unhide the sheets, and then manually run the macro again, it works perfectly, but does not seem to work while they are hidden.

Any ideas on how I can fix this?

vba excel
0 Answers
Related