Hide column on selection Change

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Right click the sheet tab, View Code. Copy and paste in

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 2 Then Columns(2).Hidden = True
End Sub
 
Upvote 0
Thanks for the code but it is not working in my desired manner. The code hides column B if the cursor moves from other column to column say column C to column D .

My requirement is that suppose user is entering data in cell b1, then b2 and so on. As soon as cursor moves from say B2 to any other cell say Cell C2, code should hide column B .

Can this be done?
 
Upvote 0
Try this

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Columns(2).Hidden = True
End Sub

This will hide column B when an entry is made in a different column.
 
Upvote 0
I thought Vog's was better, but considering you only want it to hide column B if you've moved FROM Column B. This should work..

The First Line "Dim oldcell" MUST be at the top of the module, as it is a global variable.
This in the SHEET module
Code: 
Dim Oldcell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Oldcell.Column = 2 And ActiveCell.Column <> 2 Then Columns(2).Hidden = True
Set Oldcell = Selection
Application.EnableEvents = True
End Sub

this in the WORKBOOK module
Code: 
Dim Oldcell

Private Sub Workbook_Open()
Set Oldcell = Range("a1")
End Sub
 
Upvote 0
Actually, reconsidering Vog's original suggestion

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
If Target.Column <> 2 Then Columns(2).Hidden = True 
End Sub

this should satisfy your requirement...

The code hides column B if the cursor moves from other column to column say column C to column D

My requirement is that suppose user is entering data in cell b1, then b2 and so on. As soon as cursor moves from say B2 to any other cell say Cell C2, code should hide column B
Click to expand...

If a user is typing in Say column C, doesn't that mean the collumn B is already hidden? Unless you want column B to remain visible upon OPENING the workbook, UNTIL Changing something in Column B.
 
Upvote 0
Dear jonmo1
If a user is typing in Say column C, doesn't that mean the collumn B is already hidden? Unless you want column B to remain visible upon OPENING the workbook, UNTIL Changing something in Column B.
Click to expand...

Your observation is correct but unfortunately no code is working.

I am trying to clarifying my requirement :
Column B should get hide when cursor will move from column B to another column.

I am using another code to hide column B on close of the file as suggested at vbaexpress (link given above) So on opening of WB column B will be hidden. In case user need to enter data in column B he will unhide the column B and the code for same have been made by recording a macro.

Unfortunately, your code is also not working
 
Upvote 0
so column B will already be hidden when opening the file...

and the user will unhide column B (via another macro) to enter data into column B.

Then when user leaves column B, column B should hide.

Vog's first suggestion satisfies that requirement.

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
If Target.Column <> 2 Then Columns(2).Hidden = True 
End Sub


if it's not working, verify you have events enabled.

in the VBA window, click View - Immediate Window
type

?application.enableevents

if this returns FALSE, then events are disabled.
type application.enableevents = true to re-enable them.
 
Upvote 0
You must log in or register to reply here.

Similar threads

David Kemp
  • Solved
Hide different columns depending on value in a cell
Replies
5
Views
165
Peter_SSs
Peter_SSs
D
  • Solved
VBA Conditional Statement That Functions Based on Today's Date
2
Replies
18
Views
183
Cubist
Cubist
D
  • Question
Copy the value of a cell onto another worksheet based on the dropdown selection
Replies
4
Views
149
mumps
mumps
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
120
Lil Stinker
L
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
347
mumps
mumps

Forum statistics

Threads
1,215,636
Messages
6,125,961
Members
449,276
Latest member
surendra75

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top