Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"Disk is Full" add-on question to "Can't reset last cell" post tod | Excel Discussion (Misc queries) | |||
Question on determining "ROW" inside of a "For .. RANGE " loop | Excel Programming | |||
Validation question - allow -500%, 0%, "=50%*50%" but forbid "A", "", " ", "-" ? | Excel Programming | |||
Is the list of Worksheets limited to "This Workbook" | Excel Programming |