The professional me!

Icon

technical thinking

Making cells Readonly in Excel using VBA

Making cells read only is the most common requirement of excel users. Here, I am explaining how to do that using VBA code. I have used MS Excel 2003 in this example.

Before you start with the code, you need to open Excel sheet & select Tools-> Macro -> Visual Basic Editor. You will see following window. Select Sheet1 and type the code:

Visual Basic Editor in MS Excel

Visual Basic Editor in MS Excel

Type following for the range of cells needs to be made read only: Following code raises message if read only cells are selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range(“A3:G4”)) Is Nothing Then
Range(“B1”).Select
MsgBox (“These cells are Readonly,So you can not Change”)
End If

Where I can apply this?

Many times we need to make some part of sheet Read only so that no one should change that part. Many times we use formula in excel sheet which never changes. You can make that particular cell read only.

Excel sheets are used to make some structures in companies. In that structure many times values are changes but format remains same and that format is used repeatedly. In that case this code is really useful.

Advertisements

Filed under: General, , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: