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 th
same to all my team members but i want to restrict some of the team members fro
using the same .i.e who are not authorized to use the above mentioned addin. S
I want a macro which should first check the existence of a user's XP ID in m
sql database table and then allow him to run the macro from my addin. If th
user's XP ID is not present in my sql database then it should show a messag
that "You are not a authorized user to run this macro". I have a code with m
which actually checks the existence of Windows XP user id in Excel workbook bu
I want a code which should check the existence of XP id in sql server table
Following is the code :

I have the below code so far :

Sub Test() Dim ws As Worksheet Dim strSQL As String Dim strConnection_String A
String Dim x As String Dim strFilePathOfAuthorizedUsersFile As Strin
'----------------------------------------------------------- 'NOTE: Require
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.
Library (or higher) '----------------------------------------------------------
'----------------------------------------------------------- 'ENTER YOUR SOURC
FILE WHERE NAMES ARE KEPT '1) Must be an Excel file with One Column, '2) Colum
header must be labeled: "NameOfAuthorizedUser" '3) Data is a named range cal
"MyRange" strFilePathOfAuthorizedUsersFile = "C:\MySourceTest.xls
'----------------------------------------------------------- 'SQL String strSQ
= _ "SELECT NameOfAuthorizedUser " _ & "FROM myRange " _ & "WHERE " _
"NameOfAuthorizedUser = '" & Environ("Username") & "';" 'Connection String t
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 MsgBo
"User " & x & " Found!" Else MsgBox "No Authorized User Found." End If End Su
Function CheckForAuthorizedUser(ByVal strConnection_String As String, ByVa
strSQL As String) As String 'Creates a recordset from Excel, using filte
criteria from the calling sub 'Returns a name or an empty strin
'----------------------------------------------------------- 'NOTE: Require
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.
Library (or higher) '----------------------------------------------------------
Dim x As Long Dim myRecordset As ADODB.Recordset Set myRecordset = Ne
ADODB.Recordset 'sql string - uses module-level constants Debug.Print strSQ
'initialize recordset and run the query Call myRecordset.Open(strSQL
strConnection_String, CursorTypeEnum.adOpenForwardOnly,
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText) 'Results If No
myRecordset.EOF Then CheckForAuthorizedUser = myRecordset.fields(0).Value Els
CheckForAuthorizedUser = "" End If 'Close recordset object and release memory I
(myRecordset.State And ObjectStateEnum.adStateOpen) Then myRecordset.Close Se
myRecordset = Nothing End Function


Please expedite... I already have the above mentioned code in place which work
fine with excel I just want to use the same with SQL data table and apart fro
that I require some thing like below.

Example code required by me :

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

Thanks for your help in advance.