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

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

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

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

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

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