1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

What should be a simple Macro function?

Discussion in 'Business Applications' started by lilandyuk, Jan 15, 2013.

Thread Status:
Not open for further replies.
  1. lilandyuk

    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?
     
  2. Garf13LD

    Garf13LD

    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.
     
  3. lilandyuk

    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!
     
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085390

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice