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.

Facts:

The checkbox is placed on a worksheet, named May

A checkbox from the Forms toolbar


My checkbox is number 11 (checkbox11)

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 check box from the developer tab under form controls.



Thank you
Pamela

Hi,

Form Checkboxes allow linking the checkbox to a cell. That cell will
contain True or False depending on whether the box is checked or not.
Suppose the linked cell is A1 then

If Sheets("Sheet1").[A1] = TRUE then
'your code here
Else
'more code here
End If

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

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.

Facts:

The checkbox is placed on a worksheet, named May

A checkbox from the Forms toolbar


My checkbox is number 11 (checkbox11)

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 check box from the developer tab under form controls.



Thank you
Pamela

There's really no advantage to the added indirection of linking to a
cell when you can check the checkbox status directly:

If Sheets("Sheet1").Checkboxes("Checkbox11").Value = xlOn Then
'your code here
Else
'more code here
End If


There are a couple of potential disadvantages - e.g., changing the value
in the linked cell changes the checkbox status, but doesn't fire the
assigned macro, among others...


In article ,
Shane Devenshire wrote:

Hi,

Form Checkboxes allow linking the checkbox to a cell. That cell will
contain True or False depending on whether the box is checked or not.
Suppose the linked cell is A1 then

If Sheets("Sheet1").[A1] = TRUE then
'your code here
Else
'more code here
End If

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

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.

Facts:

The checkbox is placed on a worksheet, named May

A checkbox from the Forms toolbar


My checkbox is number 11 (checkbox11)

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 check box from the developer tab under form controls.



Thank you
Pamela

On Dec 30, 10:14*pm, JE McGimpsey wrote:
There's really no advantage to the added indirection of linking to a
cell when you can check the checkbox status directly:

* *If Sheets("Sheet1").Checkboxes("Checkbox11").Value = xlOn Then
* * * 'your code here
* *Else
* * * 'more code here
* *End If

There are a couple of potential disadvantages - e.g., changing the value
in the linked cell changes the checkbox status, but doesn't fire the
assigned macro, among others...

In article ,
*Shane Devenshire wrote:



Hi,

Form Checkboxes allow linking the checkbox to a cell. *That cell will
contain True or False depending on whether the box is checked or not. *
Suppose the linked cell is A1 then

If Sheets("Sheet1").[A1] = TRUE then
* *'your code here
Else
* *'more code here
End If

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

" wrote:

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.

Facts:

The checkbox is placed on a worksheet, named May

A checkbox from the Forms toolbar

My checkbox is number 11 (checkbox11)

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 check box from the developer tab under form controls.

Thank you
Pamela- Hide quoted text -

- Show quoted text -

the if sheets line is debuging any idea why?

In article
,
Pantera wrote:


the if sheets line is debuging any idea why?

What's the error?

Do you have the correct name of both your worksheet and your checkbox?

Check your other thread, too.

wrote:

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.

Facts:

The checkbox is placed on a worksheet, named May

A checkbox from the Forms toolbar

My checkbox is number 11 (checkbox11)

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 check box from the developer tab under form controls.

Thank you
Pamela

--

Dave Peterson

Hi Pamela,

To use a checkbox to trigger different macros depending on whether it's checked or unchecked, you can use the following steps:

1. Right-click on the checkbox and select "Format Control" from the menu.
2. In the "Format Control" dialog box, go to the "Control" tab.
3. In the "Cell link" field, enter a cell reference where the checkbox state will be stored. For example, you can enter cell A1.
4. Close the "Format Control" dialog box.
5. Press Alt + F11 to open the Visual Basic Editor.
6. In the Project Explorer window, double-click on the worksheet where the checkbox is located (in your case, the "May" worksheet).
7. In the code window that opens, enter the following code:
   
   
   Formula:

   Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If Target.Value = True Then
            'Code to run when checkbox is checked
            Range("C11:C17").Value = Range("C11:C17").Value 'Copy and paste as values
        Else
            'Code to run when checkbox is unchecked
            Range("C11:C17").Value = Range("B29:B35").Value 'Copy and paste as values
        End If
    End If
End Sub 

8. Save the workbook and close the Visual Basic Editor.

What this code does is it checks the value of cell A1 (which is linked to the checkbox) every time a change is made on the worksheet. If the value is True (i.e. the checkbox is checked), it will execute the code to copy and paste from one location. If the value is False (i.e. the checkbox is unchecked), it will execute the code to copy and paste from a different location.