EXCEL

Occasional Visitor

I have an excel spread sheet with multiple tabs.  How do I send the file to persons and not allow them to see all of the columns. I wish to hide some columns from some reciepents and do not wish them to have access to all of the information.  How can I do that please?

1 Reply

@DB0321 

 

Other than built in Hide/Un-hide command with or without Password, the best way is using the VBA macro, because it automates the task as well keeps the end user away from handing the Sheet/Workbook.

 

Better use Workbook Open event:

 

Here I'm showing you few examples:

 

This is the way you may load the VB editor:

 

  • Either Press Alt+F11 or hit the Sheet Tab, Right Click the from the menu View Code.
  • On left find the Project Explore, hit the Workbook Icon.
  • Copy & Paste this code.

 

Private Sub Workbook_Open()

 Columns("A").Hidden = True
 Columns("C").Hidden = True
 Columns("E").Hidden = True

End Sub

For range of columns you may use:

Columns("A:E").Hidden = True

Or you may use this also:

Columns("A:E").Select
Selection.EntireColumn.Hidden = True 

 

To Un-hide those columns: 

 

  • Use this code as standard module for the Sheet.

 

Private Sub Unhide_Column( )

 Columns("A").Hidden = False 
 Columns("C").Hidden = False
 Columns("E").Hidden = False

End Sub

For Rannge of columns use this:

Columns("A:E").Hidden = False

Or use this:

Columns("A:E").Select
Selection.EntireColumn.Hidden = False 

 

 

  • Save the Workbook as Macro Enable *.xlsm

N.B. Mark this as Best solution/answer as well Like, if it works for you.