VBA to hide a column and unhide it

[Guest]

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

 

[Roman]

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.

 

[Guest]

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

 

[Roman]

Hi Frederic,
unfortunately I did not get the enclosement / probably because of the
macros in the workbook.