Set device limit for excel with vba

[sekhar03]

I've an Excel sheet and I want to set limit on number of computers that the workbook can be accessed.
Every time the Excel workbook opened my vba script should collect the computer name. If the computer name is not part of earlier list then auto increment. Limit should be exceeded if excel accessed in more than 6 computers.
Here, my intention is to Excel sheet can be accessed only in 6 different computers.

Can we have vba script for the same.?

 

[Rijnsent]

Yes, you can do that with VBA. However, this is a forum with volunteers, this question sounds like a paid job. From the forum rules: If you have an urgent need, check the Consulting Services page.

 

[sekhar03]

Thank you for your response. This is not a paid job. However, I've figured out the way to limit computers. Thanks.

Yes, you can do that with VBA. However, this is a forum with volunteers, this question sounds like a paid job. From the forum rules: If you have an urgent need, check the Consulting Services page.

 

[Joe4]

However, I've figured out the way to limit computers.

Would you mind posting your solution?
That way if others have similar questions, and find this thread, it might help them out.

 

[sekhar03]

This can be achieved by using Remove duplicate option. Every time user opens the file, we'll be storing their computer name in a sheet (which is hidden to user) and remove duplicates to make sure we are not single computer name multiple times. We'll be counting the rows after removing duplicates. If the count is beyond our specified limit then we can display a message saying limit exceeded and close the active workbook.

Here, we are protecting the sheet and and VBA code from user. Here is the code.

This code might sound silly but this level of security will be enough for my purpose. There might be better solutions as well. If you have any such, post here.

Private Sub Workbook_Open()

'Getting computer name
ComputerName = Environ("computername")

'Getting user name
UserName = Environ("username")

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 'Getting Last row from active sheet
Cells(LastRow + 1, 1) = ComputerName 'Storing the computer name in the last cell
ActiveSheet.Range("A1:A100").RemoveDuplicates Columns:=1, Header:=xlNo ' Removing Dups
usedRows = ActiveSheet.UsedRange.Rows.Count
'MsgBox (usedRows)
If usedRows >= 1000 Then
MsgBox ("Device limit exceeded: " & usedRows)
ActiveWorkbook.Close savechanges:=False

End If

End Sub