Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Hello --
I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
I don't think so.
You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Dave --
Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
If the workbooks have to be opened to be used, then you don't want to assign
passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
But if the OP did use that helper workbook to open all the other workbooks, then
the OP could specify the password in the workbooks.open code. Tom Ogilvy wrote: If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Dave and Tom --
Thanks for discussing this. Dave ... a couple of questions: - what is the "OP"? I like the idea of using the helper workbook. - How do I specify the password in the helper workbook's .open code and pass it to the other workbooks? Larry "Dave Peterson" wrote in message ... But if the OP did use that helper workbook to open all the other workbooks, then the OP could specify the password in the workbooks.open code. Tom Ogilvy wrote: If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
OP=Original Poster (in this case, you!)
Take a look at Workbooks.open in VBA's help for an example. If the password to open is always the same, this may get you started: Option Explicit Sub auto_open() Dim myFileNames As Variant Dim myPath As String Dim iCtr As Long Dim testStr As String myPath = ThisWorkbook.Path If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If myFileNames = Array("book1.xls", "book2.xls", "book3.xls") For iCtr = LBound(myFileNames) To UBound(myFileNames) If WorkbookIsOpen(myFileNames(iCtr)) Then 'do nothing, it's already open Else testStr = "" On Error Resume Next testStr = Dir(myPath & myFileNames(iCtr)) On Error GoTo 0 If testStr = "" Then MsgBox "Oh, oh. " & myFileNames(iCtr) & " wasn't found!" _ & vbLf & "Please contact me at xxx.xxx.xxxx!" Else Workbooks.Open Filename:=ThisWorkbook.Path & myFileNames(iCtr) End If End If Next iCtr 'thisworkbook.Close savechanges:=false End Sub Function WorkbookIsOpen(myFileName As Variant) As Boolean On Error Resume Next WorkbookIsOpen = CBool(Len(Application.Workbooks(myFileName).Name) 0) On Error GoTo 0 End Function L Mehl wrote: Dave and Tom -- Thanks for discussing this. Dave ... a couple of questions: - what is the "OP"? I like the idea of using the helper workbook. - How do I specify the password in the helper workbook's .open code and pass it to the other workbooks? Larry "Dave Peterson" wrote in message ... But if the OP did use that helper workbook to open all the other workbooks, then the OP could specify the password in the workbooks.open code. Tom Ogilvy wrote: If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Dave --
Thank you for this example. It gives me a great start ... password will always be the same. I will post a question to Tom's his comment "If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords." Larry "Dave Peterson" wrote in message ... OP=Original Poster (in this case, you!) Take a look at Workbooks.open in VBA's help for an example. If the password to open is always the same, this may get you started: Option Explicit Sub auto_open() Dim myFileNames As Variant Dim myPath As String Dim iCtr As Long Dim testStr As String myPath = ThisWorkbook.Path If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If myFileNames = Array("book1.xls", "book2.xls", "book3.xls") For iCtr = LBound(myFileNames) To UBound(myFileNames) If WorkbookIsOpen(myFileNames(iCtr)) Then 'do nothing, it's already open Else testStr = "" On Error Resume Next testStr = Dir(myPath & myFileNames(iCtr)) On Error GoTo 0 If testStr = "" Then MsgBox "Oh, oh. " & myFileNames(iCtr) & " wasn't found!" _ & vbLf & "Please contact me at xxx.xxx.xxxx!" Else Workbooks.Open Filename:=ThisWorkbook.Path & myFileNames(iCtr) End If End If Next iCtr 'thisworkbook.Close savechanges:=false End Sub Function WorkbookIsOpen(myFileName As Variant) As Boolean On Error Resume Next WorkbookIsOpen = CBool(Len(Application.Workbooks(myFileName).Name) 0) On Error GoTo 0 End Function L Mehl wrote: Dave and Tom -- Thanks for discussing this. Dave ... a couple of questions: - what is the "OP"? I like the idea of using the helper workbook. - How do I specify the password in the helper workbook's .open code and pass it to the other workbooks? Larry "Dave Peterson" wrote in message ... But if the OP did use that helper workbook to open all the other workbooks, then the OP could specify the password in the workbooks.open code. Tom Ogilvy wrote: If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Tom --
Thank you. I haven't yet implemented the code Dave suggests below, so I don't know whether my hidden books must be "opened to be used". I want the hidden books to not be visible to the user. By "file password" to mean the one used to protect the workbook? If so, then should I protect each sheet in each book? Larry "Tom Ogilvy" wrote in message ... If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
I think Tom's warning was to not use passwords if the other three workbooks were
to be opened manually (and you wanted to keep them "invisible") or if the primary workbook had links to the other 3. In either case, the user would have to specify the passwords to open/get access to those 3 workbooks. === Workbooks can be protected via tools|protection inside excel--you can protect the structure--no renaming of worksheets, no deleting of worksheets, no moving of worksheets. Workbooks can be protected from opening/modifying via the File|SaveAs dialog. File|SaveAs|Tools|General Options (xl2002+ wording) File|SaveAs|Options (in earlier versions) (The file|saveAs password was what I was using in the suggested code.) The protection via tools|protection is pretty simple to break. J.E. McGimpsey has code that will unprotect the workbook/worksheet in just moments: http://www.mcgimpsey.com/excel/removepwords.html There are commercial password breakers that will break the File|SaveAs password. http://www.lostpassword.com/ And if you've got the password embedded in your code, you'll want to protect that, too. Inside the VBE with your project selected: tools|VBAProject Properties|Protection tab But this protection can be broken easily, too. And even worse, if the users notice that there are multiple workbooks open, they could just unhide the workbooks and take a look. If you're doing this for security purposes, realize that it'll keep out the not-so-curious. But it won't stop the dedicated. L Mehl wrote: Tom -- Thank you. I haven't yet implemented the code Dave suggests below, so I don't know whether my hidden books must be "opened to be used". I want the hidden books to not be visible to the user. By "file password" to mean the one used to protect the workbook? If so, then should I protect each sheet in each book? Larry "Tom Ogilvy" wrote in message ... If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Don't let me distract you. Go ahead and work with Dave.
-- Regards, Tom Ogilvy "L Mehl" wrote in message ... Tom -- Thank you. I haven't yet implemented the code Dave suggests below, so I don't know whether my hidden books must be "opened to be used". I want the hidden books to not be visible to the user. By "file password" to mean the one used to protect the workbook? If so, then should I protect each sheet in each book? Larry "Tom Ogilvy" wrote in message ... If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide workbooks that drive another workbook
Dave and Tom --
Thanks again to you both. Larry "L Mehl" wrote in message ... Tom -- Thank you. I haven't yet implemented the code Dave suggests below, so I don't know whether my hidden books must be "opened to be used". I want the hidden books to not be visible to the user. By "file password" to mean the one used to protect the workbook? If so, then should I protect each sheet in each book? Larry "Tom Ogilvy" wrote in message ... If the workbooks have to be opened to be used, then you don't want to assign passwords to them. At least not file passwords. -- Regards, Tom Ogilvy "L Mehl" wrote in message ... Dave -- Your first suggestion sounds workable. Thank you. I will add passwords to the 3 hidden books. Giving the user 5 books but only seeing one will not be a problem. Larry "Dave Peterson" wrote in message ... I don't think so. You'd have to give 4 different workbooks to the customer. You'll ask them to store the files somewhere. They'll have to see the 4 files when they store them. If you can get by that problem, you could save each of the 3 workbooks hidden. Open all 3 and do this for each one: Window|Hide (and hide the single(?) window to that workbook. Now close excel and answer Yes to "do you want to save your changes". Now when one of those three workbooks is opened, it'll be hidden. It won't stop all--but some/most people won't even notice. ==== I think I'd might give them a 5th workbook. It's only purpose is to open the 3 hidden workbooks first, then open the "real" workbook. If you have links between workbooks, it'll be quicker to update these links when they're all open. But anyone who is curious won't be fooled. === Another option (that is also not foolproof). Move those other worksheets into your real workbook. But hide the worksheets. Protect the workbook so they can't easily unhide the worksheets. (But this, too, is easily broken.) My rule is: If you don't want to share the data, don't put it in excel. L Mehl wrote: Hello -- I need help in hiding 3 workbooks while maintaining their ability to link to and calculate for a 4th workbook. Our sales-support application identifies hardware and prices for a customer/user-specified system. It consists of 4 workbooks: Customer Engineering FinMktSales FinCalculations Each links to the other 3 to pass and retrieve information. Customer does the data entry and results-display; the other 3 are calculators, not to be viewed by the user. Our plan is to - populate Customer.xls with our best guess of starting values - populate the other 3 with current engineering data, prices, etc., relevant to the specific sales opportunity - give the set of 4 to the user We want the user to be able to test various scenarios he/she defines in Customer.xls, where that workbook will feed the other 3 and link to and display various calculated results to the user. Is it possible to hide the 3 calculators and protect them from being viewed, in away so that links still work? Thanks for any direction on this. Larry Mehl -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pathnames to linked workbooks change from mapped drive to UNC occasionally | Links and Linking in Excel | |||
Linked network workbooks revert to pointing at my hard drive | Excel Discussion (Misc queries) | |||
how can I protect workbook on a drive from deletion | Excel Discussion (Misc queries) | |||
Link workbooks-C drive to network drive | Excel Worksheet Functions | |||
How to automaticaly hide all toolbars in in workbook when another workbooks are open? | Excel Programming |