none
Restrict access to edit texboxes & select values in Comboboxes, based on user profile RRS feed

  • Question

  • Dear All,

    Request your guidance to restrict the edit access for specific user to certain cells in the user form.

    These specific users should get to enter the details in the user form, when they initially fill in the data, however, once they click on SUBMIT, then thereafter they should only have READ ACCESS to the user form.

    Eg. I have a user form section to Enter Data (with combination of Text Boxes and Combo Boxes) and another section for SEARCHING the data, which can eventually be edited.

    I want to enable a feature or write appropriate VBA, so that the specific users are allowed to enter DATA in the data capture section but once they click on SUBMIT, thereafter they should only be able to search the data but not update anything.

    Thanks in advance.


    Thanks & Regards, Shiju Cherian

    Thursday, October 1, 2015 11:24 AM

Answers

  • You need a place to record things, so insert a worksheet named "Record" (hide it if you want) and then use code like this - you need to know the usernames as used by Excel to have this work


    Private Sub UserForm_Initialize()
        Dim vRet As Variant
        
        'Disable all the entry controls on the form
        Me.TextBox1.Enabled = False
        Me.ListBox1.Enabled = False
        Me.ComboBox1.Enabled = False
        
        vRet = Application.Match(Application.UserName, Worksheets("Record").Range("A:A"), False)
        
        
        If IsError(vRet) Then 'The user has not entered data yet, so turn on some controls
            If Application.UserName = "Shiju Cherian" Then
                Me.TextBox1.Enabled = True
                Me.ListBox1.Enabled = False
                Me.ComboBox1.Enabled = False
            End If
            If Application.UserName = "Bernie Deitrick" Then
                Me.TextBox1.Enabled = False
                Me.ListBox1.Enabled = True
                Me.ComboBox1.Enabled = True
            End If
        End If
        
    End Sub

    Private Sub cmdSubmit_Click()  'Submit button
        Dim vRet As Variant
        vRet = Application.Match(Application.UserName, Worksheets("Record").Range("A:A"), False)
        'If the user has not been recorded yet, add their name to the list
        If IsError(vRet) Then Worksheets("Record").Cells(Rows.Count, "A").End(xlUp)(2).Value = Application.UserName
    End Sub

    Thursday, October 1, 2015 4:19 PM

All replies

  • You need a place to record things, so insert a worksheet named "Record" (hide it if you want) and then use code like this - you need to know the usernames as used by Excel to have this work


    Private Sub UserForm_Initialize()
        Dim vRet As Variant
        
        'Disable all the entry controls on the form
        Me.TextBox1.Enabled = False
        Me.ListBox1.Enabled = False
        Me.ComboBox1.Enabled = False
        
        vRet = Application.Match(Application.UserName, Worksheets("Record").Range("A:A"), False)
        
        
        If IsError(vRet) Then 'The user has not entered data yet, so turn on some controls
            If Application.UserName = "Shiju Cherian" Then
                Me.TextBox1.Enabled = True
                Me.ListBox1.Enabled = False
                Me.ComboBox1.Enabled = False
            End If
            If Application.UserName = "Bernie Deitrick" Then
                Me.TextBox1.Enabled = False
                Me.ListBox1.Enabled = True
                Me.ComboBox1.Enabled = True
            End If
        End If
        
    End Sub

    Private Sub cmdSubmit_Click()  'Submit button
        Dim vRet As Variant
        vRet = Application.Match(Application.UserName, Worksheets("Record").Range("A:A"), False)
        'If the user has not been recorded yet, add their name to the list
        If IsError(vRet) Then Worksheets("Record").Cells(Rows.Count, "A").End(xlUp)(2).Value = Application.UserName
    End Sub

    Thursday, October 1, 2015 4:19 PM
  • Hi Shiju Cherian,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel Developer:

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, October 2, 2015 6:28 AM