Blocking from Moving

[Nasser]

Hi,

I have used some Form Controls and ActiveX Controls in my application using excel / VBA. I want to block my control from being moved. The user needs only to manipulate them and does not have to move them from their origine location.

I have also made some network diagrams using Shapes. Is it possible to block them from getting moved as well?

Cheers,
Nasser.

[HansV]

You can protect the sheet. There are two steps to the process.

1) Select the cells that the user should be able to edit.
Activate the Format Cells dialog (Ctrl+1).
Activate the Protection sheet.
Clear the 'Locked' check box.
Click OK.

2) Protect the sheet. You can do this interactively or using code.

Interactively: Tools | Protection | Protect Sheet... in Excel 2003 or before, or Review tab > Protect Sheet in Excel 2007 or later.

With VBA, you can protect the sheet in such a way that you can still modify locked cells in your code. This is best done in the Workbook_Open event procedure in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_Open()
  Worksheets("MySheet").Protect UserInterfaceOnly:=True
End Sub

(You can have more code in the Workbook_Open event procedure, of course)

[Nasser]

Hi Hans,

1- Using the code: i did not get this point. I put in the the editor the same code but nothing is happening. My sheet is still unprotected and an error is coming.
2- Interactively: here my sheet is protected that's fine but the user can still open the editor, thing which i don't want. How can i protect my sheet from both sides, worksheet as well as the program.
3- Is it the protect the entire workbook if it contains many worksheets. No need of repeating the same procedure many times for a couple of worksheets within the workbook.

Many Thanks,
Nasser.

[HansV]

Hello Nasser,

The code that I posted will only work if you place it in the ThisWorkbook module:

x138.png

It won't work if you place it in a standard module or in a worksheet module.

If you want to prevent users from seeing the Visual Basic code, you can protect it with a password:
- Activate the Visual Basic Editor.
- Select Tools | VBAProject Properties...
- Activate the Protection tab.
- Tick the 'Lock project for viewing' check box.
- Enter a password in the Password box, and the same in the Confirm password box.
- Click OK.
- Save the workbook.
Next time you open the workbook, you will only be able to view the code after providing the password, so make sure you don't forget it!

x139.png

Protecting the workbook as a whole has a different function - users won't be able to insert, delete or move worksheets. If you want to protect more than one sheet, you must do so one by one. This can be done in a loop:

Code: Select all

Private Sub Workbook_Open()
  Dim wsh As Worksheet
  For Each wsh In Worksheets
    wsh.Protect UserInterfaceOnly:=True
  Next wsh
End Sub

This code is a replacement for the Workbook_Open event procedure I posted higher up in this thread. It belongs in the ThisWorkbook module.