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

 

[Joe4]

the idea is when a file is open every sheet is shown if macros are enable

That code will hide the first sheet, and show all the others.
However, the way it is written, it needs to be run manually or called from some other procedure.
It will not run automatically, unless you have a Workbook_Open event procedure calling it.

 

[mole999]

Has your machine macro settings been changed by an admin, i.e. you are prompted about macros when it starts

 

[dfsobral]

joe4
yes i know, as i said it was a open event macro, i place like this to test it
as you said the code should hide first sheet and show the others, it did that but the problem is that now only shows sheet2 and don't hide the sheet1

mole999
the macro runs, just don't run as it should
if i run it once or as open event, shows only one sheet of several hidden
if run a second time works fine have no idea of what is happening
it's a problem in a particular file, i copy the macro to a new one and worked as it was working until recently
i believe the file may be corrupt same way but don't know what to do

 

[Joe4]

Just so you are aware, the way you have written it is NOT an open Event Macro.
A workbook open macro MUST have this as the first line (with no alterations!):

Private Sub Workbook_Open()

and MUST be placed in the "ThisWorkbook" module.

If those two conditions are not met, it will not run automatically.

 

[dfsobral]

Just so you are aware, the way you have written it is NOT an open Event Macro.
A workbook open macro MUST have this as the first line (with no alterations!):

Private Sub Workbook_Open()

and MUST be placed in the "ThisWorkbook" module.

If those two conditions are not met, it will not run automatically.

Again I KNOW, just place it like this to test it
The problem remains regardless is placed as a regular macro or an open event macro
My problem is why after first iteration in

For Each sh In Worksheets
    sh.Visible = xlSheetVisible
Next sh

the macro just stops, don't even reach next sh
even add

For Each sh In Worksheets
    sh.Visible = xlSheetVisible
    Debug.Print sh.Name
Next sh

to test it and after the command
sh.Visible = xlSheetVisible is executed the macro just stops

thank you for your concern about the open event but as i told you i know how it works

this code works fine until recently but now the code is not executed until the end (but only at first try if i execute the macro twice the second time works, but this does not help me in a open event macro)

 

[dfsobral]

hi

finally solve it

I was using a UDF in a conditional format rule, instead of using it directly place the value in a cell and use that cell value to the conditional format rule

start working again

have no idea why the problem occur, even less knowledge of why it stop by doing this
but i'm worried now, a macro can fail to work without any warning with apparently no reason
worst, can be working fine today and tomorrow fail (that's what hapend)

really interest in finding out why in order to be able to trust in automation
Please anyone that have some knowledge about this problem advise me

thanks

 

[Joe4]

thank you for your concern about the open event but as i told you i know how it works

You must understand that I know nothing about you - all that I have to go on is what you have posted in this thread. I know that you said that you know how to use it, but the code you posted is not valid "Worksheet_Open" procedure code. So based on that, I cannot really tell if you really know it or not (or might have made a mistake along the way). So we start with ruling out the most likely things and work from there.

I was using a UDF in a conditional format rule, instead of using it directly place the value in a cell and use that cell value to the conditional format rule

I do not see where a UDF comes in to the question here, or why it would affect the code you posted.

It sounds like you may have a lot of different things going on, and without having access to your file and all the stuff in it, it is difficult to say what the issue may be. Your original code, when put it into a Workbook_Open event, seems to work just fine for me. So my guess is that you have other things interfering with it.

 

[dfsobral]

ok but since in order to test each line of code an open even does not work and i said more than once this was not the initial macro but the one i was using to test ...

relative to the problem you don't see where the UDF comes into question and neither do i, specially because it is never used directly or indirectly in the code, is only used in a conditional formating rule

as i said the code worked fine to me as well until recently, yesterday to be precise

in other files also worked well

i try to test the file in all ways i could think just because.
first found that the macro stopped after first sh.visible command inside the For statment, just didn't run any further code in the macro regardless of what i add to the code to track it's execution, also if remove that particular command everything else would work
then tried multiple things just because and found that if delete a module the event worked then test everything in there, it stoped working only when a particular UDF was present and only if that UDF was used in the conditional format rule

this are the facts, why? absolutely no idea but if this can hapend in this code, it can hapend in others and in situations harder to identify, that leaves me worried
how can a code work till half and then just stop with no warning and no exit command to do so is what i cannot even imagine how it is possible

 

[Joe4]

as i said the code worked fine to me as well until recently, yesterday to be precise

Usually, when something like this happens, the first question to ask is "did anything change since it last work"?
This includes the structure of the sheets, adding VBA code, applying updates, etc.
Things seldom stop working for reason. Usually, there is some sort of change that affected things.

For statment, just didn't run any further code in the macro regardless of what i add to the code to track it's execution, also if remove that particular command everything else would work

Instead of trying to "track" the execution, have you tried stepping through the code using the F8 key?
That way, you can watch what happens each step of the way. If you have two monitors, place the code on one, and the workbook on the other, so you can watch what is happening with each step (if you only have one option, re-size the code box so you can see both).
Many times, when you see what is happening with each step, the problem becomes evident. At the very least, you will see which line of VBA code it quits on, which is also a clue as to what might be happening.