Solved: Hide column on selection change

[sujittalukde]

I have recorded a macro to hide and unide a column but the problem is that it has to be hidden manualy by running the macro. whereas I want to hide the column when the cursor moves from column B

I have made a form where column B is for information purpose only and is kept hidden and unhidden by the user if they need the same and put entries there however this has be kept hidden finally, which many users forget to do it. So i need a macro which will unhide column B when the user change cursor from column B and if the user closes the excel from column B then before close column B should be hidden.

THe code I am using as below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("B:B").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select
End Sub

[lucas]

Could you let them put thier entries in column B using the userform?

[sujittalukde]

Thanks for the reply, lucas.
Actually I dont have any idea or cant use user forms nicely. So if this can be done in the excel sheet, it would be easier for me handle the code . As in the file other macros are also present, I can use macro code cleanly.The form I have made is a excel sheet only and does not contain any user forms.

[lucas]

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100" '<== change to suit
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
End If
End Sub[/vba]
Put this in the code module for the sheet you want it to work on.

[lucas]

I hope I understand your need. If you type and hit enter in any cell that is not in column B then column B is hidden...

If you close the workbook then column B is hidden and the workbook is saved.

Code in sheet 1 and thisworkbook module

Let me know if this helps and if you understand. See attached file

[sujittalukde]

Thanks lucas, you understand my problem in the right direction but one modification is required. Actually, column B should hide on entering a data in column B cell and on moving to another column.
Suppose say user has entered a value at cell A1, column B should not be hidden (Your code is hiding column B now)
Suppose user has entered a value at cell B1 and moved to cell C1,then column B should get hide. As long as user stay on column B, Column B should not hide ie say if user move from cell B1 to B2, col B should not hide.

Before close hiding column B is well made.

[sujittalukde]

Bump!!!!!!!!!!!!

[sujittalukde]

Posted at mrexcel for help at there.
http://www.mrexcel.com/board2/viewto...363854#1363854

[lucas]

Suppose say user has entered a value at cell A1, column B should not be hidden (Your code is hiding column B now)
Suppose user has entered a value at cell B1 and moved to cell C1,then column B should get hide. As long as user stay on column B, Column B should not hide ie say if user move from cell B1 to B2, col B should not hide.

I'm not sure this is possible. Maybe someone else has an idea.

[sujittalukde]

As I was not getting any reply so I thought no one is looking to my problem. Anyway thanks for the effort and help that you have provided.

[Charlize]

This one ?[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:B100" '<== change to suit
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column > 2 And Columns("B:B").Hidden = False Then
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
End If
End Sub[/vba]

[sujittalukde]

Thanks Charlize, but the code is not working.

[rory]

It's not clear to me exactly what you want. Do you want column B hidden unless a cell in columns A or B is selected? If so, you can use:
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("B").Hidden = (Intersect(Target, Range("A:B")) Is Nothing)
End Sub
[/VBA]
Regards,
Rory

[lucas]

Thanks Charlize, but the code is not working.

Not working doesn't help Charlize to help you.....what does it do or not do that you want from it.....

[sujittalukde]

Thanks Rory, a slight modification in your code has worked for me I have changed Range A:B to Range B:C. Its all right now. Thanks to lucas and Charlize also for their support.