@protecting a work sheet

Question

i had problem of protecting my workbook and you gave me answer -

Yes, run this code:

Sub HideAll() dim NoHide as worksheet

orksheets.add
set nohide=activesheet
range("A1").value="You must enable macros"
for each x in Sheets
  if x.name<>nohide.name then x.visible=xlveryhidden
Next
thisworkbook.save

End Sub

and change your workbook open to:

Private Sub Workbook_Open()

 On Error Resume Next
 Err.Clear
 Application.DisplayAlerts = False
 Sheets("Special").Delete
 If Err.Number = 0 Then
     SaveSetting "X", "X", "X", "OK"
     ThisWorkbook.Save
 Else
     If GetSetting("X", "X", "X", "NG") = "NG" Then
         MsgBox "You do not have rights to this workbook, sorry.", vbExclamation
         Application.Quit
     End If
 End If
 For each x in Sheets
     x.visible=xlsheetvisible
 Next

End Sub

i tried this but can you please explain more where to put the first part of the code and the later one , because i tried putting them together as you said earlier by double clicking on "this workbook" in VBA ,but it failed. failed in the sense after coping to another computer it still asks for enabling Macross ; if i enable macross it shows the desired message ; but if i don't enable macross the sheet is ready for use .

please guide me .........

Answer

The first part is in a regular module - In the VBE: Insert/Module, then put in the Sub HideAll() routine and run it (Press F5 while the cursor is in it). This isn't code which is sent to the user. The Private Sub Workbook_Open goes in the place where you'd double-click "ThisWorkbook". Yes, you will still get the enable macros message, but the user won't see anything except a sheet which says "You must enable macros" and the workbook would be unusable otherwise.

You should also password protect the VBA code: From the VBE: Tools/VBA Project Properties; there are 2 tabs - select the Protection tab, click the lock project checkbox, and enter a password.

Question

QUESTION: i have to protect my work sheet in such a way that

Suppose i have made a spread sheet of suppose quantity surveying , it automatically

calculates all the quantities after giving some basic dimensions , My spread sheet has demand in market , Now i want to sale this spread sheet

What do you think how should i protect my sheet , otherwise after sale my customers may sale

this to others , is there any way that if i allow the sheet for one computer the copy of same

should not be aloud for other computers until i allow the copy or i put some password there, Sir Please answer..............

ANSWER: When YOU send out the workbook, put in new sheet called "Special" and hide it. Press Alt/F11 to get to the VBE, doubleclick on "ThisWorkbook" in the VBAProject window, and put in this code: Private Sub Workbook_Open()

   On Error Resume Next
   Err.Clear
   Application.DisplayAlerts = False
   Sheets("Special").Delete
   If Err.Number = 0 Then
       SaveSetting "X", "X", "X", "OK"
       ThisWorkbook.Save
   Else
       If GetSetting("X", "X", "X", "NG") = "NG" Then
           MsgBox "You do not have rights to this workbook, sorry.", vbExclamation
           Application.Quit
       End If
   End If

End Sub -HTH

---------- FOLLOW-UP ----------

QUESTION: after sending out a workbook i put a new Sheet , hide it , put code in VBA ; after this the result i get is - sheet (i.e. Special) i inserted gets deleted on the other hand i still can make multiple copies of the sheet i want to protect and also i can edit it and make changes,

if i made some mistake please guide me ............

ANSWER: Yes, running the code removes the sheet, but the user can still use the workbook. Once it's been used, if you send it to another computer and try it, it won't work.

Here's how it works. You send out the workbook with the hidden Special sheet. User "A" tries it out. This removes the sheet and leaves "OK" in his registry. The next time he uses it, it doesn't have the Special sheet, but the registry has "OK in it, so he can continue. He sends it on to someone else. They try to use it. It sees that the Special sheet is not there, but the registry doesn't have "OK" in it, so he gets that message about not being able to use the workbook.

---------- FOLLOW-UP ----------

QUESTION: yes i tried it works , but when i copy it to other computer and open the workbook it first ask to enable the Macros then it shows the message about not being able to use the workbook, if i don't enable macros the workbook is ready for the use.

is there any way that without enabling the macros workbook can't be used or ..... Please help....

Answer

Yes, run this code:

Sub HideAll() dim NoHide as worksheet

 orksheets.add
 set nohide=activesheet
 range("A1").value="You must enable macros"
 for each x in Sheets
   if x.name<>nohide.name then x.visible=xlveryhidden
 Next
 thisworkbook.save

End Sub

and change your workbook open to:

Private Sub Workbook_Open()

  On Error Resume Next
  Err.Clear
  Application.DisplayAlerts = False
  Sheets("Special").Delete
  If Err.Number = 0 Then
      SaveSetting "X", "X", "X", "OK"
      ThisWorkbook.Save
  Else
      If GetSetting("X", "X", "X", "NG") = "NG" Then
          MsgBox "You do not have rights to this workbook, sorry.", vbExclamation
          Application.Quit
      End If
  End If
  For each x in Sheets
      x.visible=xlsheetvisible
  Next

End Sub

Question

QUESTION: i have to protect my work sheet in such a way that

Suppose i have made a spread sheet of suppose quantity surveying , it automatically calculates all the quantities after giving some basic dimensions , My spread sheet has demand in market , Now i want to sale this spread sheet

What do you think how should i protect my sheet , otherwise after sale my customers may sale this to others , is there any way that if i allow the sheet for one computer the copy of same should not be aloud for other computers until i allow the copy or i put some password there, Sir Please answer..............

ANSWER: No offense to but he just posted a code based solution in another group - I suspect it was inspired by your question.

In my opinion, this can be so easily broken it isn't worth the effort. But it is your property.

---------- FOLLOW-UP ----------

QUESTION: to whom i sale this , they even don't knowwell if i am coding the workbook there is no chance of breaking the code so if you have any idea @ this , please help

Answer

set you security to high. Open a workbook with a macro. Try to run it. It won't run becausehas silently disabled macros. Set security to medium. Open the workbook. The user is prompted to disable macros. You don't have to know anything aboutto disable a macro based solution.

I will grant that for those people who are not interested in "stealing" you product a macro based solution may be all you need because you don't need any protection - they wouldn't take your ideas anyway. I thought you were trying to protect your ideas from those that want to take them. I use a padlock analogy. A padlock works fine for those that don't want to break into your stuff. For those that do, it does not stop them.

I told you what I thought might work. If your property is valuable, then you might want to spend some money hiring someone who can help you secure it. (not me - I am not soliciting work). I just don't want you (and certainly not me) to waste time cobbling together some complex solution and then finding the it is useless. Others feel differently - so you may want to listen to them. I believe I gave you very plausible arguments why they are wrong. You will have to make your own decision.