Re: how can hide and show columns using macro?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Roman,

Thanks again.
this was a good thing, but still I havent achieved my target. I am sure you
can help, please bear with me.

When I am typing AB in column A, the columns C:E are shown which is fine.
but when I am typing another AB right under the first AB these columns are
hidden again. The aim is to have columns C:E open whenever I type AB in
clumun A. If I type anything other than AB in clumun A, Columns C:E are to be
hidden. and so on.

Thanks
Hoshyar



"Norman Jones" wrote:

> Hi Hoshyar,
>
> Rowan's code is now:
>
> '=====================================>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrorHandler
> Application.EnableEvents = False
> If Target.Column = 1 And Target.Count = 1 Then
> If Target.Value = "AB" Then
> Columns("C:E").Hidden = Not Columns("C:E").Hidden
> End If
> End If
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
> '=====================================>>
>
> This is worksheet event code and should be pasted into the worksheets's code
> module (not a standard module and not the workbook's ThisWorkbook module):
>
> ************************************************************
> (1) Right-click the work***'s tab
>
> (2) Select 'View Code' from the menu and paste the code.
>
> (3) Alt-F11 to return to Excel.
> ************************************************************
>
>
> ---
> Regards,
> Norman
>
>
>
> "Hoshyar" <Hoshyar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A5B4084B-457B-4E7E-BC8D-6D0581102127@xxxxxxxxxxxxxxxx
> > Hi Rowan and Norman,
> >
> > Your help is much appreciated. However, I am not a programmer, I am an
> > accountant, I therefore appreciate I can get contact with one of you by
> > email
> > and would like to send you my spread *** to handle this.
> > I am aware that macro doesnt work while editing a cell. What I mean is
> > when
> > I type AB in column "A" and then press enter Column B and C should open.
> > When
> > Column B and C are open I would like to to add information in them. then
> > after moving to column E, I would like column B and C be hidden again.
> >
> > I hope this can work.
> > Many thanks.
> > My email is hrassam@xxxxxxxxxxx
> >
> > "Rowan" wrote:
> >
> >> Thanks Norman
> >>
> >> I knew there was a better way but my brain stopped working and totally
> >> forgot about "Not" in relation to boolean values. <g>
> >>
> >> Regards
> >> Rowan
> >>
> >> "Norman Jones" wrote:
> >>
> >> > Hi Rowan,
> >> >
> >> > > If Columns("C:E").Hidden Then
> >> > > Columns("C:E").Hidden = False
> >> > > Else
> >> > > Columns("C:E").Hidden = True
> >> > > End If
> >> >
> >> > could also be expressed as:
> >> >
> >> > Columns("C:E").Hidden = Not Columns("C:E").Hidden
> >> >
> >> > ---
> >> > Regards,
> >> > Norman
> >> >
> >> >
> >> >
> >> > "Rowan" <rowanzsa at hotmailNOSPAM dot com> wrote in message
> >> > news:94FB7B02-700C-4E95-963B-D854A17DA6C7@xxxxxxxxxxxxxxxx
> >> > > Hi Hoshyar
> >> > >
> >> > > It is not possible to undide and then hide the column while you are
> >> > > editing
> >> > > the cell. However, if you are wanting to toggle the unhide/hide each
> >> > > time
> >> > > you
> >> > > type AB in column A you can achieve this with a change event macro:
> >> > >
> >> > > Private Sub Worksheet_Change(ByVal Target As Range)
> >> > > On Error GoTo ErrorHandler
> >> > > Application.EnableEvents = False
> >> > > If Target.Column = 1 And Target.Count = 1 Then
> >> > > If Target.Value = "AB" Then
> >> > > If Columns("C:E").Hidden Then
> >> > > Columns("C:E").Hidden = False
> >> > > Else
> >> > > Columns("C:E").Hidden = True
> >> > > End If
> >> > > End If
> >> > > End If
> >> > > ErrorHandler:
> >> > > Application.EnableEvents = True
> >> > > End Sub
> >> > >
> >> > > This is work*** event code. Right click the *** tab, select view
> >> > > code
> >> > > and paste the code in there.
> >> > >
> >> > > As Tom has said this will not run while you are editing the cell but
> >> > > rather
> >> > > once the change is made. So each time you type AB in column A,
> >> > > columns C:E
> >> > > will be hidden/unhidden.
> >> > >
> >> > > Hope this helps
> >> > > Rowan
> >> > >
> >> > > "Hoshyar" wrote:
> >> > >
> >> > >> Many thanks for you answer Tom. But isn't it possible to unhide the
> >> > >> hidden
> >> > >> columns by typing a word in another column?
> >> > >> In fact, once I did it with help of a freind, but I lost this
> >> > >> workbook,
> >> > >> and
> >> > >> now I am trying to do it again.
> >> > >>
> >> > >> your answer is appreciated
> >> > >> Hoshyar
> >> > >>
> >> > >>
> >> > >> "Tom Ogilvy" wrote:
> >> > >>
> >> > >> > macros don't run while you are editing a cell, so if you mean hit
> >> > >> > Enter
> >> > >> > to
> >> > >> > exit edit mode, then no.
> >> > >> >
> >> > >> > --
> >> > >> > Regards,
> >> > >> > Tom Ogilvy
> >> > >> >
> >> > >> >
> >> > >> > "Hoshyar" <Hoshyar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> > >> > news:C379342E-DEA6-47B4-B233-1B6DCBEC7C8C@xxxxxxxxxxxxxxxx
> >> > >> > > How can I unhide hidden columns and then hide them again by
> >> > >> > > macro?
> >> > >> > > suppose
> >> > >> > > that I have a workbook made of 10 columns. colum 3,4 and 5 are
> >> > >> > > hidden
> >> > >> > > by
> >> > >> > > default. I want to unhide these hidden columns when I type word
> >> > >> > > "AB"
> >> > >> > > in
> >> > >> > any
> >> > >> > > cell in colum Number 1. and then by pressing enter I want to
> >> > >> > > hide
> >> > >> > > these
> >> > >> > > columns again. is this possible using macro?
> >> > >> > >
> >> > >> > > Many thanks
> >> > >> > > Hoshyar
> >> > >> >
> >> > >> >
> >> > >> >
> >> >
> >> >
> >> >
>
>
>
.

Quantcast