Copying/Clearing Information using Check Box (Form Control)

[DarrylK]

Hi I have the following conundrum:

I am creating a sheet that collects billing and shipping information and I want to use a check box that will copy the billing information to the shipping information section if the box is checked and delete the shipping information if the box is unchecked.

The simplified idea of the action is

-----------------------------------------

If (E18) on sheet.(Misc Data) = "True" Then

Copy Range C5:N11
Paste.Values C20:N26

If (E18) on sheet.(Misc Data) = "False" Then

Select F20, E22, E24, D26, I26, M26)
Clear.contents


-----------------------------------------

Assistance would be greatly appreciated!,

Have a great week!,

 

[cerfani]

Not sure the question but you can use the check box event, i dont know the exact name of the event but it would be something called like Checked, ValueChanged, etc

 

[My Aswer Is This]

Try this:
Assuming the ranges and the check box are both on sheet named:

Misc Data
And you have the Form control checkbox linked to E18 on active sheet.

​

Sub CheckBox1_Click()
If Range("E18").Value = True Then
Range("C5: N11").Copy Range("C20: N26")
Else
Range("F20, E22, E24, D26, I26, M26").ClearContents
End If
End Sub

 

[hotabae]

Not sure the question but you can use the check box event, i dont know the exact name of the event but it would be something called like Checked, ValueChanged, etc

This example can illustrate the way I usually handle form control checkboxes on a worksheet (on a basic level)~

Sub CheckBox1_Click()
MsgBox(ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value)
End Sub

And assign that macro to the checkbox (via right clicking)

Every time you click it with this code, It should display either 1 (true) or -4146 (false).
Using -- ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value -- Is super helpful to get dropDown values or checkbox values. You just have to assign the macro and it'll run on changes