Checkbox To Move One Cell Value Into Another Cell

[data808]

This will be a 2 part question.

1. Does anyone know how to insert checkboxes to go down a column indefinitely starting from the 3rd row? The first two rows will be the title headers. I know how to insert a check box and copy and paste more of them but I would like it to keep going down so that I don't have to keep repeating this process.

2. I will be typing financial amounts into column B. I would like column C to have the checkboxes. If the user checks the box I would like the value in column B for that row to be transferred to Column D for that row. If the user made a mistake by checking the box and then wants to uncheck it, I would like the value to go back to column B. So basically reverse the process.

Let me know if you need more info.

Thank you.

 

[Sektor]

There are (now) THREE types of checkboxes in Excel:
1) Forms Controls
2) ActiveX Controls
3) Recently added checkbox (Insert -> CheckBox) for Office 365 users (Beta Channel). This is actually TRUE/FALSE value in a cell, but Excel renders checkbox.

 

[Sektor]

1. Select cells you want to insert checkboxes into.
2. Run macro below (change the text of the checkbox to whatever you need).

Sub InsertCheckBoxes()
  Dim cell As Range
  Dim chk As CheckBox
  For Each cell In Selection
    With cell
      Set chk = Sheet1.CheckBoxes.Add(.Left, .Top, .Width, .Height)
    End With
    chk.Text = "Press me!"
  Next
End Sub

 

[kevin9999]

Another option which doesn't use checkboxes, but achieves the same result? Put the code in the sheet code module of the sheet of interest (right-click the tab name, select View Code & paste the code into the window that appears).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Target.Row > 2 And Target.Column = 3 Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If Target = "" And Target.Offset(, -1) <> "" Then
            With Target
                .Value = Chr(214)
                .Font.Name = "Symbol"
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .Offset(0, -1).Cut .Offset(0, 1)
            End With
        Else
            With Target
                .Offset(0, 1).Cut .Offset(0, -1)
                .ClearContents
            End With
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[kevin9999]

I should have pointed out that the code works whenever you select a cell in column C from row 3 down:

 

[data808]

I should have pointed out that the code works whenever you select a cell in column C from row 3 down:

This actually works really well. The only thing I am afraid of is that it might be a little too easy to select the cell. With a checkbox I won't be able to select the cell with just tabbing on my keyboard. If I can't find a way to do it with checkboxes then I will definitely consider this option because this is pretty cool as well. Thank you very much for the suggestion.

 

[kevin9999]

Understood
I guess an alternative could be a double-click event trigger. The following code does the same thing as post #4 - but requires a double-click for it to work.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    cancel = True
    If Target.Row > 2 And Target.Column = 3 Then
        If ActiveCell = "" And ActiveCell.Offset(, -1) <> "" Then
            With ActiveCell
                .Value = Chr(214)
                .Font.Name = "Symbol"
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .Offset(0, -1).Cut .Offset(0, 1)
            End With
        Else
            With ActiveCell
                .Offset(0, 1).Cut .Offset(0, -1)
                .ClearContents
            End With
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[data808]

Understood
I guess an alternative could be a double-click event trigger. The following code does the same thing as post #4 - but requires a double-click for it to work.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    cancel = True
    If Target.Row > 2 And Target.Column = 3 Then
        If ActiveCell = "" And ActiveCell.Offset(, -1) <> "" Then
            With ActiveCell
                .Value = Chr(214)
                .Font.Name = "Symbol"
                .Font.Bold = True
                .HorizontalAlignment = xlCenter
                .Offset(0, -1).Cut .Offset(0, 1)
            End With
        Else
            With ActiveCell
                .Offset(0, 1).Cut .Offset(0, -1)
                .ClearContents
            End With
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Brilliant. This is much better.

Do you know of any effect or something that I could enter into the cell by default to intuitively cause the user to know that they can double click this cell? I was thinking of a shading or raised effect to make the cell look like a button or enter "[ ]" to simulate a checkbox. What do you think?

 

[kevin9999]

One way to get a "raised effect" in a cell is to colour it grey to start with, then add a thick black border to the bottom & right edges of the cell only. Makes it look like a button.

 

[data808]

One way to get a "raised effect" in a cell is to colour it grey to start with, then add a thick black border to the bottom & right edges of the cell only. Makes it look like a button.

Will try this out tomorrow and get back to you. Thank you very much for all your help.