"Limited Use Question?"

[DEG]

How do you (or can you) limit the use of a macro to a certain number of Uses?
Uses being opening the WB and allowing macros.... macro could run any number
of times once the WB was opened. Some kind of counter installed so the OP
could not change it.... Hope I have described what I'm after here.... TIA

Don

 

[royUK]

You would need to record the opening of the workbook - in a hidden
sheet, the registry or a nmed range.

n If statement would check this and if it equals a ertain value close
the workbook. Something like this

Code:
--------------------
Option Explicit
Option Compare Text
Dim ws As Worksheet
Const MaxUses As Long = 5 '<- change uses
Const wsWarningSheet As String = "Splash"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'hide all sheets except warning sheet
For Each ws In ThisWorkbook.Sheets
If ws.Name = wsWarningSheet Then
ws.Visible = True
Else: ws.Visible = xlVeryHidden
End If
Next
'record opening in remote cell
With Sheets(wsWarningSheet).Cells(Rows.Count, Columns.Count)
.Value = .Value + 1
End With
End Sub

Private Sub workbook_open()
'check tored usage before cotinuing
If Sheets(wsWarningSheet).Cells(Rows.Count, Columns.Count).Value >= MaxUses Then
MsgBox "Trial up", vbCritical, "Trial period"
Exit Sub
End If
Sheets(wsWarningSheet).Select
'unhide hidden sheets
For Each ws In ThisWorkbook.Sheets
ws.Visible = True
Next
'hide warning sheet
ActiveSheet.Visible = xlVeryHidden
End Sub

 

[DEG]

Thanks for the response Roy....but I didn't want to close the WB, just make
the macros inactive.......

 

[DEG]

There would be a data sheet that they would still be able to read, enter and
print from.....the macro's just automate some frequently used functions......

 

[royUK]

This example will store the number of times that a workbook is opened in
a Hidden Name. After the specified number of times opened the macro will
not run

Code:
--------------------

'---------------------------------------------------------------------------------------
' Module : Module1
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)
' Website : 'click here for more examples and Excel Consulting' (http://www.excel-it.com)
' Purpose : Create a check by using a Hidden Name to control how often a macro runs
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
' projects but please leave this header intact.

'---------------------------------------------------------------------------------------
Option Explicit

Const MaxUses As Long = 5 '<- change uses

Private Sub workbook_open()
Dim MyUses As Long
On Error Resume Next
MyUses = Evaluate(ThisWorkbook.Names("USED").RefersTo)
If MyUses < 1 Then MyUses = 0 'first used
MyUses = Evaluate(ThisWorkbook.Names("USED").RefersTo) + 1
MsgBox MyUses
ThisWorkbook.Names.Add Name:="USED", RefersTo:="=" & MyUses
End Sub
Sub Test()
'test if OK to run macro
If [USED] > MaxUses Then Exit Sub
MsgBox "Hello World"
End Sub

 

[DEG]

Thank you Roy...that's what I needed.

Don

 

[royUK]

Glad that helped

 

[DEG]

Roy,

Can't thank you enough. I've now had a chance to try out a couple of the
solutions you laid out and they all work well. I chosen to use a cell in a
hidden sheet and use vbveryhidden to hide same....seems to be a little more
secure. The named range solution works well but IMO would be too easy for an
OP to overcome. If there is a way, other than a macro command, to "unhide" a
veryhidden sheet it eludes me.

Anyway, thanks again for your help and for staying with me on this...have a
great day....

Don

 

[royUK]

I think the first place that anyone thinks to look is the very hidden
sheet option. The name is harder to find, many users would not even
think of it.

The other option would be the Registry, but that means it would work on
a different computer. You could also write to a text file.

None are foolproof though