Visual Basic Script

[Stalker_26]

Hi all,

After recently "popping my cherry" with excel, my boss has decided to task me with the following.

I already have a spreadsheet with IF, V/Hlookups etc, now i need to produce a script/code or whatever it is called, where on any given spreadsheet, any changes to the data, or any new additions to the sheet, excel will auto save a log of what user log in, date and time the change was made, and possibly show what the data was before it was changed.
But it needs to be a log that can only be viewed and not edited.

It also needs to run on a shared HDD so anyone in my dept can view it, i assume that means it will only allow one person at a time to edit the sheet, but i am not sure.
I am aware that a sheet can be password protected, but i have been asked to provide a system that will not allow any changes by anyone without a time, date and user ID stamp.

Any help would be great as i am shortly going to run out of hair to pull out

 

[Pho]

What data are you manipulating? It sounds like you need a database to do this properly.

Here's something which does a similar thing with access (which you should be able to import your excel sheets into with minimal effort)

 

[~J~]

If this is an Excel spreadsheet, you can achieve everything you want using VBA (Visual Basic for Applications), not really Visual Basic Script as such.

The hardest part is probably going to be the logging of changes, if you envisage that there's going to be LOTS of changes on a regular basis, then it's perhaps best to create a connection to Access which stores something along the lines of "user", "old value", "new value", "date/time", "worksheet name", "Row/Cell", etc.

The shared HDD is probably just a network share, it's the actual Workbook itself that would be shared to allow many users to access the same workbook/sheet and allow changes but also keeps track of other changes so that values aren't overwritten by another user who has the same workbook/sheet open at the same time.

Yes you can password protect a worksheet, but likewise with VBA you can attach code to events that would ask exactly what you're after.

It's certainly doable, and it would be a lovely little challenge, but don't know how much of a hardcore sex fest you could cope with seeing as you've just 'popped your cherry', things might still be a little sore!

 

[Stalker_26]

Sounds like a good old romp then, best get to tesco for some red bull!!!

yes it is an excel spreadsheet, a few different ones, but excel none the less.
Now, next question, what the heck is VBA, how does one use it, and where do i find it, n most important, what will it cost to get it and use it?

And yes, the data will be changed on an hourly/daily basis, and at the end of the day collated onto a master sheet to show efficiency, targets, hours, blah blah blah (all the fun stuff)

It will ultimately be, a shared workbook, on our shared network drive, all can access, but no-one may edit without leaving a log of who and when.

Im a hardcore gamer (cod4 + too many beers to count) at the weekends so long spells in front of my PC is no problem.

 

[~J~]

If you've created a Macro, then basically that's the frontend to VBA. It's the bare essential Visual Basic all wrapped up in a dedicated language for Office. I'd pretty much say that whatever you 'do' in Excel, Word, Access, Outlook can be programmed via VBA and it's built in ready for you to go go go!

If you've managed to get by on VBScript, I honestly don't think you're going to have much problems moving up to VBA, yes it's different in that it has a lot more commands to master, but the language structure is identical.

Try recording a macro, say, inserting a new sheet, adding some values, changing the text font, colour, size, a calculation, then stopping the macro and having a look at it using the Macro Editor - all that is in VBA which is a great little way of learning what does what.

Does sound a good romp!!!