I had a problem with users of my Excel worksheet at work saving copies of my file leading to some people entering data into the real file and some people entering data into the copy – BIG mess!! Today I thought of this solution: On workbook open, I assign the pathway of the workbook to a variable and then compare that pathway to the pathway where the real workbook resides. If they don’t match, a message pops up and tells them it’s just a copy and then closes without letting them enter any data. I had to use the UCase function because I found out that depending on what computer the user is using, the pathway was capitalized differently – by using UCase, it doesn’t matter how things are capitalized J Here’s the code to copy: (Put this in the Workbook object code)
Option Explicit
Private Sub Workbook_Open()
Dim myfullfilename As String
myfullfilename = ActiveWorkbook.FullName 'get the pathway of the current workbook
'The actual file pathway should be typed below in all caps, this is just an example file pathway
If UCase(myfullfilename) <> _
"J:\SHARED DRIVE\EXCEL CODE\RESTRICT OPENING FROM OTHER PLACES.XLSM" Then
MsgBox "Someone has made a copy of the file, you may not enter data here.", vbOKOnly, "Invalid file"
Application.Quit
End If
End Sub
Make sure to look at the picture at the end of the code because the blog cut some of my lines off in the wrong places. Although I just tried copying the typed lines and pasting them into a module and it does paste correctly :)
ReplyDeleteWow, that made NO sense to me, but it's so cool that my mom talks about computers like that :) I'm sure someone will be googling a questions soon that leads them here!! Someone really smart... :)
ReplyDelete