Selection_Change question

[MOB]

I'm using this;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("C3").Value = 28 Then
        Columns("AK:AM").EntireColumn.Hidden = True
    Else
   If Range("C3").Value = 29 Then
        Columns("AL:AM").EntireColumn.Hidden = True
    Else
   If Range("C3").Value = 30 Then
        Columns("AM").EntireColumn.Hidden = True
   Else
        Columns("AK:AM").EntireColumn.Hidden = False
    End If
     End If
      End If
End Sub

Feel free to improve it lol..............

Anyway, my question is this - cell C3 contains a number (28-31) and the code hides columns based on this value.

However C3 is a formula driven by a data validation cell in cell C2 - so if the user changes the value in C2, the columns will not hide until you click on C3

Is there a simple fix for this?

 

[pgc01]

If I understand correctly you should use the Change Event and run your code when C2 changes value.

 

[MOB]

Sorry..........as a VB numptie, how do I do that?

 

[pgc01]

Instead of a Selection Change event use a (Value) Change event.

Replace your code with:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address <> "$C$2" Then Exit Sub
    
    Select Case Range("C3").Value
        Case 28
            Columns("AK:AM").Hidden = True
    
        Case 29
            Columns("AM").Hidden = True
    
        Case 30
            Columns("AK:AM").Hidden = False
    End Select
End Sub

 

[mikerickson]

On Error Resume Next
Set dependentCells = Target.Dependents
On Error Goto 0
If Not dependentCells is Nothing Then
    If dependentCells.Address = "$C$3" Then
        'Hide stuff
    End If
End If

 

[MOB]

Instead of a Selection Change event use a (Value) Change event.

Replace your code with:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address <> "$C$2" Then Exit Sub
    
    Select Case Range("C3").Value
        Case 28
            Columns("AK:AM").Hidden = True
    
        Case 29
            Columns("AM").Hidden = True
    
        Case 30
            Columns("AK:AM").Hidden = False
    End Select
End Sub

I get "Compile Error - Ambiguous name detected: Worksheet_Change"

Edit: I have another Worksheet_Change in the same workbook - is that the problem?

 

[lenze]

Try this,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$3" Then Exit Sub
Select Case Target
        Case 28 : Columns("AK:AM").EntireColumn.Hidden = True
        Case 29 : Columns("AL:AM").EntireColumn.Hidden = True
        Case 30 : Columns("AM").EntireColumn.Hidden = True
        Case Else : Columns("AK:AM").EntireColumn.Hidden = False     
End Select
End Sub

lenze

 

[Colin Legg]

I get "Compile Error - Ambiguous name detected: Worksheet_Change"

Edit: I have another Worksheet_Change in the same workbook - is that the problem?

You can only have one Worksheet Change event handler per worksheet so you will need to combine the code in the two procedures together.

 

[lenze]

What is your other Change Event? Like Colin says, you can combine them ito one.
lenze

 

[pgc01]

Edit: I have another Worksheet_Change in the same workbook - is that the problem?

Yes, there can be only one. You have to merge the 2, like

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' In case cell C2 changed value, the code I posted
    If Target.Address = "$C$2" Then
    
        Select Case Range("C3").Value
            Case 28
                Columns("AK:AM").Hidden = True
        
            Case 29
                Columns("AM").Hidden = True
        
            Case 30
                Columns("AK:AM").Hidden = False
        End Select
    Else
    
        ' for other changes, your other code
    
    End If
End Sub

Lenze: The code should run when C2 changes, but you will hide the columns depending on the values of C3.