VBA - Worksheet Change not working

pacittm

New Member
Joined
Sep 26, 2014
Messages
5
I am trying to get this code to run a macro based on the number in cell C7.
All of the macros work perfectly, but I can't get the worksheet change to trigger them.
Here's the code -

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Set Target = Range("C7")
   Application.EnableEvents = False
   If Target Is Nothing Then
   Call ROWNULL
   End If
If Not Intersect(Target, Range("C7")) Is Nothing Then
    If Target.Value = 1 Then
    Call ROWONE
    Else
         If Target.Value = 2 Then
          Call ROWTWO
    Else
             If Target.Value = 3 Then
                 Call ROWTHREE
    Else
                 If Target.Value = 4 Then
                  Call ROWFOUR
    Else
                     If Target.Value = 5 Then
                      Call ROWFIVE
    Else
                         If Target.Value = 6 Then
                          Call ROWSIX
    Else
                             If Target.Value = 7 Then
                              Call ROWSEVEN
    Else
                                 If Target.Value = 8 Then
                                  Call ROWEIGHT
    Else
                                    If Target.Value = 9 Then
                                      Call ROWNINE
    Else
                                                                             Call ROWNULL
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    
End Sub

Any help would be appreciated!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Does C7 contain a formula? If so, it won't trigger the Change event - you'd have to use the Worksheet_Calculate event or monitor the input cells to the formula instead.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Perhaps events have become disabled. In the VB Editor, open the Immediate window (Ctrl+g), type this in and press enter:
Code:
Application.EnableEvents = True
then try changing the number in C7 again.

If it still doesn't work, do the users enter the number into the cell directly or using data validation, or a control?
 

pacittm

New Member
Joined
Sep 26, 2014
Messages
5

ADVERTISEMENT

I'm afraid that didn't seem to do anything - I do have that text in my code as well, if that helps?

I'm afraid not - it's just a question of typing in a number.
 

pacittm

New Member
Joined
Sep 26, 2014
Messages
5
Ah never mind - I pasted it into the wrong window - thank you! That works a treat!!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,322
Messages
5,600,951
Members
414,417
Latest member
Nobu

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
Top