Solved

vba code or alternative method to lock one column in worksheet

Posted on 2014-03-19
6
2,891 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 48

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 48

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 45

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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