Software

Ask something
Discussion Locked

microsoft excel protection and macros

0 helpful
13 replies

by snuffy smif

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
How can I use a macro to unprotect specific items in a worksheet and alter them, then re protect the items without unprotecting the whole workheet. I want to make a user friendly worksheet that can be updated by anyone but is protected from inadvertent alterations to the basic structure. The sheet I'm working on is graphics based and requires macros that allow color changes to objects in the sheet. The macros are assigned to buttons or hotspots to make a user friendly way to update the condition of mechanical equipment and is intended to be used by employees with limited knowlege of excel. I am fairly adept at using Lotus123r5 for this purpose but our company is asking us to start using Excel for this purpose and I am having trouble with macro development and assignment to buttons and or hotspots. Plus, the previously mentioned protection issues. Any help would be really appreciated.

13 replies

by it_cowboy

To unprotect individual cells, highlight them and go Format > Cells, click on the Protection Tab and uncheck the 'locked' box.

To write your macro, just go Tools > Macro > Record New Macro and record the keystrokes you want your macro to perform.

I'd say you want one macro to unprotect the cells for input, the other macro to apply the protection again. You will need to do this in conjunction with Protecting the Worksheet.

For Example:
Sheet should be protected before you begin.

Macro 1
Unprotect Worksheet (Tools > Protection > UnProtect Sheet).
Select Cells and uncheck 'locked' box.

Macro 2
Select Cells and check 'locked' box
Protect Worksheet.

To add some buttons, click on View > Toolbars > Forms.

Attach your macros to the buttons.

Good Luck!

by it_cowboy

just realised, you will need to protect the sheet again...

Macro 1
Unprotect Worksheet (Tools > Protection > UnProtect Sheet).
Select Cells and uncheck 'locked' box.
Protect Worksheet.

Macro 2
Unprotect Worksheet (Tools > Protection >UnProtect Sheet).
Select Cells and check 'locked' box
Protect Worksheet.

by snuffy smif

Thanks, Greg. I think that what you suggested may work but I need to be able to automate the data entry process as well incorporating the whole thing in one "Entry" macro. This requires the ability to have one macro "unlock" specific cells then point to a cell and wait for the entry of information then move on to the next cell in a sequence of cells. Then upon completion of the sequence have the macro "re-lock" the cell protection. I know it's a long and convoluted macro but it will allow access to the "unlocked" cells only during the acutual operation of the macro and at no time will anyone be able to change the basic worksheet graphics or text. This is necessary because of user inexperience. Can you help with this?

by kbarry

Greg mentioned the simplest method. To do all of the things that you want to do will require some time in VBA. Play around with the resord macro feature. Seeing the syntax develop, and using controlled tests, will allow you to create the spreadsheet you want. Another alternative is to create a form that pops up when the user tries to edit a cell. Then have the cells dynamically linked to a hidden sheet that records the entries to the form. That will allow you to protect everything the user can see, and give you the flexability with the data.

Have fun with it! :)
Hope this helps.

by Member 22879628

I agree with L. Cid, you are going to need a little design time in order to fulfill your objective and make it user friendly. Another idea?consider an event based macro. The user can access an entry point by double-clicking the cell in question. You can place code in the worksheet ?BeforeDoubleClick? event that calls a Function which checks some type of list or case statement for cells that are valid for entry. You can capture the cell address to a variable based on their current selection. If the cell is valid, as Greg described, otherwise give a MsgBox indicating the cell is not valid for entry. You can use the ?SelectionChange? worksheet event to reverse the process and reprotect the cell once their input is complete. Capturing the cell address in the previous step will help with this. Hope this helps?

by sankulpune

Hello snuffy smif,

1) Using Format > Cells > Protection, un-lock all the cells in the worksheet.
2) Lock only those cells that you want to protect.
3) Using Tools > Protection > Protect Sheet, protect the sheet. Remember the Password('SSK' for example).
4) In the SelectionChange Event of the WorkSheet, paste the code:
'----
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
If InputBox("Type Password") <> "SSK" Then
MsgBox "Locked !"
Else
ActiveSheet.Unprotect "SSK"
End If
Else
ActiveSheet.Protect "SSK"
End If
End Sub
'----

I have successfully tried this could and would be happy to help you implement it.

Your early response will be highly appreciated.

With best personal regards,

SanganakSakha,
Your Friend In Need.

by parier

Why fuss with protecting and unprotecting all that when you can make a simple form in access to all the things you want users to fill out. You can also link your Access form to the excel spreadsheet you are currently using. This was nobody can edit anything and all the data is neat and by setting up before hand, the formatting is to your liking. If you like I can create it, send the excel spreadsheet and I will start it up and email it to you and we can do it together.

eparisek@mtga.com

This discussion is closed to new replies.