ActiveCell Value Hides Columns

[hamistasty]

I want to be able to select (click with my mouse) a cell in column A and depending on that cell's value will carry out a case. I'm completely lost, and I'm not sure if this is how ActiveCell works. ??

Sub FICspecific()
'MAKES BLAH A STRING
Dim blah As String
'IN SCHEDULE TAB
With Sheets("Schedule")
    ' IN COLUMN A AND ALL CELLS THAT ARE NOT BLANK
  For I = 7 To .Range("A" & Rows.Count).End(xlUp).Row ' From row 7 to the Last used row on "Schedule"
          
 ' IF THE ACTIVE CELL IS A FOLLOWING FIC
 Sheets(.Range("A" & I).Value).ActiveCell = blah
               
'SELECT FIC NUMBER
Select Case CInt(Mid(.Range("A" & I).Value, 4, 3))
Case 1
Columns("C").EntireColumn.Hidden = True
Case 2
Columns("D").EntireColumn.Hidden = True
Case 3
Columns("E").EntireColumn.Hidden = True
End Select
End With
End Sub

 

[repairman615]

Hello,

Sounds like you will need to place the code into the sheet module instead of a standard "macro" module.

See if this gets you further to your goal:

right click Schedule sheet tab.
select veiw code.
Try (paste) this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Changed As Range
Set Changed = Intersect(Target, Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row))
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Changed Is Nothing Then
        Select Case CInt(Mid(.Range("A" & I).Value, 4, 3))
           Case 1
           Columns("C").EntireColumn.Hidden = True
           Case 2
           Columns("D").EntireColumn.Hidden = True
           Case 3
           Columns("E").EntireColumn.Hidden = True
       End Select
    End If
    
End Sub

This should run when a cell is selected between cell A7 and the last cell from the bottom up is clicked.

Does that help?

Jeff

 

[hamistasty]

Thanks, that's a big help. I just came back to change that to a selectionchange too.

I get an error "invalid or unqualified reference" with .range in: Select Case CInt(Mid(.Range("A" & I).Value, 4, 3))

It's taking the last 3 letters of "FIC001", "FIC002" etc. to match the case.

I also added "Dim WS As Worksheet, I As Long".

 

[repairman615]

I get an error there too. Why not use the right function instead if always pulling last three numbers?

Right(FIC001, 3)

Should yeild: 001

 

[hamistasty]

I get a type mismatch error with: Select Case CInt(Right(("A" & I), 3))

 

[repairman615]

Not sure how these Columns are to become visible again, try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Changed As Range
Set Changed = Intersect(Target, Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row))
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Changed Is Nothing Then
        Select Case Right(Target, 3)
           Case 1
           Columns("C").EntireColumn.Hidden = True
           Case 2
           Columns("D").EntireColumn.Hidden = True
           Case 3
           Columns("E").EntireColumn.Hidden = True
       End Select
    End If
    
End Sub

 

[hamistasty]

Brilliant that works great. What I'm going to do for each case is hide all columns except a certain group of them.

I would like to know how to code so that if I click off column A that all the columns unhide though.

Thanks!

 

[repairman615]

Your welcome!

Give this a shot:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Changed As Range
Set Changed = Intersect(Target, Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row))
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Changed Is Nothing Then
        Select Case Right(Target, 3)
           Case 1
           Columns("C").EntireColumn.Hidden = True
           Case 2
           Columns("D").EntireColumn.Hidden = True
           Case 3
           Columns("E").EntireColumn.Hidden = True
       End Select
    Else
        Columns("C").EntireColumn.Hidden = False
        Columns("D").EntireColumn.Hidden = False
        Columns("E").EntireColumn.Hidden = False
    End If
    
End Sub

Jeff

 

[hamistasty]

I keep getting a type mismatch error on case 1?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Changed As Range
Dim WS As Worksheet, I As Long
Set Changed = Intersect(Target, Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row))
    If Target.Cells.Count > 1 Then Exit Sub
 
    If Not Changed Is Nothing Then
        Select Case Right(Target, 3)
           Case 1
                     Columns("A:A, B:B, C:C, D:D, E:E, F:F, G:G, H:H, I:I, J:J, K:K, L:L").EntireColumn.Hidden = True
           Case 2
           Columns("D").EntireColumn.Hidden = True
           Case 3
           Columns("E").EntireColumn.Hidden = True
       End Select
 
        Else
        Columns("A:DA").EntireColumn.Hidden = False
    End If
 
End Sub

 

[repairman615]

I tried an Array()... I get a bit confused with that.

Columns("A:L").EntireColumn.Hidden = True