Unlocking Cells

  • Thread starter Thread starter lj
  • Start date Start date
L

lj

Hi, I'd like to know if there's a simple way to unlock or lock cells.
I'm not sure if "Lock" is the correct term here. For example, if you
have a formula that has C$14. Is there an easy way to unlock (remove
the "$") from the formula or to ad it without manually doing it?
Thanks for your help!

Lisa
 
You can use a macro to change the reference style (absolute vs relative), but
you can also do it manually.

Select the portion of the formula (just a single cell reference or the entire
formula if you want) and hit the F4 key to cycle through all 4 options.
 
Thanks, this is helpful. What I'm trying to do is to change a whole
group of cells from relative to absolute. Is there a way to highlight
a whole group of cells and do that? How do I use a macro to do this?
 
First, each reference has 4 posibilities:

$A$1 (both column and row are absolute references)
$A1 (column is absolute, row is relative)
A$1 (column is relative, row is absolute)
A1 (both are relative)

So here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next Cell
End Sub
Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next Cell
End Sub
Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next Cell
End Sub
Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
End If
Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8 (to see
the macro dialogs), pick the one you want and run it.




Just select the range to fix and run Gord's macro for the reference style you
want.
 
First, each reference has 4 posibilities:

$A$1  (both column and row are absolute references)
$A1   (column is absolute, row is relative)
A$1   (column is relative, row is absolute)
A1    (both are relative)

So here are 4 different macros from Gord Dibben:

Option Explicit
Sub AbsoluteCol()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula _
                (Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
        End If
    Next Cell
End Sub
Sub Absolute()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula _
                (Cell.Formula, xlA1, xlA1, xlAbsolute)
        End If
    Next Cell
End Sub
Sub AbsoluteRow()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula _
                (Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
        End If
    Next Cell
End Sub
Sub Relative()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula _
                (Cell.Formula, xlA1, xlA1, xlRelative)
        End If
    Next Cell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
Then you'll have to select the range of cells to fix and then hit alt-F8 (to see
the macro dialogs), pick the one you want and run it.

Just select the range to fix and run Gord's macro for the reference styleyou
want.







--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks, this is perfect!!
 
Thanks, this is helpful. What I'm trying to do is to change a whole
group of cells from relative to absolute. Is there a way to highlight
a whole group of cells and do that? How do I use a macro to do this?
Click to expand...

Where possible, I use 'replace' to do this.
 
Back
Top