"Limited Use Question?"
 

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

"Limited Use Question?"
 

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

--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101883

"Limited Use Question?"
 

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

"royUK" wrote:


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

--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101883

"Limited Use Question?"
 

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" wrote:


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

--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101883

"Limited Use Question?"
 

Glad that helped


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101883

"Limited Use Question?"
 

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" wrote:


Glad that helped


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101883

"Limited Use Question?"
 

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


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101883