My code stops working
 

I have the follwoing code in Sheet1. Using it, I can hide og show specific sheets by putting x'es in cells A1 to H1. It woeks nicely, but if I rename just one of my sheets, it stops working all together. It doesn't show any errors, absolutely nothing happens. The code is simply not run at all. Even if I put a Stop in the very first line of code, it doesn't get there.

Any ideas

Regards
Jan

PS! I use Danish versions of Excel 2007and 2010.

The code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tar As String
tar = Target.Address
On Error GoTo err

Select Case tar
Case Is = "$A$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(2).Visible = True
Else
Sheets(2).Visible = False
End If
Case Is = "$B$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(3).Visible = True
Else
Sheets(3).Visible = False
End If
Case Is = "$C$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(4).Visible = True
Else
Sheets(4).Visible = False
End If
Case Is = "$D$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(5).Visible = True
Else
Sheets(5).Visible = False
End If
Case Is = "$E$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(6).Visible = True
Else
Sheets(6).Visible = False
End If
Case Is = "$F$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(7).Visible = True
Else
Sheets(7).Visible = False
End If
Case Is = "$G$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(8).Visible = True
Else
Sheets(8).Visible = False
End If
Case Is = "$H$1"
If UCase(Range(tar).Value) = "X" Then
Sheets(9).Visible = True
Else
Sheets(9).Visible = False
End If
Case Else
Exit Sub
End Select
err:

If err.Number = 9 Then
MsgBox "Du kan ikke vise og skjule et ark, som ikke eksisterer. Prøv med et andet ark!", vbCritical + vbOKOnly
End If

End Sub

My code stops working
 

Hi Jan,

Am Thu, 9 Jun 2016 01:06:55 -0700 (PDT) schrieb :

I have the follwoing code in Sheet1. Using it, I can hide og show specific sheets by putting x'es in cells A1 to H1. It woeks nicely, but if I rename just one of my sheets, it stops working all together. It doesn't show any errors, absolutely nothing happens. The code is simply not run at all. Even if I put a Stop in the very first line of code, it doesn't get there.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:H1")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Sheets(Target.Column + 1).Visible = (UCase(Target) = "X")
End Sub


Regards
Claus B.
--
Windows10
Office 2016

My code stops working
 

Nice code. Musch shorter than mine. But still same or similar problem. It works fine a few times or five, then I make some kind of change to the sheet, not just renaming it, and the event is no logner triggered. When I then run another macro, og close and open the workbook, the event is triggere again, so it as probably nothing to do with the code itself.

Jan

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:H1")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Sheets(Target.Column + 1).Visible = (UCase(Target) = "X")
End Sub


Regards
Claus B.
--
Windows10
Office 2016

My code stops working
 

Hi Jan,

Am Thu, 9 Jun 2016 02:45:46 -0700 (PDT) schrieb :

Nice code. Musch shorter than mine. But still same or similar problem. It works fine a few times or five, then I make some kind of change to the sheet, not just renaming it, and the event is no logner triggered. When I then run another macro, og close and open the workbook, the event is triggere again, so it as probably nothing to do with the code itself.

do you have another code that disables events if it run into error?


Regards
Claus B.
--
Windows10
Office 2016

My code stops working
 

No other code in the workbook. But now that I tink about it, I have experience similar problems before, with other events. It seems that the event handler simply stops working from time to time.

Jan

do you have another code that disables events if it run into error?


Regards
Claus B.
--
Windows10
Office 2016

My code stops working
 

Hi Jan,

Am Thu, 9 Jun 2016 03:01:34 -0700 (PDT) schrieb :

No other code in the workbook. But now that I tink about it, I have experience similar problems before, with other events. It seems that the event handler simply stops working from time to time.

the events can also be disabled from another workbook you worked with.
Copy following code in a module and run it:

Sub EventsOn()
Application.EnableEvents = True
End Sub

If you disable events in your code use an error handler to make sure
that the events are enabled if you run into an error, e.g.:
Sub Test()
Application.EnableEvents = False
On Error GoTo CleanUp
'Your code

CleanUp:
Application.EnableEvents = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016

My code stops working
 

Hi Claus

You solution did the trick, so I must have disabled events somehow. I dont know how though, as I only had one workbook open, and it worked initially.

Jan

But thaky your for your effort.

Den torsdag den 9. juni 2016 kl. 12.16.04 UTC+2 skrev Claus Busch:
Hi Jan,

Am Thu, 9 Jun 2016 03:01:34 -0700 (PDT) schrieb
No other code in the workbook. But now that I tink about it, I have experience similar problems before, with other events. It seems that the event handler simply stops working from time to time.

the events can also be disabled from another workbook you worked with.
Copy following code in a module and run it:

Sub EventsOn()
Application.EnableEvents = True
End Sub

If you disable events in your code use an error handler to make sure
that the events are enabled if you run into an error, e.g.:
Sub Test()
Application.EnableEvents = False
On Error GoTo CleanUp
'Your code

CleanUp:
Application.EnableEvents = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016

My code stops working
 

This is why I use my EnableFastCode component. It prevents other code
from toggling settings while already in use. Doesn't work across
projects, though, so your advice here is just good practice. I include
this with other UI stuff in a module named "mWorkspace" so it just
drops into projects. I prefer to copy it into mOpenClose so it
initializes the enum at startup!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus