Hello All,
(I posted this in the Excel Forum yesterday and did not get any answers, hopefully someone here can help me)
I would like to develop a VBA whereby users are only able to access the Excel file if they run the VBA code. Is there any way of doing this.
I can stop users (via code):
Closing Userforms etc.
Deleting/Adding rows and columns
Disable the menu
Another thing I thought was to protect the Workbook and only unprotect it via VBA code.
If the user choses "Disable Macros" at the security prompt, then they will have full access to the data. Is there a way to stop this.
Another thought was to process the file without id being open ie set a reference to it from another another Excel VBA module (This will probably be a bit cumbersome for writing the code though).
Reason why I want this is because the application will be one where
employess can book their annual leave. Each person has a limited number of
days they can book and on each day only a limited number of employees can be
off.
As you can imagine if they have full access they can book what they want!
Any suggestions as to what I can do
Cannot use VB and compile it either (security restriction network at work, cannot use anything that will create an "installation")
Thanks in advance.
Chas
File Protection from Users
aashta
Hi Derek,
Looks like Access is out of the question then.
I'm going to try an approach in Excel, then maybe when done I will put it on the intranet too. (One problem already springs to mind, multi user access. Since it will only be for about 250 users, I suppose the next one will have to wait until first user has finished. Will put some kind of timer in it so that if not being used for a specified amount of time then will log out.
Another thing is that our Intranet server is somehwere in Chicago (wondering about the speed now).
Will give the Intranet Administrators a call and see what they have to say. (I dont hold much hope there though!)
Will keep you posted (via your website)
Chas
ReevesEdward
If the users don't have Access on there machines they won't be able to run the forms in the database, or I don't think they will. But! If they have Excel you could use Access as the back end and Excel as the front end... do you know DAO or even better ADO. It's still a bit long winded approach though, not much of a solution, ideally you'd have wanted the forms in Access, like an Access application.
Back to the drawing board....
First of all you'd want all your data in one place, thats the important thing.... you want all your users to update one data store...
An Excel spreadsheet could be used for this, Excel sheets can be accessed as a database. On my old blog I did a post about using SQL to query an Excel spreadsheet. Here's the pdf....
http://homepage.ntlworld.com/derek.smyth1/blog/How%20to%20parse%20large%20amounts%20of%20data%20in%20Excel%20efficiently.pdf
The PDF demonstrates a SELECT statement but you can do INSERT and DELETE statements also. So you could get away with using Excel as your database. You will need to know ADO to do this. You'd need to create an Excel addin that contains the forms for updating the back end. The addin would just contain forms and rather than updating the workbook in Excel you'd need to use ADO to update the spreadsheet on the network.
It's possible but it would be limited to 65,000 records a year, and it should only be a temporary solution so you'd want to write code that easy to port to the next solution.
What about a web site on your intranet You know web development
I know about large companies and the political loops that need to be jumped through to get software installed.
The email address has been fixed on my blog site so mail won't bounce now. T
Vijay R
hey Chas,
I think a database would be better. Using spreadsheets to store mutiple users holiday details, if one sheet per user, would fragment the data. You wouldn't be able to ask questions of the data, for example you couldn't check how many employees where off on a specific day without needing to collate all the data in all the spreadsheets. Trust me you'll have problems with this approach.
If you thinking of only updating one spreadsheet on a network share with all the employees holidays then that is essentially a database, but you'd be treating it as a spreadsheet. If you do that you wouldn't be able to take advantage of the data storing and searching features of a database system. Asking a spreadsheet for the number of employees that are holiday for a specific day is much more difficult than asking a database.
Do you have Access Let me know what you think of this and if it's feasable for you to do.
Sudhakar.hcitek
Hello Derek,
I have not had many suggestions for this but yours seems the best so far (I had a feeling this would be the way to go but needed to hear from another pro!).
Yes I can get Access for my computer, but the PCs that will be eventually running it will not have Access (too costly). I do remember that we had an Access database that could be read on Pcs that did not have Access and I remember setting something to do with ODBC in the control panel.
If all PCs will be able to read/write to the database the I will definitley go with this approach.
Being a fairly large Company, things move very slowly here, it will take weeks to get approval for Access to be installed on my PC and then some time for me to learn it.
Anyway will keep you posted.
I used to work with Foxbase many years ago, where you could lock the record (it was a multiuser database, does Access have this type facility, if it does, I'll think of making this accessible from more than one PC).
Thanks for the info (I will be asking more I'm sure). Incidentally I emailed you from your website but both times came back undeliverable).
Chas