Excel macro - sheet navigation & hidden sheets
I need to create a macro that moves the user to a specific sheet depending on the value they enter into a specific cell.
However, when the file is sent to a client, the client must not be able to view worksheets containing calculations - these must remain hidden.
Do you know a way of hiding them that will still allow the macro to work?
Any advice appreciated.
Re: Excel macro - sheet navigation & hidden sheets
Re: Excel macro - sheet navigation & hidden sheets
erm think about what your saying logically.
You want to send un-encrypted data to a client, which you then don't want them to read.
Sorry if this is harsh sounding, but your DPO should come down hard on you for this incredibly stupid idea.
A bit of critical thinking will save people from having their data breached......
Re: Excel macro - sheet navigation & hidden sheets
hey, it wasn't my idea
:D
I just need to make it happen
Re: Excel macro - sheet navigation & hidden sheets
Click the Globe
Excel options (at the bottom)
Advanced (on the left)
scroll down until you see Show Sheet Tabs and unclick it
http://s4.postimage.org/2zbpu4544/Show_Sheet_Tabs.jpg
You can have all your pivots and charts that run in macro's hidden on tabs that have shortcuts from the one visible tab.
Re: Excel macro - sheet navigation & hidden sheets
let's modify it (while maintaining the security liability, lol:
Only main sheet is visible.
On entering a number into the prescribed cell, one is taken to one of the hidden sheets, and only that sheet. All other sheets remain inaccessible.
We could control which values are allowed by a simple dropdown, to prevent access to sheets which must remain off the grid.
ideas for the code?
Re: Excel macro - sheet navigation & hidden sheets
Something like this? Obviously add .visible properties to make hidden sheets visible.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) = "A1" Then
Select Case Target.Value
Case 2
Worksheets("Sheet2").Activate
Case 3
Worksheets("Sheet3").Activate
End Select
End If
End Sub
Re: Excel macro - sheet navigation & hidden sheets
This is truely terrible..... Sometimes educating the client is an important and legal obligation, if someone asks me to help them break the law I'll try and explain to them that's the case or walk away if they fail to listen.
obviously if there is no issue with them having all the data and its more a strange usability issue then fine, but remember half the disastrous cockups that we see are because people decided to absolve themselves with I'm only following orders logic!
Re: Excel macro - sheet navigation & hidden sheets
Animus is right....
even passworded you'l leave yourself open to a mountain of cack when someone better at excel than you opens it all up.