Copying checkboxes

[edhogan1224]

I want to have a checkbox that, when checked, copies the row to another sheet. These macros are fairly abundant online but i have several thousand rows of data across 31 sheets. so obviously, doing a macro for each checkbox seperately would be incredibly time consuming. is there any way i can copy and paste the checkbox so that the row it copies stays relative to the checkbox and they arent all linked to the same row as the first?

 

[nuked]

If you assign the macro to the control and then copy it, all replicas should continue to look at the same macro. You could then use application.caller to get the object that called the macro and find the location of it for use generically in the code.

 

[edhogan1224]

Using a check box to copy a row of information into another sheet

I was looking at this, this is basically what i am looking for. is this guy (post #2) using the same method you describe? ive gone beyond my knowledge in excel by this point so if you could kind of dumb things down for me id appreciate it Thanks!

 

[nuked]

What check boxes are you using? Form controls or ActiveX?

 

[edhogan1224]

What check boxes are you using? Form controls or ActiveX?

I havent used any yet, but i was thinking Form Controls.

 

[nuked]

If you're using form controls, and, your row-heights are the standard 15 then this might help.

(There's also probably a much better way to do it, but other experts will have to chip-in)

Option Explicit
Sub CheckBox_Click()
Dim chk As Shape
Dim lRowNumber As Long
Set chk = Worksheets("Sheet1").Shapes(Application.Caller)
lRowNumber = Round((chk.Top + chk.Height) / 15, 0)
If chk.OLEFormat.Object.Value = 1 Then
  'use lRowNumber as you wish - this is the row number that the checkbox is in
    
End If
End Sub

 

[edhogan1224]

Option Explicit
Sub CheckBox_Click()
Dim chk As Shape
Dim lRowNumber As Long
Set chk = Worksheets("Sheet1").Shapes(Application.Caller)
lRowNumber = Round((chk.Top + chk.Height) / 15, 0)
If chk.OLEFormat.Object.Value = 1 Then
  'use lRowNumber as you wish - this is the row number that the checkbox is in
    
End If
End Sub

So i can assign this to a checkbox and then copy it however many times i need it and still stay relative to the row corresponding to the checkbox? Or is this just the control you mentioned earlier? I assigned this to a Form Control checkbox and i get an error saying

"Cannot run the macro "file name, macro name, etc." The macro may not be availiable in this workbook or all macros may be disabled"

I double checked and all macros are allowed and Ive checked off to trust access to the VBA Project. Im probably just dumb and im missing something really basic, sorry!

 

[nuked]

I would add a checkbox. Assign a new macro, and paste the code above between Dim chk as Shape, and End if. Then copy and paste the checkbox on the sheet as many times as you want and the macro assignment should remain. And yes, this should then return a relative row reference where indicated in the code, depending on which one you've clicked.