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
Code to allow Macro to write in a protected cell
-
JimmyB -
September 10, 2019 at 1:37 AM -
Thread is marked as Resolved.
-
-
-
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.
-
Thanks a lot Kenneth. I appreciate it. I'll try it out tomorrow.
Jimmy -
I assume that my password goes in the quotations
-
Hello,
In order to make modifications in a protected sheet,
the first step is to Unprotect:
the second step is to perform all the actions which are required
and the third step is to Protect your sheet again
Hope this will help
-
-
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 -
Hi,
With your next message ... why not posting your current macro ... to see how these instructions should be included ...:wink:
-
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...JimmyCode
Display MoreSub PopulateWeight() Dim Count Dim Row Dim LastCell Dim FirstCell Dim Last As Long Sheets("Home Sheet").Select Range("F3").Select ActiveCell.FormulaR1C1 = "=Today()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'Converts date formula to date value on Home Sheet '*****Weight***** Sheets("Home Sheet").Select Range("F3").Select Selection.Copy Sheets("Weight Data").Select Call Col001_FirstBlankCell.Column1_FirstBlank ActiveSheet.Paste Selection.Borders.LineStyle = xlNone 'Copies date to Weight Data Sheets("Home Sheet").Select Range("C3").Select Selection.Copy Sheets("Weight Data").Select Range("A1").Select ActiveSheet.Paste Selection.Font.Size = 16 ActiveCell.Interior.Color = vbYellow Selection.HorizontalAlignment = xlCenter Selection.WrapText = True Selection.Font.Bold = True Sheets("Home Sheet").Select Range("C3").Select Selection.Copy Sheets("Weight Chart").Select Range("A1").Select ActiveSheet.Paste Selection.Font.Size = 20 ActiveCell.Interior.Color = vbYellow Selection.HorizontalAlignment = xlCenter Selection.WrapText = True Selection.Font.Bold = True 'Copies patients' name from Home Sheet to Weight Data & Weight Chart Sheets("Home Sheet").Select Range("B23").Select Selection.Copy Sheets("Weight Data").Select Call Col002_FirstBlankCell.Column2_FirstBlank ActiveSheet.Paste Selection.Interior.ColorIndex = 0 Selection.Borders.LineStyle = 0 Sheets("Default Sheet").Select Range("C20").Select Selection.Copy Sheets("Weight Data").Select Call Col003_FirstBlankCell.Column3_FirstBlank ActiveSheet.Paste Selection.Font.Size = 16 'Copies weight target to Weight Data With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = True .ReadingOrder = xlContext End With Sheets("Home Sheet").Select 'Goes to Home Sheet for next command Range("F22").Select Range("F22:H22").Merge ActiveCell.Font.Size = 20 ActiveCell.Interior.Color = vbYellow ActiveCell.Interior.Color = vbBlue ActiveCell.Interior.Color = vbRed ActiveCell.Font.Color = Black ActiveCell.Interior.Color = vbYellow ActiveCell.FormulaR1C1 = "ENTERED" ActiveCell.Font.Size = 24 ActiveCell.Font.Size = 20 ActiveCell.Font.Color = vbRed ActiveCell.Font.Bold = True ActiveCell.HorizontalAlignment = xlCenter 'This Flashes to Show the patient that the data has been entered in Weight Data sheet Sheets("Weight Data").Select Last = Sheets("Weight Data").Cells(Rows.Count, "A").End(xlUp).Row If Last > 115 Then Call RngDelWT End If 'This decides if there are 115 rows of data & what to do Sheets("Home Sheet").Select End Sub
-
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 -
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
-
-
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 -
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 -
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 -
Have a look at the structure of my example workbook. You should be able to manually add data to unlocked cells, locked cells should be changed by the macro
-
-
Sounds great Roy. That would show where I'm going wrong...but sorry, I don't see an attachment
Jimmy -
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:CodeOption 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:CodeOption 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]
-
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 letting me know.
-
You're welcome
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!