Use checkboxes to convert formulas to values in multiple cells

[Rainmanne]

I've got a spreadsheet where I enter some amount in one currency and it's converted automatically into another using a web linked exchange rate in the formula. I then want to lock the results converting the formula into value by checking a checkbox in the same row. So I need to link the cells I nee to lock to each checkbox nd then assign a macro, which will convert the formulas in the linked cells into values when the checkbox is checked.

So I am going to use a macro something like this to convert the formulas into values

Sub LockValues()

Option Explicit

Dim rng As Range
    For Each rng In Selection
        If rng.HasFormula Then
            rng.Formula = rng.Value
        End If
    Next rng
End Sub

Where I need to defined rng as cells linked to a particular checkbox.

1. How can I linked cells to checkbox and assign a macro automatically? The checkboxes are at the end of each row so I can just linked all cells in the row before a checkbox.

2. How can I define rng as linked cells to a particular checkbox?

Thaks a lot!

 

[bobsan42]

You can link a range to checkbox, but on clicking the CheckBox the first Cell in the range will get a TRUE or FALSE value.
If your checkboxes are properly positioned, you don't need linking.
Try this code in a public module:

Option Explicit

Sub assignMacroToCheckboxes()
    Dim chb As CheckBox
    With ActiveSheet
        For Each chb In .CheckBoxes
            chb.OnAction = "lockValues"
        Next chb
    End With
    Set chb = Nothing
End Sub

Sub LockValues()
    Dim ws As Worksheet
    Dim rng As Range, Target As Range
    Set ws = ActiveSheet
    Set Target = ws.Range(ws.Shapes(Application.Caller).TopLeftCell.Address(0, 0))
    Set rng = ws.Range("A" & Target.Row)
    If Target.Column > 2 Then
        Set rng = rng.Resize(1, Target.Column - 1)
    End If
    rng.Formula = rng.Value
    Set ws = Nothing
    Set rng = Nothing
    Set Target = Nothing
End Sub

The first procedure runs once to assign the second to all checkboxes on clicking.

 

[Rainmanne]

You can link a range to checkbox, but on clicking the CheckBox the first Cell in the range will get a TRUE or FALSE value.
If your checkboxes are properly positioned, you don't need linking.
Try this code in a public module:

Option Explicit

Sub assignMacroToCheckboxes()
    Dim chb As CheckBox
    With ActiveSheet
        For Each chb In .CheckBoxes
            chb.OnAction = "lockValues"
        Next chb
    End With
    Set chb = Nothing
End Sub

Sub LockValues()
    Dim ws As Worksheet
    Dim rng As Range, Target As Range
    Set ws = ActiveSheet
    Set Target = ws.Range(ws.Shapes(Application.Caller).TopLeftCell.Address(0, 0))
    Set rng = ws.Range("A" & Target.Row)
    If Target.Column > 2 Then
        Set rng = rng.Resize(1, Target.Column - 1)
    End If
    rng.Formula = rng.Value
    Set ws = Nothing
    Set rng = Nothing
    Set Target = Nothing
End Sub

The first procedure runs once to assign the second to all checkboxes on clicking.

Thanks so much! It works perfectly .