• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

File Name

Bomino

Member
Hello,
I am trying to prevent users from opening backup copies of some files I have on a shared drive. Apparently something's wrong with the following code:

Code:
Private Sub Workbook_Open()
Dim MyName As String
Dim Location As Integer

MyName = ActiveWorkbook.Name
Location = InStr(MyName, "*backup*")

If Location = 0 Then

MsgBox "Please proceed."

Else

MsgBox "You are using the backup copy"
exit sub
End If
End Sub

Could someone please help me fix the code or provide a better solution?
Regards.
 
Last edited:
Hi,

Try your code by just removing the "*"s in "*backup*". Your code should work fine if you just give "backup".

Regards,
Prasad DN
 
hi,

per your code, if the activeworkbook.name is having a word "backup" only then it will work as per your code.

Pls post some example of what the MyName variable will have?

Regards,
Prasad DN
 
Prasad DN,
Please save the attached file to your desktop. If you open and close it back, excel will automatically create a backup copy on your desktop.
Now, Let's imagine that you would like to make some changes to the spreadsheet again; but instead of opening the file you inadvertently open the backup copy. I would a macro that will prevent that from happening.
Hope my explanation make sense.
Regards
 

Attachments

  • b4 with macro.xlsm
    36.7 KB · Views: 4
You can also use full syntax of InStr function which will make it ignore cases.

e.g.
Will check for option compare settings in the module. Default is binary so it is case sensitive.
Code:
Location = InStr(MyName, "backup")
Will ignore case
Code:
Location = InStr(1, MyName, "backup", vbTextCompare)
 
Back
Top