Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: A macro to authorize a user to run another macro.

    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 :
    HTML 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.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

  3. #3
    Join Date
    Sep 2009
    Posts
    79

    A macro to authorize a user to run another macro.

    Yes, I have posted it from my end over there as well so that I can get the answer asap.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Yes, I understand why you have posted there too, and that's fair enough: you want the best answer as quickly as possible.

    But from my point of view it's a little bit different:
    I allocate x amount of time each day to freely help people on online forums such as this one. I don't want to spend y amount of time trying to answer a problem for someone when their question has already been answered elsewhere: I would rather spend that time working on providing an answer to a problem which will help someone! As such, I think it is polite to include cross-post links so people can easily check if the problem has been solved elsewhere.

    Ken Puls sums this up really nicely here:
    A message to forum cross posters | Excelguru.ca

    Just something to bear in mind...

  5. #5
    Join Date
    Sep 2009
    Posts
    79

    A macro to authorize a user to run another macro.

    Hi Colin,

    That's true and that's why I follow a practice where in if I get answer of certain post in one of the forum then i post the answer on the other forum as well that it's answered now.

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    That's true and that's why I follow a practice where in if I get answer of certain post in one of the forum then i post the answer on the other forum as well that it's answered now.
    In my opinion that doesn't entirely cover the issue. I think the links should be added as soon the cross posts are created, and each forum you post in should be given some time (perhaps a day) to respond before you post somewhere else.

    For example, on this one, you hadn't answered back to say it was solved and I was still trying to help you:
    http://www.dbforums.com/microsoft-ex...s-toolbar.html

    And in the meantime two people spent their time simultaneously answering your question:
    How to assign different face id to macros in a toolbar - MrExcel Message Board
    http://www.vbaexpress.com/forum/showthread.php?p=210431

    Some people feel so strongly about this that some forums (not this one) have rules that x-posts must be added to the thread straight away. Anyway, this isn't worth getting bogged down on. It's just a suggestion....

  7. #7
    Join Date
    Sep 2009
    Posts
    79

    A macro to authorize a user to run another macro.

    Ok. I will Ensure this from next time.

  8. #8
    Join Date
    Sep 2009
    Posts
    79

    A macro to authorize a user to run another macro.

    Hi,

    Please expedite.....

Posting Permissions

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