Solved hide formula with password

Microsoft Windows xp inside out, second...
February 24, 2010 at 20:37:53
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
I have following code to hide and protect formula but the formula can be unhide(unprotect) through tools menu.

 
Sub Worksheet_SelectionChange(ByVal 
Target As Range)
   Dim rng As Range
   For Each rng In Target.Cells
      If rng.HasFormula Then
         ActiveSheet.Protect
         Exit Sub
      Else
         ActiveSheet.Unprotect
      End If
   Next rng
End Sub

how to make this code to prompt for password if someone try to unprotect the formula?


See More: hide formula with password

Report •

✔ Best Answer
February 26, 2010 at 04:45:28
Hi,

The approach you are taking seems quite complex if all you want to do is protect cells containing formulas.

Run a macro that identifies all the cells with formulas on the worksheet in question. If the cell contains a formula - lock the cell, if it doesn't contain a formula - unlock the cell. Then as a final step Protect (with a password) the whole worksheet.

As DerbyDad03 notes, you will have to protect the VB code so that the password is not visible, or else do the worksheet protection with password manually after running the macro to lock or unlock individual cells.

In either case users will be presented with a worksheet where they cannot change cells containing formulas and they will not be able to turn off the protection, as it requires a password.

Regards



#1
February 25, 2010 at 03:18:46
In the VBA editor, place your cursor on the word Protect and hit F1.

This will open the VBA Help file which will explain all of the optional arguments for the .Protect method.

BTW...there's one other question you should be asking:

How do I protect the VBA code so that the users can't see/delete/alter it?


Report •

#2
February 25, 2010 at 23:15:18
Hi DerbyDad03,

I know how to protect VBA code but the code i posted dosen't give fullproof security as the cell having formula can be selected and then unprotect them through tools menu.

The nice part of the code i posted is that the cells having formula only is protected whenever and wherever they occur in the sheet; the bad part is, it dose not protect with password.

The first line of the code should have been like this:

Sub Worksheet_SelectionChange(ByVal Target As Range)

How to hide and protect with password to the cells having formula only?.


Report •

#3
February 26, 2010 at 04:00:01
As I said in my first response:

In the VBA editor, place your cursor on the word Protect and hit F1.

This will open the VBA Help file which will explain all of the optional arguments for the .Protect method.

I'm sure that you will find that one of those arguments will suit your needs.

(Yes, I could just give you the answer, but I think you'll learn more if you do the research yourself.)


Report •

Related Solutions

#4
February 26, 2010 at 04:45:28
✔ Best Answer
Hi,

The approach you are taking seems quite complex if all you want to do is protect cells containing formulas.

Run a macro that identifies all the cells with formulas on the worksheet in question. If the cell contains a formula - lock the cell, if it doesn't contain a formula - unlock the cell. Then as a final step Protect (with a password) the whole worksheet.

As DerbyDad03 notes, you will have to protect the VB code so that the password is not visible, or else do the worksheet protection with password manually after running the macro to lock or unlock individual cells.

In either case users will be presented with a worksheet where they cannot change cells containing formulas and they will not be able to turn off the protection, as it requires a password.

Regards


Report •

#5
February 26, 2010 at 23:38:06
Thanks DerbyDad03 and Humar for reply. I got exactly what i need.

Report •

Ask Question