Unprotect VBA project (view of macros) using <unprotect> exercise code <Protect>

  • Hi,

    I have password protected my macros from viewing, by: <Developer><Visual Basics><Tools><VBAProject Properties><Protection><Lock project for viewing><password>

    After the program runs, I need to delete a line of code in the Workbook Event Sheet. Everything works great on their own, but when together, I get the 'protected error.


    I need the protection removed

    Then execute the code

    Then protection reinstated.


    Thanks for your time and help

    JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi,


    If I understand correctly .. you are after designing a macro which can modify a macro ...


    So basically you will need to dive into the ' Extensibility Library '


    Chip has done a remarkable job at showing this rather tricky programming area :


    http://www.cpearson.com/Excel/VBE.aspx


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Wow...Looks complex. I would have thought, that it would be just an easy line of code to unprotect the project. I already have the code that works great to modify the macro. I don't have time right now. Later on, I'll search through the link and see if anything looks promising. LOL.

    Thanks JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Well I thought that it would be a no brainer, but the more I google it and search all of the forums, that is not the case. I made sure the items that you recommended for me to check out was accomplished...to no avail. I know that the password protection is easily hacked by a coding-savvy programmer. I just wanted to protect the file from people accidentally going in and messing things up. I'll just remove the developer tab from the toolbar. If someone goes that far to screw with it, there's no stopping them anyway...LOL

    Thanks for your help Carim

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • YTou cannot run code to work on the VB Editor, i.e. amend macros with Project Protection on. You cannot run code to remove that protection as you can with Sheet and WorkBook protection. So the project must be unprotected.l

  • Yeah Roy, that's what I've been seeing. Will just remove the developer tab to avert the curious...LOL

    Thanks so much for the confirmation

    JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Sorry, what do you mean by removing the Developer Tab? Do you mean take it from the Ribbon?


    If so, then it can soon be restored or the VB Editor accessed differently. Also, if I opened a workbook that messed up my Ribbon then I would be more than unhappy!!


    I can't understand why you need to remove a line of code, surely it's better to add a check into the code that would determine if that line runs or not.

  • Hi Roy. Yeah I overlooked the possibility of just working around the line of code, especially since deleting it is not feasible, due to the project protection. I just thought it would be cleaner if I got rid of it, as it's only used once before sending the program to the recipient (who has no need for it). Sometimes I can't see the forest...all of the darn trees are in the way...LOL

    Thanks for your help

    Have a great day

    JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi Roy. Thanks for the offer. Here's my issue, explained as good as I can. (too much info is better than not enough...I say...LOL).


    <Call InputBox2> is activated by ThisWorkbook event on startup, which has the sole purpose of prompting me to assign a unique password to the program and an “Alpha code word” to correlate with the password, so I can respond in case the user forgets it.

    Sub Inputbox2()

    Dim inbx As String

    Dim inbx2 As String

    Dim vbCom As Object

    Dim wb As Workbook

    Dim ans As String

    Dim SaveChanges As Boolean

    Dim FileName As String

    inbx = InputBox("Program the New Password" & vbNewLine & vbNewLine & "DEVELOPER SECTION", _

    "Assign Password BOX", "Type Here", 2000, 6000)

    'This is for the programmer to insert the password for the program

    inbx2 = InputBox("PROGRAM the ALPHA" & vbNewLine & "DEVELOPER SECTION", "ALPHA Box", _

    "Type Here", 5000, 6000)

    'This is for the programmer to insert the password for the program

    Sheets("Default Sheet").Activate

    Range("B38").Value = inbx

    'This sets the paasword from the developer into the program

    Sheets("Sign In").Activate

    With Range("A2")

    .Value = inbx2

    .HorizontalAlignment = xlCenter

    .Font.Bold = True

    End With

    With Range("A1")

    .Value = "ALPHA"

    .Font.Bold = True

    End With

    MsgBox "Hi, I will delete myself "

    ans = MsgBox("DONE" & vbNewLine & vbNewLine & "Password & Alpha set to" _

    & vbNewLine & inbx & inbx2 & vbNewLine & vbNewLine & " YES = end and shutdown" _

    & vbNewLine & " NO = Continue to app", vbYesNo)

    If ans = vbYes Then

    FileName = "BondyMT_Alpha_" & Range("A2").Value

    'Workbooks.Add

    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & FileName

    Call DeleteCodeLines

    ActiveWorkbook.Save

    ActiveWorkbook.Close

    Else

    Exit Sub

    End If

    End Sub


    It assigns the password – Saves as new name – Deletes code line <Call InputBox2> from ThisWorkbook – re-saves the program (Therefore <Call InputBox 2> is gone from the WorkBook event) – Closes the program.


    Option Explicit

    Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    With Sheets("Home Sheet").ComboBox1

    .AddItem "Appoint. (change)"

    .AddItem "Appoint. (needed)"

    .AddItem "Bleeding"

    .AddItem "Dizzy Spells"

    .AddItem "Flu / Cold"

    .AddItem "Getting Better"

    .AddItem "Getting Worse"

    .AddItem "Headache"

    .AddItem "Pain"

    .AddItem "Heart Rate"

    .AddItem "Pain(Chest)"

    .AddItem "Prescription"

    .AddItem "Sprain"

    .AddItem "Throat Sore"

    .AddItem "Vomitting"

    .AddItem "Other"

    End With

    With Sheets("Home Sheet").ComboBox2

    .AddItem "Before B-fast"

    .AddItem "After B-fast"

    .AddItem "Before Lunch"

    .AddItem "After Lunch"

    .AddItem "Before Supper"

    .AddItem "After Supper"

    .AddItem "Bedtime"

    End With

    With Sheets("Default Sheet").ComboBox3

    .AddItem "Choose Here"

    .AddItem "gmail"

    '.AddItem "yahoo"

    '.AddItem "outlook"

    End With

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

    ws.Protect "Pamela491", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True

    Next ws

    'This allows the macros to run on protected sheets

    'This next block is for developer use when adjusting sheets (to remove password)

    'For Each ws In ThisWorkbook.Worksheets

    'ws.Unprotect "Pamela491"

    'Next ws

    Application.ScreenUpdating = True

    Sheets("Sign In").Select

    'This ensures we start on the header sheet

    'Insert Call Inputbox2 below this line

    Call Inputbox2

    Call Inputbox1

    End Sub


    The renamed altered program is now sitting in my location for the user. (Thus InputBox2 is gone) so <Call InputBox1> is activated for the user to open exactly as planned, (disabling the sheets until the user confirms that the disclaimer has been read,etc...) and must use the newly assigned password.


    Option Explicit

    Sub Inputbox1()

    Dim rng1 As String

    Dim A1 As String

    'Dim rng2 As String

    Dim A2 As String

    Dim blnSignInAsGuest As Boolean

    Dim Answ

    Dim xWs As Worksheet

    Do Until Len(A1) > 0 And Len(rng1) > 0 And A1 = rng1

    'Sets inputBox (A1) to test if it matches (rng1) from Default Sheet

    'Also checks input & Password from 'Default sheet' it present

    A1 = 0

    rng1 = Sheets("Default Sheet").Range("B38").Value

    A1 = InputBox("Type in Password" & vbNewLine & "OWNER SECTION" & vbNewLine & vbNewLine & vbNewLine _

    & "For Guest with limited access" & vbNewLine & "Just hit ENTER", "PASSWORD SIGN IN BOX", "Type Here", 5300, 3500)

    'Sets values for A1 & rng1

    If Len(A1) = 0 Then

    GoTo Jump1

    End If

    'If the password A1 is left blank or hit cancel or 'X out' (go to retry or guest limited access)

    If A1 = rng1 Then

    MsgBox "Welcome you're in With Full Access" _

    & vbNewLine & vbNewLine & "AFTER YOU'VE READ THE DISCLAIMER" & vbNewLine & "Feel free to change your passcode" & _

    vbNewLine & ">>>>> Just Click OK <<<<<" & vbNewLine & vbNewLine & _

    "____THEN_____" & vbNewLine & vbNewLine & "Tap the Default Sheet tab" & vbNewLine & _

    "at the bottom of this page"

    'Tests if password from InputBox = rng1 and ('Cancel' and 'X' out) has not been hit

    Call HideWorksheets

    Sheets("Disclaimer").Activate

    Range("A1").Select

    MsgBox "PLEASE ENSURE THAT YOU READ THE TERMS" & vbNewLine & vbNewLine & _

    "Click (OK)" & vbNewLine & "Read the Terms" & vbNewLine & _

    "Click the button at the bottom" & vbNewLine & "YAHOO! START YOUR DAY"

    Exit Sub

    End If

    'Exits Sub releasing to full access of program

    Jump1:

    If A1 <> rng1 Then

    Answ = MsgBox("Sorry Password does not match" & vbNewLine & _

    "Please hit YES to Re-try" & vbNewLine & "Or hit NO to enter as Guest", vbYesNo)

    End If

    'If password is false then user has option to retry or go on to limited guest access

    blnSignInAsGuest = False

    'Sets boolean YesNo to 'NO' value for MsgBox choice at label Jump1: to retry or go on to limited access

    If Answ = vbYes Then

    GoTo Jump3

    End If

    'This assigns the 'YES" (decision to retry password) by going to label - Jump 3:

    'thus looping back to start to begin the process or trying to sign in

    If Answ = vbNo Then

    blnSignInAsGuest = True

    GoTo Jump2

    End If

    'This assigns the 'NO' (decision to retry password) to actually sign in as guest at label - Junp2:

    'reaching past the Loop command

    Jump3:

    Loop

    'Looping has completed. Either the password has been accepted or

    'the user has decided to just sign in as Guest with limited access

    Jump2:

    If blnSignInAsGuest = True Then

    A2 = InputBox("Guest/Physician Entry with limited access", _

    "GUEST BOX", "JUST HIT ENTER", 500, 5000)

    End If

    'Now the user has conceded to sign in as Guest as inputbox (A2) has stated

    'If Len(A2) = "" Or StrPtr(A2) > 0 Or A2 = 0 Then

    'This line tests that the user can input anything or nothing & hit 'Enter'

    Call DeleteSheets

    'Call DeleteButtons

    'This calls the sub routine that deletes the restricted view sheets

    MsgBox "Welcome! You're in" & vbNewLine & "Limited Access" _

    & vbNewLine & vbNewLine & ">>>>> Just Click OK <<<<<" & vbNewLine & _

    "_______THEN" & vbNewLine & "Tap any of the tabs at the bottom of the sheet", 300, 3000

    'This advises that the user in in the limited access area for viewing only

    'End If

    Sheets("Disclaimer").Activate

    Range("A1").Select

    MsgBox "Please click OK" & vbNewLine & "Then take a few minutes" & vbNewLine & _

    "and read the Disclaimer" & vbNewLine & vbNewLine & "Enjoy your" & vbNewLine & _

    "Bondy Medical Tracker"

    End Sub


    Works Great, but I want the macros to be protected. If I protect the project by: <Tools><Project Properties><Protection><Lock Properties for viewing><Password>...It won’t allow me to remove “Call InputBox2”


    As you suggested there must be a work-around that I can do. I’m having trouble with this one. Any help will be appreciated, as this is one of the very last items to be completed, on this grueling quest...LOL... that I’ve set out on.

    Sorry for the length. I figure if you want you can just ignore what you don't need.

    Thanks JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Wow...Looks complex. I would have thought, that it would be just an easy line of code to unprotect the project. I already have the code that works great to modify the macro. I don't have time right now. Later on, I'll search through the link and see if anything looks promising. LOL.

    Thanks JimmyB

    Hi,


    Looks complex ... but it is extremely powerful ...


    Indeed, you do need to ' invest ' some time and energy ...


    But you do have to know ... the reward will come !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • No doubt Carim. I did look through his site and it looked like a world of information there, that I can benefit from. Thanks so much for that! I saved the link for future help. I did what was advised at the onset of his post, set a reference to the VBA Extensibility library and of course my trust center is good, but nothing in his post helped with what I wanted. If you look at the post that I just put up (#10) you'll see my issue more in depth. I was going to kind of let my issue go by the wayside. I even set the thread as resolved, but RoyUK advised that he wants to help out. So here I am again.:S LOL

    Speaking about time and energy...I've been working through this program every day for the last year||, using my 'Basic' Programming training from the 1980's (HaHa...yeah I'm really old :whistling:), but i love the challenge), and learning from scratch LOL. With most of the help from Google. When that doesn't pull through, you (especially) and a few others from Ozgrid, have saved me:thumbup:. Now It's almost complete.

    If you check my YouTube channel, you'll see me actually running the program. I did this early on in the quest and have since made a ton of improvements (including 'screen updating') but you'll get the picture. check out my links and feel free to subscribe etc. Ooops sorry didn't mean to plug it but couldn't help myself.

    Thanks so much. You've been a GREAT help

    Have a great day;)

    JimmyB

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Jimmy


    I'm not sure how you have added your code, but you haven't used Code Tags. Using Code Tags make's it easy to read & copy code. Currently I will need to do Find & Replace after copying the code as posted to make it legible.

  • I've looked at your Inputbox2 code and the line I have added will check if a password has been created previously. I would suggest a thorough check of you code, simple things like not Selecting or Activating will make the code more efficient and often remove the need for changing ScreenUpdating.


    Code
    1. Sub Inputbox2()
    2. Dim inbx As String, inbx2 As String, ans As String, FileName As String
    3. Dim vbCom As Object
    4. Dim wb As Workbook
    5. Dim SaveChanges As Boolean
    6. ''/// this should stop the code from running unless no password has been created
    7. If Not IsEmpty(Sheets("Default Sheet").Range("B38")) Then Exit Sub