limited usage

[jinvictor]

how can use VB to design one program that only allow other people onl
open the workbook 10 times, 11th time when he open the workbook the ms
shows "over the use limit, contact owner".

than

 

[Tom Ogilvy]

Obviously you would need to maintain a count of how many times the workbook
had been opened by utilizing the Workbook_Open event and storing the value
in a defined name or hidden worksheet.

The challenge becomes trying to enforce the limitation or preventing the
user from trying to circumvent it. There isn't a good answer to that.

 

[SteveM]

The easiest thing to do would be to place an increment count on a very
hidden worksheet. E.g. Name the increment cell "Increment" on a sheet
named "Hidden", and initialize the cell value to 0. Then add this code
to your Workbook Open sub

Private Sub Workbook_Open()
Dim incRange As Range
Set incRange = Range("Increment")

Sheets("hidden").Visible = xlVeryHidden
incRange = incRange + 1
Me.Save
If incRange > 10 Then
Application.DisplayAlerts = False
MsgBox "over the use limit, contact owner"
Me.Close
End If

End Sub

 

[SteveM]

One other thing. xlVeryHidden keeps the user away from the increment
value because he can't unhide the sheet using the Format -> Sheets
menu. But Tom is right. An industrious user could get into your VB
code even if you password protect it. Although most end users are not
that knowledgeable.

If even novice users figure out your strategy though, they could just
make copies of the workbook and prior to the exhaustion of the use
number. You could perhaps get around that by using a date-time stamp
check instead of a count increment (i.e., let them use it for 2 weeks
after they open it the first time.) Full lockdown would require
something more sophisticated.

SteveM

 

[Tom Ogilvy]

From another workbook, they could do

for each sh in Activeworkbook
sh.Visible = sh.xlSheetVisible
Next

so xlVeryHidden is barely better than just hiding the sheet for someone who
is willing to put in even a minimal amount of effort.

or

set rng = Activeworkbook.Names("Increment").RefersToRange
rng.Value = -1000000


Your misleading the OP in suggesting that he can limit use of his workbook.
If he is satisfied with using weak protection, then it probably isn't worth
the extra effort of using protection at all - obviously only my opinion

 

[SteveM]

Tom,

My advice was contingent on who and how large his target market is. If
it is a handful of customers and he knows that they are not
sophisticated with Excel, well then yes, the weak protection may be
enough. If he plans on developing a universal add like @Risk, then
sure, he'd want to use a fail-safe protection strategy consistent
with the size of that community.

I'm guessing that if his target market is indeed that expansive, he
(and his team of developers) would be aware of the security
requirements and the implications of a less-than-lockdown approach.

SteveM

 

[jinvictor]

thanx a lot for all your time, its realy helpful. i only need the basic
one to stop some people in the workplace.
thanx again.

 

[jinvictor]

if they use save as, even i set up they only can use the workbook 10
times, they still can use save as file for another 10 times, so on that
will never stop them to use it, what can i do with that?

 

[jinvictor]

if they use save as, even i set up they only can use the workbook 10
times, they still can use save as file for another 10 times, so on that
will never stop them to use it, what can i do with that?

 

[SteveM]

As i mentioned you can use a date check rather than a number opened
check. Hard code the date you hand off the workbook to your customer,
then give him a fixed number of days to test the application. Any
copies would have the same restriction. Something like this:

Private Sub Workbook_Open()
Dim StartDate As Date

StartDate = "06/19/06" 'Date delivered to customer
If Date - StartDate > 14 Then ' Two week trial period
Application.DisplayAlerts = False
MsgBox "Trial period expired, contact owner"
Me.Close
End If

End Sub

They could still circumvent that by changing the date on their system
clock before they open the app, but it would be a pain. I suppose you
could use both tests, i.e., e.g. 10 uses within 2 weeks to make it more
difficult still.

SteveM

 

[jinvictor]

can you please tell me how can i use both together(limited times and
limted dates)?
thanx!