Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have an 2007Excel workbook that I want to be used as a demo, is there any type of VBA or ? That I can apply that will allow user to open use workbook let say 10 times and thatís it. Or any suggestion

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I asked a similar question a few years back in this post - my requirement has gone but you may find some ideas there, or there may be some newer methods someone can suggest....

  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Leif
    I thank you for a reply, I read over the previous post you sent, I was hoping for something a little different. I not concerned about additional protection, If someone really wants to get in they can.

    In the past I've come across Excel workbooks that had a limited use, were as you were limited to how many times you could open a workbook before it would not open.

    Almost like in your post about dates, but now using number of times workbook is opened.....

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Insert a blank worksheet at the left most position and then hide the sheet.

    Put the following in the ThisWorkbook module.

    Code:
    Private Sub Workbook_Open()
    Sheets(1).Cells(1, 1) = Sheets(1).Cells(1, 1) + 1
    If Sheets(1).Cells(1, 1) = 11 Then
     	Workbooks("BOOK1.XLS").Close SaveChanges:=False
    End If
    End Sub
    Change the book1.xls to the correct name of your book. Disable macros on the sheet start up to be able to open sheet without it auto closing.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi mbarron
    Thank you that worked quite will.
    I was a little worried at first because my workbook is a xlsb and opens to a userform. But that’s not the case. Very Cool thank youÖÖ.

    Updated Comment:
    It works great but is there a way to use either a message or dialog that will let user no how many uses are left.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Keep in mind that the user can bypass the Workbook_Open event procedure by holding down Shift when opening the workbook.


  7. #7
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Keep in mind that the user can bypass the Workbook_Open event procedure by holding down Shift when opening the workbook.
    Thank you HansV
    Most of the users will probably have little to no experience, Plus Iím planning on implementing some additional safe guards

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this:
    Code:
    Private Sub Workbook_Open()
    Sheets(1).Cells(1, 1) = Sheets(1).Cells(1, 1) + 1
    If Sheets(1).Cells(1, 1) = 11 Then
     Workbooks("BOOK1.XLS").Close SaveChanges:=False
    Else
    msgbox "You can open this book " & 10-Sheets(1).Cells(1, 1) & " more times"
    End If
    End Sub

  9. #9
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you mbarron
    its seems to work good, I'll do a little more testing with it tomorrow

  10. #10
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did some further testing with some additional safe guards I put into place, everything seemed to functioned correctly. Now Iím wondering is it possible to add some additional code so that upon last opening that userform2 and userform3 will be deleted. Userformís 2 & 3 control majority of the navigation throughout workbook. Just a thought

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, in theory it's possible to do that, using code like this:

    Code:
    With ThisWorkbook.VBProject.VBComponents
    .Remove .Item("UserForm2")
    .Remove .Item("UserForm3")
    End With
    But, and this is a big but, users will have to trust programmatic access to the Visual Basic project in the workbook. If they don't - and that is the default setting - the above code will result in an error message.

  12. #12
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    Thank you..
    My Visual Basic project is password protected, I donít want to leave it vulnerable.
    Iím probably going over the deep end with a lot of safe guards; Iím just trying to see what might work best,

  13. #13
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I did some further testing and found that if I close Workbook and do not save this will stop the counting process which will allow user to continue using Workbook. Is there a way to correct this?

  14. #14
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured a way to save as it counts that will prevent user from going around by not saving, just by adding ThisWorkbook.Save
    see code example below

    Code:
    Private Sub Workbook_Open()
    Sheets(1).Cells(1, 1) = Sheets(1).Cells(1, 1) + 1
    ThisWorkbook.Save
    If Sheets(1).Cells(1, 1) = 11 Then
     Workbooks("Workbook.xlsb").Close SaveChanges:=False
     Else
    MsgBox "You can open Demo Program " & 10 - Sheets(1).Cells(1, 1) & " more times"
    End If
    End Sub

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If my logic is correct, I think you may run into a problem by adding the workbook save command after the count being increased. I think you will get the expected result at the count of 11, but not at 12 and so on.

    You may want to change the line:

    If Sheets(1).Cells(1, 1) = 11 Then

    to:

    If Sheets(1).Cells(1, 1) > 10 Then

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
  •