code no longer work

[dfsobral]

hi

the following code always worked but know fails

Sub SubTeste()
Dim sh As Worksheet

For Each sh In Worksheets
    sh.Visible = xlSheetVisible
Next sh
Worksheets("Sheet1").Visible = xlSheetVeryHidden
End Sub

the idea is when a file is open every sheet is shown if macros are enable
it always worked fine but now it shows only one sheet and don't hide sheet1

already tried in a new file and still works fine, only in this particular one it fails
tried to repair the file though open and repair but nothing happens

the macro was an open event, tried to run it as a regular macro (as shown) but the same occur
the strange is that if i run it a second time it works fine, only the first time this error occurs

anyone can tell me why and how to fix it

thanks

 

[dfsobral]

the only thing that i did since last time the file worked fine was adding columns in a particular sheet, but that was the objective of the file, had done it often and never return any problem

regarding to F8 i use it, that's why i had to execute the macro without a open event macro
that's why i knew the macro stopped working after sh.visible command
but the truly weird thing was that if i run the macro once the problem occur and the code did not reach the end, if run it again worked fine
that was not a solution however because an open event would run it only once

 

[Joe4]

How many sheets are in the workbook?
Before opening the workbook, which ones are visible and which ones are not?

 

[dfsobral]

presently 5
one to be hidden when working and shown before save
the others the opposite

 

[Joe4]

So, when the user opens the Workbook, if the Workbook_Open event procedure does not run (i.e. disabled or removed), which sheet would be hidden?
Sheet1, or a different one?

 

[dfsobral]

when the file opens sheet1 is visible and sheets 2 to 5 are veryhidden

then event procedures turns sheets 2 to 5 visible and hides sheet1

then there's another event before save that turns sheet1 visible and hides sheets 2 to 5

the objective is if macros are not enable the user has no access to the file, only a message to enable macros

this always worked until now, the before save event works the open event now only works if the UDF is not used directly on the conditional formatting rule

 

[mole999]

when the file opens sheet1 is visible and sheets 2 to 5 are veryhidden

then event procedures turns sheets 2 to 5 visible and hides sheet1

then there's another event before save that turns sheet1 visible and hides sheets 2 to 5

the objective is if macros are not enable the user has no access to the file, only a message to enable macros

this always worked until now, the before save event works the open event now only works if the UDF is not used directly on the conditional formatting rule

can we see the complete UDF (between code tags)

 

[dfsobral]

Function Prevision(rng As Range)


Application.Volatile


Dim cell As Range
Dim total As Double
Dim score As Byte


total = 180 - WorksheetFunction.Sum(rng.Offset(0, 7))


For Each cell In rng
    If total <= 0 Then GoTo MyDestination
    If IsEmpty(cell.Offset(0, 7)) Then
        score = Int(cell.Offset(0, 2) + cell.Offset(0, 3))
        If score > 0 Then total = total - score
        Prevision = Prevision + 1
    End If
Next cell


MyDestination:


End Function

this UDF gives me a number that is used to a conditional format rule
if i use it in the rule the open event macro doesn't work anymore, but it worked until recently
i place the formula in a cell and use the cell value in the conditional format and start to work again