How to hide a column once any cell of that col. is not active any

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to hide a column and unhide it 3
Code to hide and unhide columns, with certain condition? 2
Code for conditional formula 1
Excel Target.column for Data Validation would like to target some cells within column 1
Help with events 6
Help! Stop Users from corrupting Drop Down Box Macro 4
Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox 0
Changing Cells depending on data entered in other cells 1

Back
Top