How to get an NRL tipping spread sheet?

@protecting a work sheet

• 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 ......... 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.... 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:

  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. 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 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.