What should be a simple Macro function?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

lilandyuk

Thread Starter
Joined
Jan 15, 2013
Messages
2
Hi there,

I've been reading loads of forum posted regarding excel macro's, i've been trying for days now to manipulate one of them into my own project with no avail.

this one should be easy and possibly a repeat of another but please would someone be able to shine a light on this one for my i'll be forever endebted

Try a row of data, once complete i want to click a macro button at the side and lock the row that i've just typed.

Kind of like a bit of validation.

here's what i've gotten

Sub Macro2()
'
' Macro2 Macro
'

'
If Range("O4").Value = "Locked" Then
Range("A4:M4").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("O4").Value = "Unlocked"
Range("A5").Select
End If

If Range("O4").Value = "Locked" Then
Range("A4:M4").Select
Selection.Locked = False
Selection.FormulaHidden = True
Range("O4").Value = "Locked"
Range("A5").Select
End If

End Sub


Help?
 
Joined
Apr 17, 2012
Messages
455
By default all cells are locked.
Locking and unlocking will not work unless you protect your sheet.
You must also put your macro into sheetchange event, for it to be "auto".
It will be something like this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim row As Long
    Dim col As Integer
    Dim curRow As Range
    
    col = Columns("O").Column
    If Target.Column = col Then
        row = Target.row
        Set curRow = Range("A" & row & ":M" & row)
        If Target.Value = "Locked" Then
            ActiveSheet.Unprotect
            curRow.Locked = True
            curRow.FormulaHidden = True
            'Target.Locked = True ???
            ActiveSheet.Protect
        Else
            ActiveSheet.Unprotect
            curRow.Locked = False
            curRow.FormulaHidden = False
            ActiveSheet.Protect
        End If
    End If
End Sub
Btw, you might want to unlock column O first before triggering the macro.
 

lilandyuk

Thread Starter
Joined
Jan 15, 2013
Messages
2
WKD it works...i had to tweek it a bit and i've now got a button changing cell "O" between "Locked" and "Unlocked" thank you for the quick reply and valuable knowledge!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top