how can hide and show columns using macro?
 

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

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


"Hoshyar" wrote in message
...
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

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


"Bernie Deitrick" wrote:

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


"Hoshyar" wrote in message
...
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

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

"Hoshyar" wrote in message
...
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


"Bernie Deitrick" wrote:

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


"Hoshyar" wrote in message
...
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

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




"Bernie Deitrick" wrote:

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

"Hoshyar" wrote in message
...
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


"Bernie Deitrick" wrote:

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


"Hoshyar" wrote in message
...
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