[RESOLVED] vba code to block an Excel file to be opened

[news]

Hello to all of the forum,

I am wirting this post as I do not know how to define a vba code in Excel.
I am using Excel2003.

I have a file which is limited of using.
After 10 times using it, a Msgbox is opened, informing that the file will close and cannto be used anymore.

I added following code in ThisWorbook:

Option Explicit
' Limiter le nombre d'utilisation d'un classeur

Private Sub Workbook_Open()
On Error Resume Next
If ActiveWindow.Visible = False Then ' Active Sheet not visible
Application.Quit ' close Excel
Exit Sub
On Error Resume Next
ElseIf Worksheets("Feuil2").Range("A6").Value >= 2 Then
ActiveWindow.Visible = False
Application.Quit
Exit Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
With Worksheets("Feuil2").Range("A2").Value = Worksheets("Feuil2").Range("A2").Value + 1
End With
ActiveWorkbook.Save
Application.Quit
End If
End Sub

So far the vba code is functionning.

As the files was opened 10 times, it is closing.
When clicking o nthe file in the registry it is opening and closing immediatelay.

I would like to ask how to define, which vba code is needed,
that when the file is closing, that the file cannot be used anymore, cannot be opened at all. Means if clicking on it, gets shown a sing 'error'. The file is not opening.

Thanks in advance for some information,

[westconn1]

you can try like this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim cnt As Integer
cnt = ThisWorkbook.CustomDocumentProperties("count")
If Not Err.Number = 0 Then
     ThisWorkbook.CustomDocumentProperties.Add "count", False, msoPropertyTypeNumber, 0
End If
On Error GoTo 0
ThisWorkbook.CustomDocumentProperties("count") = cnt + 1
End Sub
 
Private Sub Workbook_Open()
On Error Resume Next
If ThisWorkbook.CustomDocumentProperties("count") > 9 Then ThisWorkbook.Close False
End Sub

[news]

Hello to all of the forum,

thanks westconn1 for response,
in your proposed code I get a bug in the procedure in : cnt = ThisWorkbook.CustomDocumentProperties("count")
with following Msbox : ("argument or procedure is incorrect)

The vba code which I used to open and close the file, runs well (see attached Excle file).

But I would I like to ask, how to define the vbacode for following procedure :
I have an Excel file that a user can open 10 times.
When the user is opening more then 10 times, then is shown a Msgbox ("time of using is over") and the file is closing.
This procedure is so far runing well.

But which vba code to put,
that when the file is closing, that afterwards this Excel file cannot be opened at all, means
when clicking on the file in the directory to open, that is shown a Msgbox ("file invalid") and that the file does not opened at all.

Thanks in advance for some response,

news

'

[westconn1]

in your proposed code I get a bug in the procedure in : cnt = ThisWorkbook.CustomDocumentProperties("count")

i lost a line of code somewhere, i had it in originally, put
on error resume next
before that line

AFAIK you can not prevent the workbook from opening, if clicked in the shell (explorer window), all you can do is close it immediately with or without messagebox, as below

Private Sub Workbook_Open()
On Error Resume Next
Select Case ThisWorkbook.CustomDocumentProperties("count")
  case 10: msgbox "timeof using is over" : ThisWorkbook.Close False
  case 11 to 14: msgbox " invalid file": ThisWorkbook.Close False
  case > 14:ThisWorkbook.Close False
end select
 
End Sub

[news]

Re:

thanks for vbacode, it is another possiblity as the one I used in the Excel file 'fermer_fichier' which I added before.

I posted, as we got once a Excel file, to test.
We tested with the examples proposed.

To see which vbacode was used, we did Alt+F11, when clciking on the file,
it was not possible as is was protected by password. A user entered twice some code, then the Excel file crashed,
and afterwards it was not possible even to open. As soon was clicked in the directory in Windos Explorer, it was shown the Mscgbox, file invalid, corrupted, and it was not possible otp open at all.
We could only delete the file.

Somehow in the vbacode was put some code, which made the file corrupt,
once Excel crashs down, or to find out vbaprotected password.

[westconn1]

for whatever reason, deliberately corrupting a file would be considered malicious and not a topic for here,
best suggestion i have would be to delete the file, when finished with, or password protect

[news]

Re:

as there is no vbcode to blok a file to open in windows explorer,
I will use then the version of file is closing directly,
as your vb code proposed,
or as the version I use before in the Excel file 'fermer fichier',

news