none
How to pass a cell value to another cell if a checkbox is toggled RRS feed

  • Question

  • I have created a spreadsheet where I am tracking the total amount of time completed watching training modules.

    Column D contains the length of each module. Column E is a checkbox. When the user toggles the checkbox in Column E, indicating completion the module, I would like that action to pass the value of the cell in Column D over to Column G. Then that column can be totaled as total hours watched.

    I honestly don't recall if the checkbox was created via Form or ActiveX controls. How can you tell?

    As a bonus, how would the current date be input to Column F when the Checkbox is selected?

    Thank you for your help?

    Tuesday, November 28, 2017 6:12 PM

All replies

  • Hi Dilbert,

    Thanks for visiting our forum.

    Then here we mainly focus on general issues about Excel user interface. Since your query is related to Form or ActiveX controls, I'll move your thread to the following dedicated MSDN forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Wednesday, November 29, 2017 7:36 AM
  • Hello Dilbert and Sullivan,

    >>I honestly don't recall if the checkbox was created via Form or ActiveX controls. How can you tell?

    You could try to insert a form checkbox and an ActiveX checkbox to compare with them. They have different appearance and when you hover on a form checkbox, the cursor will become a little hand but ActiveX won't.

    >> Then that column can be totaled as total hours watched.

    What do you mean this? Do you want to send value in Column E to Column G or plus original value in Column G and value in Column E? 

    For a Form checkbox, you could add below macro to a standard module and then try to assign the macro to the checkbox.

    Sub MoveDToG()
    Dim cbxName As String
    cbxName = Application.Caller
    Dim ERange As Range
    Set ERange = ActiveSheet.Shapes(cbxName).TopLeftCell
    ERange.Offset(0, 2) = ERange.Offset(0, -1)
    'use below code instead if need to calculate sum
    'ERange.Offset(0, 2) = ERange.Offset(0, 2) + ERange.Offset(0, -1)
    ERange.Offset(0, 1) = Date
    End Sub

    For an ActiveX checkbox, you could try to double click the checkbox in design mode and try to edit the click event like below code.

    Private Sub CheckBox1_Click()
    If CheckBox1.Value Then
    CheckBox1.TopLeftCell.Offset(0, 2) = CheckBox1.TopLeftCell.Offset(0, -1)
    'use below code instead if need to calculate sum
    'CheckBox1.TopLeftCell.Offset(0, 2) = CheckBox1.TopLeftCell.Offset(0, 2) + CheckBox1.TopLeftCell.Offset(0, -1)
    CheckBox1.TopLeftCell.Offset(0, 1) = Date
    End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 30, 2017 2:36 AM