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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >>
> >>
> >>
>
>
>