I have a VB Code that allows me to select multiple items from a list and have them separated by a comma. The code does not function in Protected Mode. Does anyone know how to fix this?
I have a VB Code that allows me to select multiple items from a list and have them separated by a comma. The code does not function in Protected Mode. Does anyone know how to fix this?
If a file comes up in Protected Mode it should give you an option to allow macros to run. The whole point of Protected Mode is to protect you from malicious content from untrusted sources. If you are feeling particularly invulnerable you can reconfigure Excel not to use Protected Mode.
Thank you Jazzer. I would like to run the code in Protected mode because I have adjacent cells that contain formulas that I don't want to accidentally modify.
I think you may confusing two things.
Protected Mode is a special mode in Microsoft Office that limits what you can do in a file of dubious origin.
Do you mean that you have protected a worksheet so that formulas can't be changed? This does not prevent code from running. I think we need a little more detail. What do you mean "The code does not function"?
- Does the code run?
- Does it run but do nothing?
- Does it produce error messages? If so, what do the messages say?
- Does it produce unexpected/wrong results? If so, how do the results differ from what you expect?
- Does it hang?
It might help to attach your file, and say what macro you are referring to.
Apologies for not being more clear. The VBA Code which i have below does not operate when I have the worksheet it is assigned to "Protected". I have cells locked that I don't want a user to modify but I want the VBA Code to operate. See the attached photo. The VBA Code allows me to select multiple items and have them displayed in the same cell, separated by commas. The Cells directly to the right, have formulas that calculate the values of the items selected, which I want to protect so no one deletes them BUT I want the VBA Code to function and allow me to select multiple items at the same time, but I am unable to do so when the sheet is Protected. I hope this makes sense.Attachment 642178
VBA Code:
Please Login or Register to view this content.
Last edited by 6StringJazzer; 09-20-2019 at 08:12 AM.
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
Thanks for the additional information. However, you did not answer my key question: What happens when you try to run the code? How is that different than what you expect to happen?
Which cells are protected?
There was a problem with your image attachment. Next time, try to Preview your post before adding the image tag, then preview again to confirm before sending the post.
Also, you can attach your actual Excel file, which might be a good idea in this case. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
I made an example worksheet which i have attached. When the worksheet is protected, i am only able to select one item from the list...I need to select multiple items from the list when the worksheet is protected.
Now we're getting somewhere.
The following line of code
is raising an errorPlease Login or Register to view this content.
nathanledbetter=Capture.JPG
You did not realize this because of
I am not sure why you are trapping errors here. You should not use this unless you know what error you are expecting to happen.Please Login or Register to view this content.
The way to manage this is for the code to unprotect the sheet, do its work, the reprotect it.
I have also reorganized your code a bit to make it more readable. Using ":" to putting If/Else parts on the same line gets confusing. I also strongly discourage GoTo statements and have made a small change that eliminates it.
Also I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors.
Please Login or Register to view this content.
Thank you, 6StringJazzer!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks