Worksheet_Change Event Not Firing

C

ChronicFear

New Member
Joined
Aug 9, 2006
Messages
40
Hello All,

I wrote a simple script to show/hide certain rows based on the value of a certain cell on my worksheet (cell value chosen by drop down). When I left work last night, everything worked fine. When I returned this morning, the change event no longer appears to be firing. I'm quite certain no one else accessed the file to change the coding, so my only guesses are 1)perhaps some sort of system update was applied in the middle of the night and it messed with something or 2) aliens have blocked our technology in advance of their invasion.

Can anyone suggest possible reasons that this might suddenly stop working?

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

    If Target.Address = "F1" Then

        Application.EnableEvents = False
        
        Select Case Target
            Case "150"
                Rows("13").EntireRow.Hidden = False
                Rows("14:19").EntireRow.Hidden = True
            Case "330"
                Rows("14").EntireRow.Hidden = False
                Rows("13").EntireRow.Hidden = True
                Rows("15:19").EntireRow.Hidden = True
            Case "340"
                Rows("15:19").EntireRow.Hidden = False
                Rows("13:14").EntireRow.Hidden = True
            Case Else
                Rows("13:19").EntireRow.Hidden = True
        End Select
        
        Application.EnableEvents = True
        
     End If
        
End Sub

Thanks,
CF
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Is your macro security set to High?

I've found in the past that some IS updates have reset user settings, including security levels.

Hope that helps,

Smitty
 
Upvote 0
Perhaps at some point events were turned off, though would be unusual.

To turn them back on:

1 Goto the immediate window (CTRL+G)

2 Type Application.EnableEvents = True and hit Enter.
 
Upvote 0
thanks for the quick response.

Sorry, I should have specified that I already checked my macro settings and they are still at Low.

I tried turning application.events back on, but am still having problems.

Any other ideas?
 
Upvote 0
Target address will return an absolute reference. eg $F$1

It also might be an idea to lose On Error Resume Next, that could be hiding errors making you think the code isn't being triggered.
 
Upvote 0
Good idea on keeping errors in! Haha. I forgot all about that.

I removed that line and tried the code again. It stopped on

Code: 
Rows("13").EntireRow.Hidden = False

and told me that it couldnt set the hidden properties. I commented out that line, reset, and tried running it again but now its totally unresponsive again. I put the troubled line back in and its not tripping the same error.

Weird, huh?
 
Upvote 0
CF

Well if there was an error it could be that events weren't turned back on again since the code never reached the line to do so.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
194
John_w
J
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
421
Peter_SSs
Peter_SSs
N
  • Question
ActiveX Checkbox fails after sheetname change
Replies
4
Views
186
bobsan42
bobsan42
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
1K
dmt32
D
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
596
kevin9999
K

Forum statistics

Threads
1,216,058
Messages
6,128,538
Members
449,456
Latest member
SammMcCandless

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top