Solved

vba code or alternative method to lock one column in worksheet

Posted on 2014-03-19
6
3,412 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New Criteria Prototype 3 19
populate Excel dropdown via ADO and VBA 6 17
Auto Populate Day Month  2 digit Date 4 19
Manipulate Range in Excel VBA 6 24
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…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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