Unlock entire row - condition

[singh.jms]

Hi...want to know.. If any cell in Column "A" has any text, then unlock that entire row for editing. For example, if cell "A3" has text, so All I want that the entire row 3 to be unlocked for editing.

 

[Jon von der Heyden]

Hi and welcome to the board.

Try;

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] UnlockRange()
    [COLOR="Blue"]Dim[/COLOR] rngFilter [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]Set[/COLOR] rngFilter = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    
    [COLOR="Blue"]With[/COLOR] rngFilter
        .Parent.Unprotect Password:="password"
        .Parent.AutoFilterMode = [COLOR="Blue"]False[/COLOR]
        .AutoFilter field:=1, Criteria1:="<>"
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Locked = [COLOR="Blue"]False[/COLOR]
        .End(xlDown).Offset(1).EntireRow.Delete
        .AutoFilter
        .Parent.Protect Password:="password"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[singh.jms]

Hey Thanks for the quick reply..

That doesn't seems to be working... I am new to writing VBA codes. I just copy pasted your suggested codes. I don't know why? If you could help more on that.

 

[Jon von der Heyden]

You need to paste it into a standard module:

Open the VBE (ALT + F11)
Insert a new module (on the menu bar Insert > Module)
Paste the code in the new module and close the VBE.
To run hit ALT+F8 > UnlockRange > Run

Do you encounter any specific error? And if so can you tell me what line it breaks on?

 

[singh.jms]

Dear Von.. by any chance can I share my worksheet

 

[Jon von der Heyden]

Look in my signature to download an addin that allows you to display your sheet on the board using HTML.

 

[singh.jms]

It says " you don't have permission to access"

 

[Jon von der Heyden]

Please PM Richard Schollar with your email address and ask him to send you the addin.

 

[singh.jms]

Dear Von, Let me ask in different way..
If I add any text in a cell in Column "A" , then unlock that entire row for editing. For example, if I add any text in cell "A13", then the entire row 13 will get unlocked for editing.

 

[Jon von der Heyden]

Ok, so I now assume that you want to unlock the row automatically as you input data in column A, right?

Right-click the sheet tab > view code > paste this code into the code pane that opens. Close the VBE again:

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]If[/COLOR] Application.Intersect(Target, Columns(1)) [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR] [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR]
    Me.Unprotect Password:="password"
    Target.EntireRow.Locked = [COLOR="Blue"]False[/COLOR]
    Me.Protect Password:="password"
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

Assumes that column A is set to unlocked, and that all other remaining columns are locked.