Private Sub asking me to name it before I run

[cmaize]

I had found some code about hiding and unhiding cells based on specific entry in another cell which I have changed to hopefully fit my needs.

My problem is that when I try to run it from the VBA screen it asks me to name it in the Macros Name screen. If I type in the name and click create, it takes back to VBA with just the shell code. If I paste the code into that shell and try to run it, I'm back to the Macro naming screen.

I'm sure I'm doing something stupid, but I've searched on everything I think is logical. Any help would be very appreciated it. Thank you for your help and for all the other help you have unknowingly been providing.

Here is the code I'm using:


Private Sub Worksheet_Change(ByVal Target As Range)
'Hide cells based on entry in Column E, column E is chosen from a drop down list

If WorksheetFunction.CountIf(Columns(5), 1) = 1 Then
Columns("H:O").Hidden = True
If WorksheetFunction.CountIf(Columns(5), 1) = 2 Then
Columns("J:O").Hidden = True
If WorksheetFunction.CountIf(Columns(5), 1) = 3 Then
Columns("L:O").Hidden = True
If WorksheetFunction.CountIf(Columns(5), 1) = 4 Then
Columns("N:O").Hidden = True
If WorksheetFunction.CountIf(Columns(5), 1) = 5 Then
Columns("F:O").Hiddent = False

Else
Columns("H:O").Hidden = True

End Sub

 

[VoG]

Try posting your code.

 

[sykes]

Post deleted by Sykes.

 

[VoG]

Try posting your code.

I see that you have now.

Where did you put the code? you need to right click the sheet tab, select View Code then paste in the code. It will then run automatically when you make changes to the sheet.

 

[cmaize]

Thanks for responding so quickly. I did put it in the worksheet where I want it to run.

 

[VoG]

Try replacing that with

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide cells based on entry in Column E, column E is chosen from a drop down list

If WorksheetFunction.CountIf(Columns(5), 1) = 1 Then
    Columns("H:O").Hidden = True
ElseIf WorksheetFunction.CountIf(Columns(5), 1) = 2 Then
    Columns("J:O").Hidden = True
ElseIf WorksheetFunction.CountIf(Columns(5), 1) = 3 Then
    Columns("L:O").Hidden = True
ElseIf WorksheetFunction.CountIf(Columns(5), 1) = 4 Then
    Columns("N:O").Hidden = True
ElseIf WorksheetFunction.CountIf(Columns(5), 1) = 5 Then
    Columns("F:O").Hiddent = False
Else
    Columns("H:O").Hidden = True
End If
End Sub

 

[sykes]

Hi, and Welcome to Mr Excel (even if you've been using the site for some time!!)

The worksheet_change event will behave like this if you try and run it from the VBA browser.

As Peter said - you need to actually make a change in the sheet in order for the code to fire...........then you'll realise that the code is erroring due to "Block if without end if"

Peter's changed code will do the trick.

 

[cmaize]

I knew it was something very silly. Thank you very much, I do appreciate it.