Code to allow Macro to write in a protected cell

  • Looking for code that I can add to my worksheet that:
    When a person inputs data in one unprotected cell, my macro is able to make the necessary calculations and write in the password protected cell. Now It does not write in it, as it is protected. Help will be greatly appreciated. Thanks JImmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Locked cells is not the issue. It is locked cells and worksheet protected. With this protect option, code can make any change. I like to set it for all worksheets in ThisWorkbook's Open event. Obviously, if you have the password, it must be correct and the 1st input.


    Code
    1. Sub Test()
    2. ActiveSheet.Protect "", userinterfaceonly:=True
    3. [B1] = [A1]
    4. End Sub
  • Thanks a lot Kenneth. I appreciate it. I'll try it out tomorrow.
    Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • I assume that my password goes in the quotations

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hello,


    In order to make modifications in a protected sheet,


    the first step is to Unprotect:


    Code
    1. ActiveSheet.Unprotect "password"


    the second step is to perform all the actions which are required


    Code
    1. ' insert your instructions to input data or formula


    and the third step is to Protect your sheet again


    Code
    1. ActiveSheet.Protect "password"


    Hope this will help

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

  • Hi Carim. That sounds easy. I can live with that.
    Someone else responded with the code:
    ActiveSheet.Protect "", userinterfaceonly:=True [B1] = [A1] I tried this but couldn't get it to work. I tried putting it in my macro and putting it on the sheet...to no avail. (obviously I did put my password in the quotations). Leaving for the morning and will respond this afternoon when I get back...Thanks...Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hi,


    With your next message ... why not posting your current macro ... to see how these instructions should be included ...:wink:

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

  • Well Carim I'm pretty good at Excel, but am new to VBA. I've had some training in Basic (many years back), which I must admit, helps me today as I do understand the concepts and lingo of programming. Recently I've been learning VBA through the painful process of hunting down examples from the net and applying them to build my macros. In the last 3 or 4 months I've struggled through, and successfully created a program that links about 25 sheets including 14 charts, which all finally work great. Now of late, I'm targeting the syntax that I've not been able to figure out on my own; so I thought that this forum would be the way to go...I was right. Soooo...as you've advised, I'm including a copy of one of my simpler macros (kind of long & structured like an amateur (me)). You've shown me the difference between a selection and active cell but I haven't yet cleaned that part up. I do include REM statements throughout my macros.
    Problem:
    I'm drawing data from my Home Sheet and static (mins & maxs) from my Default Sheet. I'm inputting them into a table in Weight Data Sheet, which in turn populates a chart I want to write to my table in Weight Data Sheet and copy data from my Default Sheet...both protected.
    Please excuse the syntax faux pas :thanx: (Pointers are welcome)
    Thanks...Jimmy


    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hello,


    Thanks a lot for your clarifications ...


    Just a couple of remarks :


    A. You should indeed review your macro to get rid of the ' Select '


    B. Regarding your specific question about Protected Sheets ...
    the basic 3-step -ogic described above is to be applied in any configuration :


    1. Unprotect
    2. Perform all the required actions (copy, paste, etc ....)
    3. Protect again

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

  • Hi Roy. Thanks for your help but this method locks everything up (near as I can figure) weather the cells are locked or unlocked. I need the versatility, to have appropriate individual cells on various sheets unlocked, as the patient will be inputting data in some of them. So I appreciate your help, but will use the Unlock; Input; Lock method that Carim, has offered. Perhaps to reduce the likelihood of the problem, that you had previously mentioned regarding the error occurrence, I could use this on individual sheets that are not altered...such as the charts. If it wouldn't be too much trouble could you change the code to signify current worksheet only? Very Much appreciated...Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hey Carim, Thanks for your help. I will be assessing all of my macros to determine; (Multiple cell ranges as Selection) and (Single cell ranges as ActiveCell.) . Going forward I'll be using the proper designations. The program works well, so the fix will be prioritized as last though LOL.
    Do you agree with the method of using userinterface code in conjunction with the 3 step protect method where applicable?
    I value your opinion.Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hey Roy...don't worry about giving me the code to set userinterface sub for individual sheets. Kenneth had previously given me that and it works
    Thanks Kenneth

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • It doesn't lock everything up, it only protects cells that have been set to be locked when you set up protection originally. What I was saying is that you need to set UserInterFace up in the WorkBook_Open event because it clears whenever the workbook is closed. But ignore it if you know best!

  • hmmm. I had certain cells set as unlocked and when I populated my workbook event with the code as you recommended and tried to add data to the unlocked cells, I couldn't change the data in the specific cells. I must be doing something wrong. BTW...If I felt that I knew best I wouldn't be asking for help. Hope my previous comment didn't offend you...LOL
    Thanks Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Sounds great Roy. That would show where I'm going wrong...but sorry, I don't see an attachment
    Jimmy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Good Day
    I received this code from RoyUK as you see above in the thread. I had trouble with this back when it was sent to me. I'm attempting it again, as my program is about ready to be completed. I was successful with it this time around and it worked perfectly. At the end of the day I emailed it out with my CDO code...ever since then It's messing up . Now when I run any part of my program, the box comes up asking me to unprotect using password, and won't let me do anything else. I input the password and it doesn't show an error but the box just comes back asking for "Unprotect Sheet password". (I'm trying to run the program...not unprotect it...LOL) I have 27 sheets that all interact with each other, so I don't think that it would be feasible to use the unprotect / perform action / protect method for every event. The method that I'm trying to make work is the one that makes the most sense. I feel I'm so close...
    This is the code I'm using:


    Code
    1. Option Explicit Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect UserInterfaceOnly:=True Next ws End Sub


    [SIZE=18px]In my "ThisWorkbook" section I have the code coupled with a couple of ComboBox's. Here is how I've inputted this code:

    Code
    1. Option Explicit Private Sub workbook_open() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect UserInterfaceOnly:=True Next ws With Sheets("Home Sheet").ComboBox1 .AddItem "Appointment (change)" .AddItem "Appointment (needed)" .AddItem "Bleeding" .AddItem "Broken Bone" .AddItem "Change Prescription" .AddItem "Chest Pain" .AddItem "Dizzy Spells" .AddItem "Getting Better" .AddItem "Getting Worse" .AddItem "Headache" .AddItem "Pain" .AddItem "Pain(Chest)" .AddItem "Prescription" .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 End Sub

    [/SIZE] [SIZE=18px] also my comboboxes are not coming up either. Must be I'm doing something wrong in the Workbook Open Event. I tried to put the code separately as two unique subs Identifying each as Private Sub workbook_open() and get 'ambiguous" error due to the 2 - workbook_open() statements Please help me...I'm so close to finishing the program Thanks So much Jimmy [/SIZE]

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • Hey RoyUK I finally figured it out. Your UserInterfaceOnly had everything I needed, but you forgot to include the "password" in Quotations between ws.Protect and UserInterfaceOnly. probably comes naturally to you as you already know so much, but not so much for me. I'm sure you just overlooked this, but please correct in the future for newbies.
    Ok works perfectly now thanks to Kenneth Hobson... as I was cruising through old threads on the topic.
    Thanks anyway Roy

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)