VBA code used to copy values to another place on the sheet

[darbar76528]

Greetings everyone. I have a code that i have running on two sheets of my workbook and they perform great. i am trying to implement the code into a third sheet and i cannot seem to make it work. I have included the 3 macros that i am attempting to implement. The goal is as follows:

1. With the "addcheckboxes" macro, place a checkbox starting in column F12 and continuing down column F to the end of a populated row referencing Column G.
2. When a checkbox is checked, i need for the checkbox click to copy the value of the corresponding column. For instance, if the F14 checkbox is clicked, then the values in G14:K14 would be copied up to G6:K6. Same worksheet.

In other words, if there is a checkbox in column F19 and a person clicked on it, it would copy the value of cells G19:K19 and paste the value into G6:L6

thanks for any help\assistance you can give me!

The sheet name that this code is going in to is "Sheet6" or Start Page

Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim MyLeft, MyTop, MyHeight, MyWidth As Double

Application.ScreenUpdating = False
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For cell = 2 To LRow
    If Cells(cell, "A").Value <> "" Then
        MyLeft = Cells(cell, "$C").Left
        MyTop = Cells(cell, "$C").Top
        MyHeight = Cells(cell, "$C").Height
        MyWidth = Cells(cell, "$C").Width
        ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell

Application.ScreenUpdating = True

End Sub

Sub CopyRows()
    With Sheet4.CheckBoxes(Application.Caller)
        If .Value = xlOn Then
             Sheet4.Range("L" & Sheet4.Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Sheet4.Range(.TopLeftCell.Address).Offset(, -2).Resize(, 2).Value
           .Value = xlOff
        End If
    End With
End Sub

Sub RemoveCheckboxes()
'Dim chkbx As CheckBox

ActiveSheet.CheckBoxes.Delete

'For Each chkbx In ActiveSheet.CheckBoxes
'    chkbx.Delete
'Next

End Sub

Thanks for any assistance you can offer!

 

[NoSparks]

This should do what you've requested...

Sub Insert_Checkboxes()
     Dim lastrow As Long
     Dim myCell As Range, myRng As Range
     Dim CBX As CheckBox
With ActiveSheet
    ' get row of last used cell in column
    lastrow = .Cells(Rows.Count, "G").End(xlUp).Row
    ' specify the range to have checkboxes
    Set myRng = .Range("F12:F" & lastrow).SpecialCells(xlCellTypeVisible)
End With

' prevent screen flicker and speed things up
Application.ScreenUpdating = False

' put a checkbox in each cell of myRng
    For Each myCell In myRng.Cells
        With myCell
            ' specify the click area to be the entire cell
            Set CBX = .Parent.CheckBoxes.Add _
                (Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height)
            ' specify the properties of the checkbox
            CBX.Name = "CBX_" & .Address(0, 0)
            CBX.Caption = ""         'whatever you want, "" for none
            CBX.Caption = ""                        'whatever you want, "" for none
            'CBX.LinkedCell = .Offset(0, 1).Address  'linked cell
            CBX.OnAction = "CopyRows"                'macro to run on click
        End With
    Next myCell
    
Application.ScreenUpdating = True
End Sub

Sub CopyRows()
    Dim ws As Worksheet
    Dim callersAddress As String
    
Set ws = ActiveSheet

With ws.CheckBoxes(Application.Caller)
    'MsgBox .Name
    callersAddress = Mid(.Name, 5)
    ws.Range("G6:K6").Value = ws.Range(callersAddress).Offset(0, 1).Resize(, 5).Value
    .Value = xlOff
End With
End Sub

Sub RemoveCheckboxes()
' removes checkboxes
' BUT does not clear linked cells
    ActiveSheet.CheckBoxes.Delete
End Sub

At EF you attached a workbook to your multiple postings of this request, be aware that checkboxes are not "IN" cells, they are over top of cells and will go all screwy when you filter your data so you'll want to incorporate calls to removing and inserting the checkboxes within your filtering subs.

 

[darbar76528]

Thanks for the assistance and advice at the end. However, when the copy rows macro is activated, it references sheet4 .checkboxes(application.caller). Do you have any idea how I can stop this from happening? Thanks in advance!

 

[darbar76528]

I went to the sheet 4 code and designated the sheet as activesheet. doing this stopped the error message that I was previously having, however, after the macro for adding checkboxes is run and a checkbox is checked, nothing happens. the code runs without errors and without copying to the cells in G6. any ideas? thanks!

 

[NoSparks]

Share your workbook.

 

[Fluff]

Try making the change in Red to NoSparks' code

Sub CopyRows()
    Dim ws As Worksheet
    Dim callersAddress As String
    
Set ws = [COLOR=#ff0000]sheet6[/COLOR]

With ws.CheckBoxes(Application.Caller)
    'MsgBox .Name
    callersAddress = Mid(.Name, 5)
    ws.Range("G6:K6").Value = ws.Range(callersAddress).Offset(0, 1).Resize(, 5).Value
    .Value = xlOff
End With
End Sub

 

[NoSparks]

But Fluff....

How is it possible to click a checkbox on a sheet without the sheet being active ?

Something else is going on here.

 

[Fluff]

But Fluff....

How is it possible to click a checkbox on a sheet without the sheet being active ?

That's an excellent point well made.

 

[darbar76528]

after i made the change, (sheet6) i am now getting a runtime error of 1004 which states "application-defined or object-defined error. BTW: my worksheet is 6.5 megs unzipped and approx. 1.5 megs zipped. Isn't the size limit <1 meg?

 

[darbar76528]

when debug is run the following line on the sheet4 macro (copy rows) is highlighted: "Sheet4.Range("L" & Sheet4.Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = Sheet4.Range(.TopLeftCell.Address).Offset(, -2).Resize(, 2).Value"