surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Hi

I have the following code on Sheet3 of my workbook. Unfortunately, when C16 is either left blank, or "standard" is inserted, nothing happens - i.e. it does not hide rows or columns.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = True
    
    
       ActiveSheet.Unprotect
    If Range("C16").Value = "" Then
        Columns("O:P").EntireColumn.Hidden = True
     Rows("18").EntireRow.Hidden = False
        Rows("19:20").EntireRow.Hidden = True
        
          ActiveSheet.Unprotect
    If Range("C16").Value = "Standard" Then
        Columns("O:P").EntireColumn.Hidden = True
     Rows("18").EntireRow.Hidden = False
        Rows("19:20").EntireRow.Hidden = True

End Sub
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Captain_Conman

Board Regular
Joined
Jun 14, 2018
Messages
54
Have you tried changing the range to .Text instead of .Value?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    Application.EnableEvents = True
    
    
       ActiveSheet.Unprotect
    If Range("C16").Text = "" Then
        Columns("O:P").EntireColumn.Hidden = True
     Rows("18").EntireRow.Hidden = False
        Rows("19:20").EntireRow.Hidden = True
        
          ActiveSheet.Unprotect
    If Range("C16").Text = "Standard" Then
        Columns("O:P").EntireColumn.Hidden = True
     Rows("18").EntireRow.Hidden = False
        Rows("19:20").EntireRow.Hidden = True


End Sub
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Unfortunately, no that hasn't helped. Also realise the above does not include End If statements, I have added these, and still doesn't work :9

Have you tried changing the range to .Text instead of .Value?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    Application.EnableEvents = True
    
    
       ActiveSheet.Unprotect
    If Range("C16").Text = "" Then
        Columns("O:P").EntireColumn.Hidden = True
     Rows("18").EntireRow.Hidden = False
        Rows("19:20").EntireRow.Hidden = True
        
          ActiveSheet.Unprotect
    If Range("C16").Text = "Standard" Then
        Columns("O:P").EntireColumn.Hidden = True
     Rows("18").EntireRow.Hidden = False
        Rows("19:20").EntireRow.Hidden = True


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,521
Office Version
  1. 365
Platform
  1. Windows
Try running this & then try changing cells
Code:
Sub Chk()
Application.EnableEvents = True
End Sub
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361

ADVERTISEMENT

Where does this go please? On my Sheet3 or ThisWorkbook?

Also, does my cde need to go onto Sheet3 or ThisWorkbook?

Try running this & then try changing cells
Code:
Sub Chk()
Application.EnableEvents = True
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,521
Office Version
  1. 365
Platform
  1. Windows
Your code needs to go in the sheet module for the sheet you want it to work on.
My code can go anywhere
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361

ADVERTISEMENT

Hi

I have tried this, and run your macro, then saved workbook, but (my) above code, on sheet I want to hide rows/columns on still does not work. I have tried code in a blank workbook and it works - so unsure as to why?

Your code needs to go in the sheet module for the sheet you want it to work on.
My code can go anywhere
 
Last edited:

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Ignore please - just realised when I put this into a new workbook, it is all on different sheet numbers, so I was putting it in sheet3 which used to be my main screen, and is now sheet 1 so works perfectly. Apologies!!

Hi

I have tried this, and run your macro, then saved workbook, but (my) above code, on sheet I want to hide rows/columns on still does not work. I have tried code in a blank workbook and it works - so unsure as to why?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,521
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,315
Messages
5,527,957
Members
409,794
Latest member
ajithppajith

This Week's Hot Topics

Top