check and uncheck macro

[jd64361]

How can i use the checkbox to use a different macro when it's checked
or
unchecked? In this example: i want to copy paste from one location if
is unchecked and from a different location if is check and I uncheck
the checkbox.
thank you
Pamela

 

[Dave Peterson]

I'm guessing that the checkbox is placed on a worksheet, right?

What kind of checkbox did you use? A checkbox from the Forms toolbar or a
checkbox from the control toolbox toolbar?

If it's a checkbox from the Forms toolbar:

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range
Dim CBX As CheckBox

Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

If CBX.Value = xlOn Then
Set RngToCopy = ActiveSheet.Range("a1:b3")
Else
Set RngToCopy = ActiveSheet.Range("x1:z3")
End If

Set DestCell = ActiveSheet.Range("d1")

RngToCopy.Copy _
Destination:=DestCell
End Sub



If it's a checkbox from the control toolbox toolbar:

Option Explicit
Private Sub CheckBox1_Change()
Dim RngToCopy As Range
Dim DestCell As Range

If Me.CheckBox1.Value = True Then
Set RngToCopy = Me.Range("a1:b3")
Else
Set RngToCopy = Me.Range("x1:z3")
End If

Set DestCell = Me.Range("d1")

RngToCopy.Copy _
Destination:=DestCell
End Sub

 

[jd64361]

I'm guessing that thecheckboxis placed on a worksheet, right?

What kind ofcheckboxdid you use?  Acheckboxfrom the Forms toolbar or acheckboxfrom the control toolbox toolbar?

If it's acheckboxfrom the Forms toolbar:

Option Explicit
Sub testme()
    Dim RngToCopy As Range
    Dim DestCell As Range
    Dim CBX AsCheckBox

    Set CBX = ActiveSheet.CheckBoxes(Application.Caller)

    If CBX.Value = xlOn Then
        Set RngToCopy = ActiveSheet.Range("a1:b3")
    Else
        Set RngToCopy = ActiveSheet.Range("x1:z3")
    End If

    Set DestCell = ActiveSheet.Range("d1")

    RngToCopy.Copy _
        Destination:=DestCell
End Sub

If it's acheckboxfrom the control toolbox toolbar:

Option Explicit
Private Sub CheckBox1_Change()
    Dim RngToCopy As Range
    Dim DestCell As Range

    If Me.CheckBox1.Value = True Then
        Set RngToCopy = Me.Range("a1:b3")
    Else
        Set RngToCopy = Me.Range("x1:z3")
    End If

    Set DestCell = Me.Range("d1")

    RngToCopy.Copy _
        Destination:=DestCell
End Sub

Hi Dave,
Can you guide me more please.
My checkbox is number 11 in a spreadsheet, in a tab name May.

the first task is to copy c11 to c17 and paste special as a value if I
check the box, the second option is if I uncheck the box go and copy
b29 to b35 and paste it in c11 to c17.
yes is a ceck box from the developer tab under form controls.

How do I enter the code that you gave me?

Thank you!!!

 

[Dave Peterson]

I bet you used the ActiveX version of the checkbox--it's the one you see in the
developer's tab (unless you show more controls (IIRC)).


Option Explicit
Private Sub CheckBox11_Change()
Dim RngToCopy As Range
Dim DestCell As Range

If Me.CheckBox1.Value = True Then
Set RngToCopy = Me.Range("c11")
Set DestCell = Me.Range("c17")
RngToCopy.Copy
DestCell.pastespecial paste:=xlpastevalues

Else
Set RngToCopy = Me.Range("b29:b35")
Set DestCell = Me.Range("c11")

RngToCopy.Copy
DestCell.pastespecial paste:=xlpastevalues

'or not pasting values:

RngToCopy.Copy _
destination:=destcell


End If



End Sub