How do I assign macro codes to multiple checkboxes

**spamunch** · 01-11-2011, 02:37 PM

Hello, I have written my code already for my first check box. My goal is to click a checkbox, and it moves the data to the right. When Unchecked it(the macro) moves the data to where it was in the first place. Please see the code for checkbox 1 below.

Private Sub CheckBox1_Click()
Select Case CheckBox1.Value
    Case True
        ActiveSheet.Range("k6:k6").Cut
        ActiveSheet.Range("M6").Select
        ActiveSheet.Paste
    Case False
        ActiveSheet.Range("m6:m6").Cut
        ActiveSheet.Range("k6").Select
        ActiveSheet.Paste
End Select


End Sub

I have about a 1000 checkboxes. I am trying to figure out a way to tell excel that I want the same code (different cells) for all my other checkboxes without me manually writing the code over and over again. I am using Excel 2007

My next checkboxes when checked would have to move cell contents

from K:7 to m7 and then back when unchecked
from K:8 to m8
from K:9 to m9 and so forth on so on all the way to 1000

Thank you very much. Eagerly awaiting your response. I have spent half of my workday on this problem.

**wotadude** · 01-12-2011, 11:36 PM

How about a slightly different approach ............

Copy the follwing code into the sheet module in question.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' this is column A to double click for simulated checkbox action
    Const ActionCol = 1
    
    With Target
        If .Column = ActionCol Then
            Cancel = True
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
            End With
            With .Font
                    .Name = "Wingdings 2"
                    .Size = 13
            End With
            If .Value = "£" Then ' if unchecked do this
                .Value = "T"
                Cells(.Row, "K").Copy
                Cells(.Row, "M").PasteSpecial xlPasteValues
                 Cells(.Row, "K").ClearContents
            Else ' if checked or blank do this
                .Value = "£"
                Cells(.Row, "M").Copy
                Cells(.Row, "K").PasteSpecial xlPasteValues
                Cells(.Row, "M").ClearContents
            End If
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End With

End Sub

This does not require the need to set-up the checkboxes and only requires a double-click in column A of any row. Change ActionCol value from 1 to column number where you require the checkbox & double-click to ocurr

**spamunch** · 01-14-2011, 09:10 AM

Hello wotadude. This looks very promising. I just looked at it now, I am playing around with it now to see how it works, I'll come back if I have any questions.

Thanks again

**snb** · 01-14-2011, 09:22 AM

Avoid 'copy' if you can.

Private Sub CheckBox1_Click()
  if CheckBox1 Then
    ActiveSheet.Range("M6")=ActiveSheet.Range("K6").value
    ActiveSheet.Range("K6").clearcontents
  Else
    ActiveSheet.Range("K6")=ActiveSheet.Range("M6").value
    ActiveSheet.Range("M6").clearcontents
  End If
End Sub

or

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  if cells(target.row,13)="" and cells(target.row,12)<>""  Then
    cells(target.row,13)=cells(target.row,12).Value
    cells(target.row,12).clearcontents
  Elseif cells(target.row,12)="" and cells(target.row,13)<>""  
    cells(target.row,12)=cells(target.row,13).value
    cells(target.row,13).clearcontents
  End if
End Sub

**spamunch** · 01-14-2011, 09:26 AM

This is absolutely brilliant wotadude. I like doubleclicking and the checkboxes appear in the column. That is very good. However, I would prefer the end user clicks only once on the checkbox to move items and back. How do I do that?.

Even as it stands this has given me hope, I had to code 600 checkboxes by hand yesterday and it was so exhausting. How can I learn to write VBA code like the one you have above?

**spamunch** · 01-14-2011, 10:37 AM

 If .Value = "£" Then ' if unchecked do this
                .Value = "T"
                Cells(.Row, "K").Copy
                Cells(.Row, "M").PasteSpecial xlPasteValues
                 Cells(.Row, "K").ClearContents
            Else ' if checked or blank do this
                .Value = "£"
                Cells(.Row, "M").Copy
                Cells(.Row, "K").PasteSpecial xlPasteValues
                Cells(.Row, "M").ClearContents[/COLOR]            End If
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    End With

End Sub

Hello snb sorry I didn't see your post at first. Thank you for it. I'm not going to use checkboxes the way I did before. I am going to use wotadude's code as it is less time consuming than the way I did it before.

I have a question though, In the code above, how do I select, copy and move multiple cells at the same time. I tried to do it this way (using range) it won't work.

If .Value = "£" Then ' if unchecked do this
                .Value = "T"
                Range("K:N").Copy
                Range("P:R").PasteSpecial xlPasteValues
                Range("K:N").ClearContents
            Else ' if checked or blank do this
                .Value = "£"
               Range("M:O").Copy
               Range("H:J").PasteSpecial xlPasteValues
               Range("M:O").ClearContents

Please see full code in wotadude's first post. Thank you

**snb** · 01-14-2011, 11:05 AM

You didn't read the second part of my post in which no checkbox is being used. You can doubleclick any cell in a row to swicth the cells in columns K and M