PDA

View Full Version : ActiveX Checkbox VBA Code Help



Ekphoto
02-28-2017, 02:43 PM
Hello, I am new to VBA and have a question on how to code the Active X checkboxes I'm working on. I have sheet 1 with checkboxes on it and on sheet 2 I have a list of sentences in different cells. what I want to happen is when a checkbox is checked on sheet 1 I want it to take a specific sentence from sheet 2 and put it in a "Notes" box on sheet 1. That way I can compile notes with a couple checks of some checkboxes. Any help would be greatly appreciated. Thanks

SamT
02-28-2017, 04:27 PM
Right Click one CheckBox and select View Code. This will open the VBA Editor and insert the CheckBox_Click Sub outline for that Checkbox into the Editor.

In the Editor upper right will be a Drop Down Arrow, Click the Arrow and select "Change". This will insert the CheckBox_Change Sub outline for that Checkbox into the Editor.

Put the required code in that Change sub. It will Run every time the Checkbox is Changed, whether to checked or to unchecked.

Ekphoto
03-02-2017, 06:42 AM
Thank you SamT for the outline of starting the code. I will try implementing that today. Since I am new to coding in general and VBA I don't know what function to use. Can you point me in the right direction pertaining how to write the code?

SamT
03-02-2017, 07:28 AM
Coding is all about the details.

Please provide details about what the code needs to accomplish

JKwan
03-02-2017, 07:31 AM
I think this is simpler than what you are proposing.... that's just me :_). Give this a try. Create 5 checkboxes and a textbox (with Multi-line set to TRUE) - leave as default names give the checkboxes captions of your sentences and put the code in your Sheet1 module and see if this will do

Private Sub CheckBox1_Click()
Worksheets("Sheet1").TextBox1.Text = Worksheets("Sheet1").TextBox1.Text & _
" " & _
Worksheets("Sheet1").CheckBox1.Caption & _
Chr(13)
End Sub
Private Sub CheckBox2_Click()
Worksheets("Sheet1").TextBox1.Text = Worksheets("Sheet1").TextBox1.Text & _
" " & _
Worksheets("Sheet1").CheckBox2.Caption & _
Chr(13)
End Sub
Private Sub CheckBox3_Click()
Worksheets("Sheet1").TextBox1.Text = Worksheets("Sheet1").TextBox1.Text & _
" " & _
Worksheets("Sheet1").CheckBox3.Caption & _
Chr(13)
End Sub
Private Sub CheckBox4_Click()
Worksheets("Sheet1").TextBox1.Text = Worksheets("Sheet1").TextBox1.Text & _
" " & _
Worksheets("Sheet1").CheckBox4.Caption & _
Chr(13)
End Sub
Private Sub CheckBox5_Click()
Worksheets("Sheet1").TextBox1.Text = Worksheets("Sheet1").TextBox1.Text & _
" " & _
Worksheets("Sheet1").CheckBox5.Caption & _
Chr(13)
End Sub

Ekphoto
03-03-2017, 07:28 AM
Thank you for the code JKwan, Some of my sentences I'm trying to link to are rather long and do not want the whole sentence as a caption of the checkbox. SamT: what I'm trying to do is this: I have 2 sheets. On sheet 1 I have multiple checkboxes and a textbox. On sheet 2 I have a list of sentences. What I want to happen is to link a specific checkbox on sheet 1 to a specific sentence on sheet 2, and when a checkbox is checked the associated sentence is populated in the textbox on sheet 1.

JKwan
03-03-2017, 07:48 AM
here, take a look at this

SamT
03-03-2017, 01:20 PM
Private Sub CheckBox1_Change()
'Change to checked Adds Text, Change to unchecked deletes text

If Worksheets("Sheet1").CheckBox1 = True then
Worksheets("Sheet1").TextBox1.Text = Worksheets("Sheet1").TextBox1.Text & _
Worksheets("Sheet2").Range("A1").Text & Chr(13)
Else
Worksheets("Sheet1").TextBox1.Text =Replace(Worksheets("Sheet1").TextBox1.Text, _
Worksheets("Sheet2").Range("A1").Text & Chr(13), "")
End If
End Sub
Repeat for each checkbox changing the Sentence Range in each

Ekphoto
03-07-2017, 10:46 AM
Hello, Jkwan: thank you for the example, when I use your code I am able to get the checkboxes working. If it is at all possible to also include if the box is unchecked to remove the text?

SamT: Thanks for the code. I am not able to get the code working I get a Run time error '9' subscript out of range error on the first line that states if the checkbox is true. I have renamed my sheets however kept the "Sheet1" there since that is the codename of the sheet. Is there a work around for this error?

Thank you both very much!

JKwan
03-07-2017, 11:45 AM
give this a go

SamT
03-07-2017, 01:32 PM
Change "True" to "Checked"
Also see the Checkbox Properties in VBA editor.

Ekphoto
03-08-2017, 12:20 PM
Thank you, that worked perfectly. Just 1 more question, for the ScanCheckboxes sub on this line: "TextBox1.Text = TextBox1.Text & Sheet10.Cells(Right(obj.Name, 1), "A") & vbCrLf" If I create more that 10 checkboxes I get a "Run-time error 1004: application-defined or object-defined error." Lets say I had 30 some boxes on sheet 1, how would I correct this line of code?

JKwan
03-08-2017, 02:21 PM
replace with

Sub ScanCheckboxes()
For Each obj In Sheet1.OLEObjects
If obj.progID = "Forms.CheckBox.1" Then
If obj.Object.Value = True Then
TextBox1.Text = TextBox1.Text & Sheet2.Cells(Split(obj.Name, "CheckBox")(1), "A") & vbCrLf
End If
End If
Next obj
End Sub

Ekphoto
03-08-2017, 02:33 PM
Thank you so much, its working now!

SamT
03-08-2017, 04:16 PM
Obj.Name? All Obj's are Checkboxes named CheckBox#?


Sheet10.Cells(Mid(obj.Name, 9), "A") & vbCrLf"