Copying checkboxes

edhogan1224

New Member
Joined
Apr 18, 2013
Messages
8
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?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
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.
 

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
What check boxes are you using? Form controls or ActiveX?
 

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
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)

Code:
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

New Member
Joined
Apr 18, 2013
Messages
8
Code:
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!
 
Last edited:

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,283
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top