Move cell content to another worksheet using checkbox

Donna Edwards · Mar 16th 2017

Hi, I am really new to Excel coding and do not know where to start with this so was praying someone could help me?!

On Worksheet 1 (RISK CHECKLIST) I have a list of Risks, and a checkbox against each risk in Column B. If the checkbox in Column B is ticked, I want to move the corresponding number in Column A to Worksheet 2 (RISK ASSESSMENT) Column A

For example:-

Worksheet 1
B8 and B10 checkbox ticked
Copies cell data from A8 and A10

Worksheet 2
Pastes data into cell A8 and A9

I have attached the spreadsheet to help explain things further (please note I still need to add checkboxes on RISK CHECKLIST from B12 onwards)

Please help me - I have been trying to do this for three days now and am losing the will to live!!

Thanks everyone

**pike** · Mar 17th 2017

Re: Move cell content to another worksheet using checkbox

Hi Donna ,
First remove a merged cells for the workbook and the checkboxes
the in RISK ASSESSMENT workbook us the activate sheet to populate the columns

Code

Option Explicit


Private Sub Worksheet_Activate()
    Dim rngList As Range, rngItem As Range, lngrow As Long
    Range("A8:B17").Value = vbNullString
    Set rngList = Sheets(1).Range("B8:B84")
    lngrow = 7
    For Each rngItem In rngList
        If rngItem.Value = "a" Then
            lngrow = 1 + lngrow
            Cells(lngrow, 1).Resize(1, 2).Value = VBA.Array(rngItem.Offset(0, -1).Value, rngItem.Offset(0, 1).Value)
        End If
    Next
End Sub

Display More

and in the RISK CHECKLIST workbook use the change event to toggle the tick

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("B8:B84")) Is Nothing Then
            Target.Font.Name = "Marlett"
                If Target = vbNullString Then
                    Target = "a"
                Else
                    Target = vbNullString
                End If
        End If
End Sub

Display More

and remember never use merged cells and remove them!!

**Carim** · Mar 17th 2017

Re: Move cell content to another worksheet using checkbox

Hello pike :wink:

Just noticed the always-annoying-double-post ...

http://www.ozgrid.com/forum/showthread.php?t=203346

Cheers

**pike** · Mar 17th 2017

Re: Move cell content to another worksheet using checkbox

Thanks I'll close this thread.

Move cell content to another worksheet using checkbox

Participate now!

Share

Users Viewing This Thread