| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can hide and show columns using macro?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how can hide and show columns using macro?
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how can hide and show columns using macro?
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how can hide and show columns using macro?
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 worksheet event code. Right click the sheet 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how can hide and show columns using macro?
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 ... 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 worksheet event code. Right click the sheet 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how can hide and show columns using macro?
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 ... 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 worksheet event code. Right click the sheet 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to hide/show rows and columns | Excel Discussion (Misc queries) | |||
| how can hide and show columns using macro? | Excel Worksheet Functions | |||
| Show / Hide Columns in multiple sheets | Excel Programming | |||
| Show/Hide Columns | Excel Programming | |||
| Macro to Hide/Show Columns based on control cell value | Excel Programming | |||
Hybrid Mode
