Referencing ActiveX Check box In Module

[owen4512]

Hi all,

I'm looking to reference a ActiveX Check box in a module but can't seem to make this work. I have two checkboxes - "checkbox22" & "Checkbox23" . I have created a macro that will allow the user to send a snippet of a range on sheets"Templates"). The selected range is currently hidden unless the checkbox value is True. So once the user sends the email I looking to set the value of the checkbox back to false so that the range is hidden again. I cant use the forms control checkbox as I have created a private sub on the activeX checkboxes. Here's what I've tried (by the way, the email functions works perfectly It's just setting the checkbox value back to false);

Sub email_Addition()

   Dim Outlook As Object
   Dim msg As Object
  
   Set Outlook = CreateObject("outlook.application")
   Set msg = Outlook.createitem(0)

   Dim rng As Range
    Set rng = Sheets("Templates").Range("A10:O10").SpecialCells(xlCellTypeVisible)
    
            msg.Subject = "Dealer Addition - "
            msg.HTMLBody = "<p><font face='Calibri'>Hi,</p>" & _
                                "<p></p>" & _
                                "<span>Please can you add the below to the dealer tracker? </span>" & RangetoHTML(rng) & _
                                "<p></p>" & _
                                "<p>Thanks</p>" & _
            msg.display

'Option 1
CheckBox22 = False
CheckBox23 = False

'Option 2 - I've tried creating another macro & Private sub and calling them using the below methods
Application.Run "CheckBox_deselect"
Call .CheckBox_deselect

End Sub

Thanks in advance

 

[Fluff]

Assuming the checkboxes are on the "Templates" sheet, is that code in the "Template" sheet code module?

 

[owen4512]

Assuming the checkboxes are on the "Templates" sheet, is that code in the "Template" sheet code module?

Apologies, I should have explained a little better. I have created a new module for the above macro and the checkboxes are on a tab labelled ("Additions"). On the additions tab is where the user will input the data and this is carried onto the "templates" sheet with a simple "='Addition(s)'!C7" for example. So in this example there's no changes required on the "templates" sheet. I'm simply using this tab to help with capturing the data to send in the email. The checkbox will hide the data on the "additions" tab so I'm looking to set the value of checkbox22 & checkbox23 on the additions tab to False at the end of the above macro. I hope this clarifies any misunderstanding.

 

[Fluff]

In that case you need to do it like

Sheets("Additions").CheckBox22.Value = False

Check the sheet name is correct, as you have spelt it to different ways in your post.

 

[owen4512]

In that case you need to do it like

Sheets("Additions").CheckBox22.Value = False

Check the sheet name is correct, as you have spelt it to different ways in your post.

that's done the trick. thank you very much

 

[Fluff]

You're welcome & thanks for the feedback