EXCEL

%3CLINGO-SUB%20id%3D%22lingo-sub-2226728%22%20slang%3D%22en-US%22%3EEXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2226728%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20spread%20sheet%20with%20multiple%20tabs.%26nbsp%3B%20How%20do%20I%20send%20the%20file%20to%20persons%20and%20not%20allow%20them%20to%20see%20all%20of%20the%20columns.%20I%20wish%20to%20hide%20some%20columns%20from%20some%20reciepents%20and%20do%20not%20wish%20them%20to%20have%20access%20to%20all%20of%20the%20information.%26nbsp%3B%20How%20can%20I%20do%20that%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2226728%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2227021%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2227021%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1004237%22%20target%3D%22_blank%22%3E%40DB0321%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20than%20built%20in%20Hide%2FUn-hide%20command%20with%20or%20without%20Password%2C%20the%20best%20way%20is%20using%20the%20VBA%20macro%2C%20because%20it%20automates%20the%20task%20as%20well%20keeps%20the%20end%20user%20away%20from%20handing%20the%20Sheet%2FWorkbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBetter%20use%20Workbook%20Open%20event%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20I'm%20showing%20you%20few%20examples%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThis%20is%20the%20way%20you%20may%20load%20the%20VB%20editor%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EEither%20Press%20Alt%2BF11%20or%20hit%20the%20Sheet%20Tab%2C%20Right%20Click%20the%20from%20the%20menu%20View%20Code.%3C%2FLI%3E%3CLI%3EOn%20left%20find%20the%20Project%20Explore%2C%20hit%20the%20Workbook%20Icon.%3C%2FLI%3E%3CLI%3ECopy%20%26amp%3B%20Paste%20this%20code.%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A%0A%20Columns(%22A%22).Hidden%20%3D%20True%0A%20Columns(%22C%22).Hidden%20%3D%20True%0A%20Columns(%22E%22).Hidden%20%3D%20True%0A%0AEnd%20Sub%0A%0AFor%20range%20of%20columns%20you%20may%20use%3A%0A%0AColumns(%22A%3AE%22).Hidden%20%3D%20True%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSTRONG%3ETo%20Un-hide%20those%20columns%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EUse%20this%20code%20as%20standard%20module%20for%20the%20Sheet.%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Unhide_Column(%20)%0A%0A%20Columns(%22A%22).Hidden%20%3D%20False%20%0A%20Columns(%22C%22).Hidden%20%3D%20False%0A%20Columns(%22E%22).Hidden%20%3D%20False%0A%0AEnd%20Sub%0A%0AFor%20Rannge%20of%20columns%20use%20this%3A%0A%0AColumns(%22A%3AE%22).Hidden%20%3D%20False%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ESave%20the%20Workbook%20as%20Macro%20Enable%20*.xlsm%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
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.