| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to hide a column once any cell of that col. is not active any
I have a macro, that enables me to unhide a column, should a certain code
being entered in the column before. (there is probably a simpler way for it, but I am afraid I am pretty new to Visual Basics): 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 What I would like to do now is after the certain code from the column before has unhidden the column, to be able to make an entry, but when you go to the next cell in the next column, that the one where the entry was made automatically gets hidden again. I hope you understand what I mean. Any help would be very much appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to hide a column once any cell of that col. is not active any
You would use the selectionchange event for this
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 6 then exit sub Columns("G").Hidden = True End Sub This would require the user to immediately make the entry after your change event unhides the column. -- Regards, Tom Ogilvy "Frederic" wrote in message ... I have a macro, that enables me to unhide a column, should a certain code being entered in the column before. (there is probably a simpler way for it, but I am afraid I am pretty new to Visual Basics): 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 What I would like to do now is after the certain code from the column before has unhidden the column, to be able to make an entry, but when you go to the next cell in the next column, that the one where the entry was made automatically gets hidden again. I hope you understand what I mean. Any help would be very much appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get row and column of active cell to highlight? | Excel Discussion (Misc queries) | |||
| How to extract just the Column Name of the active cell | Excel Discussion (Misc queries) | |||
| How to locate the Row and Column of an Active Cell | Excel Discussion (Misc queries) | |||
| Returning Column of active cell | Excel Programming | |||
| copying the column of the active cell to another column | Excel Programming | |||
Linear Mode
