Vba formula not woring

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Hello all!

I am trying to use this code on VBA. However, it is not working. Nothing happens when I change cell A1. Any idea why?

VBA Code:
Private Sub Worksheet_Change2(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect Password:="123"
Application.ScreenUpdating = False
With Sheets("Sheet2")
Select Case Target.Address
Case "$A$1"
Select Case Target.Value
  Case Is = 1
        .Rows("107:323").EntireRow.Hidden = True
        .Rows("36:106").EntireRow.Hidden = False
   Case Is = 2
        .Rows("37:107").EntireRow.Hidden = True
        .Rows("108:177").EntireRow.Hidden = False
        .Rows("178:323").EntireRow.Hidden = True
   Case Is = 3
        .Rows("37:179").EntireRow.Hidden = True
        .Rows("180:250").EntireRow.Hidden = False
        .Rows("178:323").EntireRow.Hidden = False
Case Is = 4,5
        .Rows("37:252").EntireRow.Hidden = True
        .Rows("253:324").EntireRow.Hidden = False
      
    End Select
    End If
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
    End Sub

Thank you!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You cannot change the name of the procedure, it needs to be
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
You cannot change the name of the procedure, it needs to be
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
I've tried without the 2 and it does not work as well
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Ok, a few questions
1) Is the code in the correct sheet module?
2) Do you get any errors?
3) How is A1 being changed?
 

AC PORTA VIA

Board Regular
Joined
Apr 9, 2016
Messages
89
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

need "end select" and "end with"
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="123"
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
With Sheets("Sheet2")
Select Case Target.Address
Case "$A$1"
Select Case Target.Value
Case Is = 1
        .Rows("107:323").EntireRow.Hidden = True
        .Rows("36:106").EntireRow.Hidden = False
   Case Is = 2
        .Rows("37:107").EntireRow.Hidden = True
        .Rows("108:177").EntireRow.Hidden = False
        .Rows("178:323").EntireRow.Hidden = True
   Case Is = 3
        .Rows("37:179").EntireRow.Hidden = True
        .Rows("180:250").EntireRow.Hidden = False
        .Rows("178:323").EntireRow.Hidden = False
Case Is = 4, 5
        .Rows("37:252").EntireRow.Hidden = True
        .Rows("253:324").EntireRow.Hidden = False
      
    End Select
    End Select
    End With
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
    End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you please answer my questions?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
In addition to the questions in post#4 are you changing the value of A1 in sheet2 or the value of A5 in sheet1?
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
In addition to the questions in post#4 are you changing the value of A1 in sheet2 or the value of A5 in sheet1?
The value that will be changing will be A5 in sheet 1. Then, A1 in sheet2 will depend of A15 in sheet1. Thank you!
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Ok, a few questions
1) Is the code in the correct sheet module?
2) Do you get any errors?
3) How is A1 being changed?
1) Yes, in Sheet2
2) No, no errors have appeared
3) A1 in sheet 2 will be the value chosen in the drop down list of cell A5 in sheet1.

Thank you!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

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