A macro to authorize a user to run another macro.

[abhay_547]

Hi All,

I have created an excel addin which contains some macros but i have sent the same to all my team members but i want to restrict some of the team members from using the same .i.e who are not authorized to use the above mentioned addin. So I want a macro which should first check the existence of a user's XP ID in my sql database table and then allow him to run the macro from my addin. If the user's XP ID is not present in my sql database then it should show a message that "You are not a authorized user to run this macro". I have a code with me which actually checks the existence of Windows XP user id in Excel workbook but I want a code which should check the existence of XP id in sql server table. Following is the code :

Sub Test()
Dim ws As Worksheet
Dim strSQL As String
Dim strConnection_String As String
Dim x As String
Dim strFilePathOfAuthorizedUsersFile As String
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------

'-----------------------------------------------------------
'ENTER YOUR SOURCE FILE WHERE NAMES ARE KEPT
'1) Must be an Excel file with One Column,
'2) Column header must be labeled: "NameOfAuthorizedUser"
'3) Data is a named range call "MyRange"
strFilePathOfAuthorizedUsersFile = "C:\MySourceTest.xls"
'-----------------------------------------------------------

'SQL String
strSQL = _
"SELECT NameOfAuthorizedUser " _
& "FROM myRange " _
& "WHERE " _
& "NameOfAuthorizedUser = '" & Environ("Username") & "';"

'Connection String to get data from an Excel file
strConnection_String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePathOfAuthorizedUsersFile & ";" & _
"Extended Properties=Excel 8.0;"

x = CheckForAuthorizedUser(strConnection_String, strSQL)
If x <> "" Then
MsgBox "User " & x & " Found!"
Else
MsgBox "No Authorized User Found."
End If

End Sub
Function CheckForAuthorizedUser(ByVal strConnection_String As String, ByVal strSQL As String) As String
'Creates a recordset from Excel, using filter criteria from the calling sub
'Returns a name or an empty string
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------

Dim x As Long
Dim myRecordset As ADODB.Recordset
Set myRecordset = New ADODB.Recordset

'sql string - uses module-level constants
Debug.Print strSQL

'initialize recordset and run the query
Call myRecordset.Open(strSQL, strConnection_String, CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)

'Results
If Not myRecordset.EOF Then
CheckForAuthorizedUser = myRecordset.fields(0).Value
Else
CheckForAuthorizedUser = ""
End If

'Close recordset object and release memory
If (myRecordset.State And ObjectStateEnum.adStateOpen) Then myRecordset.Close
Set myRecordset = Nothing

End Function

Thanks for your help in advance.

[abhay_547]

Hi All,

Please expedite... I already have the above (posted in earlier post) code in place which works fine with excel I just want to use the same with SQL data table and apart from that I require some thing like below.


Sub MyMacro()
If Application.Run "Test" = True Then ' I know that this is not a valid statement in VBA but I am just trying to explain the logic with this example.
'then my other macro code otherwise if it is false then Exit sub with a message "That you are not authorized to run this macro"
Application.Run "othermacro"
End if
End Sub



:(

[Ago]

Im sorry but i dont really understand what the problem is but, how safe do you need this to be?
Can those who you want to restrict from the macros read VBA code?

A very easy way that i made in a file once was to create a txt-file in the rootdirectory of the computer, and then i just did a FileExist.
You could even type a password inside that file and make the macro check the contense of the txt-file.

[abhay_547]

Hi Ago,

I can't use a text file or an excel file for storing the usernames because then I will have to supply it's path in my excel addin and in future if that path changes or that file gets deleted then none of the users will be able to use my macros which I have added in my addin because every time when they will click on a macro in my addin they will get an error as the text or excel file containing their usernames will be missing. This is the reason why I want to store my usernames in an sql database table and then want my macro to read the same usernames before allowing user to run a macro present in my addin.

The code which I have posted in my earlier post works fine with excel. I need only a small change in the same so that it works with the sql database table.

Thanks for your help in advance. :bow: