Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My code stops working
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
My code stops working
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code stops working after X rows. | Excel Programming | |||
Code runs for a while, then stops working | Excel Programming | |||
VB Code stops working | Excel Programming | |||
After sharing workbook VB code stops working. Runtime Error 1004 | Excel Programming | |||
Create button code stops working | Excel Programming |