how can hide and show columns using macro?

H

Hoshyar

I have a workbook of 10 columns. three columns 3,4 and five are hidden by
default. I want to unhide these columuns when I write a defined letter (e.g.
AB) in any cell in column one. Then I want to fill some information these
columns, then after pressing enter I want these columns to be hidden again.
is that possible with macro?

many thanks in advance
Hoshyar
 
B

Bernie Deitrick

Hashyar,

Copy the code below, right-click the sheet tab of interest, select "View
Code" and paste in the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Target.Column = 1 And Target.Value = "AB" Then
Columns("C:E").EntireColumn.Hidden = False
End If

If Not Intersect(Target, Range("C:E")) Is Nothing Then
Columns("C:E").EntireColumn.Hidden = True
End If

End Sub
 
H

Hoshyar

Hi Bernie,
Many many thanks. it worked perfectly except for one thing. When columns C:E
are open, I want them open until I move our from Column E. In other words,
when I am in cloumn C and typing something then moving to Column D they are
all hidden. What I want to acheive here is to have them open until I move to
Column F.

One more thing, the words "AB" is a code, and I have five other codes with
which I want the same function. Do you think what I am trying to achieve is
possible?

Thanks once again for your help
Hoshyar
 
B

Bernie Deitrick

Hashyar,

Copy the 2 event codes below, right-click the sheet tab of interest,
select "View Code" and paste in the window that appears.

Note that the codes are case sensitive - you could change:
InStr(1, "AB CD EF GH IJ KL MN", Target.Value) > 0 And _
to
InStr(1, "AB CD EF GH IJ KL MN", UCase(Target.Value)) > 0 And _
to be able to enter codes of any case.

Also, as writtten, you must select a cell in column F or beyond to get
C, D, and E to hide again.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 And _
InStr(1, "AB CD EF GH IJ KL MN", Target.Value) > 0 And _
Len(Target.Value) = 2 Then
Columns("C:E").EntireColumn.Hidden = False
Cells(Target.Row, 3).Select
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 6 Then Exit Sub
Columns("C:E").EntireColumn.Hidden = True
End Sub
 
H

Hoshyar

Hi Bernie,

Thanks for your care, In fact I achieved what I was after. your inputs and
Noramns inputs were of great help. I got exactly what I needed.

Best regards
Hoshyar
 
Top