select cell in range inputs value into cell in another sheet

Question

In the code below that i got working, when i select a cell in a range, an input box pops up and my input is sent to the same cell i clicked but in sheet 2. I want to take this one step further. i want to bypass the input box completely and just send the value F, and i only want to do this after i click cell b2. so cell b2 would have to work as some kind of toggle (maybe put an invisible shape over it to act as a button?)

Example 1: sheet 1, select cell B2 turns on macro, select cell in range example: D10 inputs the letter F into cell D10 on sheet 2, select cell B2 turns off macro so if i select cell D10 or any cell in that range nothing will happen anymore. it would also need to remove the value F from D10 if the cell is clicked again while the macro is on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Dim xRtn As Variant
     If Selection.Count = 1 Then
         If Not Intersect(Target, Range("D9:AS20")) Is Nothing Then
             xRtn = Application.InputBox("Insert your value please")
             Sheets("2020").Range(Target.Address).Value = xRtn

         End If
     End If
End Sub

chillin chillin · Accepted Answer · 2019-08-24T14:23:51

Untested. I'm not sure if I understood all of your objectives.

I think if you add a checkbox to your worksheet (resize and store it wherever you want; maybe in cell B2) called "Check Box 1" then the below code should work.

One way of adding a check box might be: Excel > Developer > Insert > Check Box (Form Control) (depending on your Excel version). If the Developer tab is not visible, you may need to get it to show first.

Option Explicit

Private Function GetCheckBox() As Shape
    Set GetCheckBox = Me.Shapes("Check Box 1")
End Function

Private Function IsCheckBoxTicked() As Boolean
    IsCheckBoxTicked = (GetCheckBox.OLEFormat.Object.Value = 1)
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub
    If Intersect(Target, Me.Range("D9:AS20")) Is Nothing Then Exit Sub

    If IsCheckBoxTicked() Then
        With ThisWorkbook.Worksheets("2020").Range(Target.Address)
            .Value = IIf(.Value = "F", Empty, "F")
        End With
    End If
End Sub

select cell in range inputs value into cell in another sheet

1 Answers