Results 1 to 9 of 9

Thread: Excel macro - sheet navigation & hidden sheets

  1. #1
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Question 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.
    One can never stop saying Thank You

  2. #2
    Senior Member MaddAussie's Avatar
    Join Date
    Dec 2006
    Location
    Deepest Darkest Dorset
    Posts
    1,708
    Thanks
    628
    Thanked
    297 times in 179 posts
    • MaddAussie's system
      • Motherboard:
      • Asus ROG Strix Z370G
      • CPU:
      • i7 8700k (5.1Ghz)
      • Memory:
      • 16Gb
      • Storage:
      • 500G 960 EVO NMVE
      • Graphics card(s):
      • GTX 1070
      • PSU:
      • Corsair RM650i
      • Case:
      • Corsair Carbide A1r 240
      • Operating System:
      • Win 10
      • Monitor(s):
      • 24" Dell HD + Samsung HD

    Re: Excel macro - sheet navigation & hidden sheets

    Use VBA?

  3. #3
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,168
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    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......
    throw new ArgumentException (String, String, Exception)

  4. #4
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Excel macro - sheet navigation & hidden sheets

    hey, it wasn't my idea



    I just need to make it happen
    One can never stop saying Thank You

  5. #5
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,176
    Thanks
    3,121
    Thanked
    3,173 times in 1,922 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    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



    You can have all your pivots and charts that run in macro's hidden on tabs that have shortcuts from the one visible tab.

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  6. Received thanks from:

    fuddam (28-07-2011)

  7. #6
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    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?
    One can never stop saying Thank You

  8. #7
    Senior Member mikemikemi's Avatar
    Join Date
    Jan 2010
    Posts
    628
    Thanks
    20
    Thanked
    48 times in 48 posts

    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

  9. Received thanks from:

    fuddam (28-07-2011)

  10. #8
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,168
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    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!
    throw new ArgumentException (String, String, Exception)

  11. #9
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,176
    Thanks
    3,121
    Thanked
    3,173 times in 1,922 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    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.

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 30-07-2007, 10:07 PM
  2. System Perfomance help
    By danjohadley in forum Help! Quick Relief From Tech Headaches
    Replies: 26
    Last Post: 24-06-2006, 09:40 PM
  3. Excel Sheet Rescue
    By Matt1eD in forum Software
    Replies: 0
    Last Post: 17-02-2006, 10:32 AM
  4. Replies: 2
    Last Post: 01-07-2005, 02:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •