+ Reply to Thread
Results 1 to 14 of 14

Hide formulas

  1. #1
    Registered User
    Join Date
    12-06-2011
    Location
    Oslo
    MS-Off Ver
    Excel 2003
    Posts
    66

    Hide formulas

    How can I hide all the formula's that I have used in my workbook using a macro?

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,796

    Re: Hide formulas

    Hi,

    if you right click the cells with the formulae, choose Format cells, in the protection window check Hidden: when you set Protection on your sheet, formulae are not visible anymore.


    I don't know VBA, but I've found some instructions here:

    http://www.teachexcel.com/free-excel...-deletion.html

    Regards

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Hide formulas

    This will lock & hide all formulas in each shet, then add sheet protection

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Hide formulas

    If you are not going to reuse the workbook but just distributing it for information purposes you could just copy and then paste back as value.

    Please Login or Register  to view this content.
    Alf

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Hide formulas

    If you want to change cells to values you don't need to activate the sheets

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Hide formulas

    Nice and short!

    Thanks Roy

    Alf

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Hide formulas

    Tested, did not work so I had to make a small change

    Please Login or Register  to view this content.
    Alf

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Hide formulas

    This works
    Please Login or Register  to view this content.

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Hide formulas

    Hello Roy,

    Some real nifty short and sweet good coding you have posted here. I Like !

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Hide formulas

    Why use "Select"?

    This should work as well I think.

    Please Login or Register  to view this content.
    Alf

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Hide formulas

    Alf, look at the code. It selects the worksheets to Group them then selects all the cells on one go before converting. Your coe will only work on one sheet

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Hide formulas

    Hello Roy,

    Could one perhaps add to this one line of your code to include preserving formats as well?

    Please Login or Register  to view this content.

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,593

    Re: Hide formulas

    Ah, I missed that. Very elegant Roy!

    Alf

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Hide formulas

    Quote Originally Posted by Winon View Post
    Hello Roy,

    Could one perhaps add to this one line of your code to include preserving formats as well?

    Please Login or Register  to view this content.
    Why not try?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1