| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to hide a column and unhide it
I have the following code to enable a hidden column(G) to be unhidden, should
a certain subcategory in a previous column(E) be chosen. I took that from an online example. However what I am trying to achieve without success is the following: I would like column(G) automatically to be hidden again, once the next cell in the next column has been moved to. Any help would be much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 And Target.Value = "Locators" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Detectors" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Survey/Navigation Equip." Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Machinery" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Medical Equip. Cap. Assets" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Weapons" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Desktops" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Laptops" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Printers" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Scanners" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Digital Cameras" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Radios" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Sat Phones" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Mobile Phones" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Vehicles" Then Columns("G").Hidden = False End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA to hide a column and unhide it
Hiding and unhiding columns is not a big deal but I really can't
understand what you mean by 'next cell in the next column has been moved to'. Try to redefine your question. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA to hide a column and unhide it
Hi Frederic,
unfortunately I did not get the enclosement / probably because of the macros in the workbook. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA to hide a column and unhide it
The code below should do it. Some warnings
I have only included the first four cases. Yoiu should include the others. If by entering the value, you move on to the next cell then this will not work. For this reason, you should put data validation on Column E so that the user selects from a valid set by choosing the value in the dropdown and not moving to the next cell. Private NewSelect As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 Then Select Case Target Case "Locators", "Detectors", "Survey/Navigation Equip.", "Machinery" NewSelect = True Me.Columns("G").Hidden = False Case Else Me.Columns("G").Hidden = True End Select End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If NewSelect Then Me.Columns("G").Hidden = True End If NewSelect = False End Sub "Frederic" wrote: I have the following code to enable a hidden column(G) to be unhidden, should a certain subcategory in a previous column(E) be chosen. I took that from an online example. However what I am trying to achieve without success is the following: I would like column(G) automatically to be hidden again, once the next cell in the next column has been moved to. Any help would be much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 And Target.Value = "Locators" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Detectors" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Survey/Navigation Equip." Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Machinery" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Medical Equip. Cap. Assets" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Weapons" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Desktops" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Laptops" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Printers" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Scanners" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Digital Cameras" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Radios" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Sat Phones" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Mobile Phones" Then Columns("G").Hidden = False ElseIf Target.Column = 5 And Target.Value = "Vehicles" Then Columns("G").Hidden = False End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hide, unhide column or row after protect worksheet | Excel Discussion (Misc queries) | |||
| How do I set up a column tool to hide/unhide? | Excel Discussion (Misc queries) | |||
| Check Box Macro to hide and unhide a column | Excel Worksheet Functions | |||
| Hide / unhide column in VBA | Excel Programming | |||
| Macro that will always hide / unhide next column | Excel Programming | |||
Hybrid Mode
