Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
code stops working after X rows. colwyn[_8_] Excel Programming 10 November 25th 08 06:26 PM
Code runs for a while, then stops working Andrew[_56_] Excel Programming 2 October 18th 07 02:36 PM
VB Code stops working Michelle K Excel Programming 17 August 17th 07 05:58 PM
After sharing workbook VB code stops working. Runtime Error 1004 Patrick LaFerriere Excel Programming 0 October 5th 05 04:01 PM
Create button code stops working Stuart[_21_] Excel Programming 5 May 1st 05 09:18 PM


All times are GMT +1. The time now is 01:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"