Solved

vba code or alternative method to lock one column in worksheet

Posted on 2014-03-19
6
3,196 Views
Last Modified: 2014-03-20
Hi  Experts Excel 2007

How would I lock/protect column dw and leave remaining column unprotected.
0
Comment
Question by:route217
6 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39939577
Hi,

pls try

    ActiveSheet.Unprotect ' EDITED
    Cells.Locked = False
    Columns("DW:DW").EntireColumn.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Open in new window

Regards
0
 

Author Comment

by:route217
ID: 39939699
Experts

I am assuming that I add this to the worksheet...module.
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39939711
Run it once from a normal module
Sub Macro
    ActiveSheet.Unprotect
    Cells.Locked = False
    Columns("DW:DW").EntireColumn.Locked = True
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Open in new window

Regards
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:senthilkumarsb
ID: 39939719
Rgonzo1971's solution is good.

Named range will help you when your sheet dynamic. when you add new column or delete a column before the particular column your required column name will change but named range remain same we can access the column using the named range.

for ex:- create named range in column "DW" as "MyColumn"

ThisWorkbook.Names("MyColumn").RefersToRange.EntireColumn.Locked = True
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 39939955
Or you can do something as simple as this which moves the cursor to column dv.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 127 Then
    Beep
    Cells(Target.Row, Target.Column - 1).Select
End If
    
End Sub

Open in new window

0
 

Author Comment

by:route217
ID: 39940264
Thanks for the feedback experts testing.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 32
highlight duplicate entry 16 31
What is format f12.8 for a CSV file 6 42
Excel VBA When using VLookup 6 29
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now