Consulting

Results 1 to 6 of 6

Thread: VBA Excel - Use UserForm even if the sheet is protected

  1. #1
    VBAX Regular
    Joined
    Jan 2021
    Posts
    14
    Location

    VBA Excel - Use UserForm even if the sheet is protected

    Hello! I just want to ask if can I use a macro or UserForm even if the sheet is protected or should I just leave the sheet unprotected? I have an UserForm with a CRUD function, can I use it even if the sheet is protected? If yes, how can I do that? Can someone give me an idea? Thank you!

  2. #2
    VBAX Expert
    Joined
    Apr 2005
    Posts
    868
    Location
    If protection interferes with anything, at the beginning of the code, unprotect, run your macro and at the end protect again.
    Lots of examples on this forum on how to do that.
    for instance


    Sheets("Sheet1").Unprotect Password:="myPassword"
    
    
        'Your code here
    
    
    Sheets("Sheet1").Protect Password:="myPassword"

  3. #3
    VBAX Regular
    Joined
    Jan 2021
    Posts
    14
    Location
    Quote Originally Posted by jolivanes View Post
    If protection interferes with anything, at the beginning of the code, unprotect, run your macro and at the end protect again.
    Lots of examples on this forum on how to do that.
    for instance


    Sheets("Sheet1").Unprotect Password:="myPassword"
    
    
        'Your code here
    
    
    Sheets("Sheet1").Protect Password:="myPassword"
    Oh. So I need to put the password every time I'm going to do something with the sheet? OKay thank you!

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,127
    Please, do not quote !!

    Avoid 'protection' in Excel: it serves no purpose.
    A thoroughly designed structure of data in a worksheet, structure of presentation in another worksheet and structure of User Interaction (Userform) is vital & crucial.

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,688
    Location
    Quote Originally Posted by snb View Post
    Please, do not quote !!

    Avoid 'protection' in Excel: it serves no purpose.

    A thoroughly designed structure of data in a worksheet, structure of presentation in another worksheet and structure of User Interaction (Userform) is vital & crucial.
    1. Quote only when necessary and only what is necessary - not the whole entire post

    2. Protecting a WS and unchecking "Select Unlocked Cells" allows the Tab key to move to next input cell, providing a better user exerience

    3. Depending on complexity, a UserForm may provide an easier to use and more robust application
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,308
    You can avoid the repeated unprotect/protect operations by using UserInterfaceOnly:=True as part of the Protect line.
    It stays in force until the workbook is closed or until another Protect operation cancels it.
    You could for example place it in the workbook_open event:
    Private Sub Workbook_Open()
    Sheets("Sheet1").Protect Password:="myPassword", userinterfaceonly:=True
    End Sub
    This allows vba to alter a sheet but not the user.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

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