Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How many times excel sheet is opened

    Hello
    I would like to send an excel sheet for someone and i want him to use is for 5 times only.
    for example: i want make a counter in registry of windows or in an external text to count how many times the sheet is opend (1, 2,3 so on) then if times of open >4 then close application.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    alwkeel,

    You're going to have to use VBA to accomplish that (writing to the registry). I'd suggest that since you have to use VBA anyway avoid the registry and rather create an Auto_Open macro that will write to a Hidden area (Sheet or Cell).

    Each time the sheet is opened you test the value of the hidden counter and automatically exit if it = 5 if not you just increment the counter and allow the user to continue.

    If you use a separate sheet you need to use vba to set the visible property to vbVeryHidden and unless the user is really savy they won't know it is there and you can also password protect the VBA Project to prevent the user from snooping.

    If you want to use a hidden cell it is a little trickier as you'll have to protect that cell and unprotect all the cells you want the user to be able to access and then password protect the sheet.

    I'll work up a test case and post back. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Ok,

    Here's a sample sheet using a VeryHidden sheet.

    Hidden Sheet Name: Counters
    Cell with count Name: OpenCounter
    VBA Project Password: Test

    Code:
    Option Explicit
    
    Sub Auto_Open()
    
       Dim wksCounters As Worksheet
       
       Set wksCounters = Sheets("Counters")
       
       If wksCounters.Range("OpenCounter") = 5 Then Application.Quit
         
       wksCounters.Range("OpenCounter").Value = _
          wksCounters.Range("OpenCounter").Value + 1
       
       ActiveWorkbook.Save
       
    End Sub
    
    Sub HideCountersSheet()
    
       Sheets("Counters").Visible = xlVeryHidden
       
    End Sub
    Use the VBA Editor Immediate Window to:
    Unhide the sheet: Sheets("Counters").visible = True
    Reset the count: Sheets("Counters").Range("OpenCounter").Value = 0
    Check the count: ?Sheets("Counters").Range("OpenCounter").Value

    Remember you can get access to make changes after the count is reached by holding the SHIFT Key when you open the file (prevents the VBA from running).

    Sample File:VBA - Excel - Limit Usage to Count.xlsm

    Note: If you want to give the user a message when the limit is reached you can change the code to this:
    Code:
    Sub Auto_Open()
    
       Dim wksCounters As Worksheet
       
       Set wksCounters = Sheets("Counters")
       
       If wksCounters.Range("OpenCounter") = 5 Then 
         Msgbox "You have reached the usage limit for this workbook.", _
                vbokonly+vbcritical, "Warning: Usage Limit"
         Application.Quit
       Else  
         wksCounters.Range("OpenCounter").Value = _
          wksCounters.Range("OpenCounter").Value + 1
       
         ActiveWorkbook.Save
       End if
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Alternative might be to use a defined name or custom property and store the count in that. Of the two, a custom property would probably be less apparent to most users.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    Turning off macros will defeat that. Is there a way to prevent the sheet opening if VBA is off?

    cheers, Paul

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    In a word, no. In any event, even without turning off macros, it's easily defeated. For example, if the workbook is emailed, one need do nothing more than download it again. Similarly, if one sets the file's read-only attribute, the counter won't update (and the macro will likely crash). And if the VBA project isn't protected, anyone can access it to disable the counter, etc. etc.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You could make it necessary to enable macros in order to see the content. For example, if the workbook close event hid the 'wanted' worksheets and a workbook open event showed it then the user would NEED to enable macros in order to get what they wanted. That could still be worked around with some lateral thinking but might be protection enough.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    awkeel,

    Ok, here's an updated version of the file with some major changes.

    1. If macros are disabled all sheets, except a message sheet, will be very hidden. A message sheet will be displayed indicating that VBA/Macros must be enabled. {This technique I got from Maudibe}
    2. Upon opening the workbook the user will be presented with a message detailing which run this is out of how many.
    3. If the allowed run count is exceeded the user is presented with a message indicating the fact.
    4. You can add any number of user sheets w/o the code needing to be changed.


    Of course the VBA is still protected (PW: Test) and I think it would take a pretty determined user to circumvent the run count.

    File: VBA - Excel - Limit Usage to Count.xlsm

    HTH
    Last edited by RetiredGeek; 2015-10-13 at 18:56.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by RetiredGeek View Post
    I think it would take a pretty determined user to circumvent the run count.
    Depends on how they get the file; if it's via email or CD-ROM, for example, all it takes is reloading from the source. Even without that, simply making multiple copies and using each one till it expires is enough.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Paul,

    You're, of course, assuming that the data in the workbook isn't cumulative.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Even if they are, they could most likely be copied from one to the other. Indirectly, though, this raises another issue - whether protecting the workbook in this way will deny users access to their data once the number of allowed uses is up. Furthermore, a simple counter is a fairly crude tool that might result in one person being able to use the workbook for less than a day while another who keeps it open might use it for weeks at a time.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    What a lovely can of worms!

    cheers, Paul

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Hey Y'all,

    Just an observation but since the OP is creating the workbook for someone else it would seem to me that that someone would be rather novice and not know how to get around the road blocks the code puts in his/her place? Also obviously, if opening the workbook after a certain number of uses is the desired situation naturally the user will be denied further access to the data the workbook contains.

    Am I missing something here? Are we over thinking this?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You ask that, human nature being what it is?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #15
    New Lounger
    Join Date
    Oct 2015
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ok,

    Here's a sample sheet using a VeryHidden sheet.

    Hidden Sheet Name: Counters
    Cell with count Name: OpenCounter
    VBA Project Password: Test

    Code:
    Option Explicit
    
    Sub Auto_Open()
    
       Dim wksCounters As Worksheet
       
       Set wksCounters = Sheets("Counters")
       
       If wksCounters.Range("OpenCounter") = 5 Then Application.Quit
         
       wksCounters.Range("OpenCounter").Value = _
          wksCounters.Range("OpenCounter").Value + 1
       
       ActiveWorkbook.Save
       
    End Sub
    
    Sub HideCountersSheet()
    
       Sheets("Counters").Visible = xlVeryHidden
       
    End Sub
    Use the VBA Editor Immediate Window to:
    Unhide the sheet: Sheets("Counters").visible = True
    Reset the count: Sheets("Counters").Range("OpenCounter").Value = 0
    Check the count: ?Sheets("Counters").Range("OpenCounter").Value

    Remember you can get access to make changes after the count is reached by holding the SHIFT Key when you open the file (prevents the VBA from running).

    Sample File:VBA - Excel - Limit Usage to Count.xlsm

    Note: If you want to give the user a message when the limit is reached you can change the code to this:
    Code:
    Sub Auto_Open()
    
       Dim wksCounters As Worksheet
       
       Set wksCounters = Sheets("Counters")
       
       If wksCounters.Range("OpenCounter") = 5 Then 
         Msgbox "You have reached the usage limit for this workbook.", _
                vbokonly+vbcritical, "Warning: Usage Limit"
         Application.Quit
       Else  
         wksCounters.Range("OpenCounter").Value = _
          wksCounters.Range("OpenCounter").Value + 1
       
         ActiveWorkbook.Save
       End if
    
    End Sub
    HTH
    RetiredGeek, Thank you so much for your help, and it's working good

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •