Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show Formula / Hide Formula | Excel Discussion (Misc queries) | |||
Hide and show Rows | Excel Discussion (Misc queries) | |||
Hide Cell Content if no data in previous columns | Excel Discussion (Misc queries) | |||
pivot table - hide details but show subtotal for calculated field | Excel Discussion (Misc queries) | |||
hide column but show chart | Charts and Charting in Excel |