CheckBox?

[rkcim]

I have a scenario where I click a button on Sheet1 which adds a row of data. At the end of the row is a checkbox which is created in VBA, inside the click button event.

My question is on each new checkbox added to the form I would like the following code to appear in them according to there name

Private Sub CheckBox1_Change()
Call OptionCheck(CheckBox1)
End Sub
.
.
.
.
.
Privat Sub CheckBoxN_Change()
Call OptionCheck(CheckBoxN)
End Sub

Where N is the number of the checkbox ie CheckBox7

This way I wouldn't have to go into each checkbox when they are created, and put the code: Call OptionCheck(CheckBoxN) in the CheckBox_Change event.

Can this be done?

 

[Greg Truby]

In a hurry, so I've not the time to customize this, but I did something similar recently. Hopefully this will give you ideas on how to do what you're after. This WB creates a menu based on a list in the WB which can vary in size. Since you can't pass parameters via the OnAction property of a menu item, I had to write a bunch of tiny subroutines, each a numbered variant, to assign to the menu items. The conditional compiling statements are 'cause some users are on XL97 - strip 'em out if you can.


Anyway, I hope this yields some ideas...

<font face=Courier New>#<SPAN style="color:#00007F">Const</SPAN> booUseNewCode = VBA6</FONT>

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
<SPAN style="color:#007F00">'...Other Code...</SPAN></FONT>
<font face=Courier New>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rngMenu.Cells.Count
        MyAddControlToPopup AutoLoadMenu, newMenuItem, i, rngMenu.Cells(i).Value, _
                "AutoLoad" & i
        #<SPAN style="color:#00007F">If</SPAN> booUseNewCode <SPAN style="color:#00007F">Then</SPAN>
            BuildAutoLoads i, vbc
        #<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

<font face=Courier New>
<SPAN style="color:#007F00">'==============================================================================</SPAN>
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> BuildAutoLoads(<SPAN style="color:#00007F">ByVal</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, vbc <SPAN style="color:#00007F">As</SPAN> VBComponent)
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">' Would have preferred to put the code for this in the Workbooks Open method</SPAN>
<SPAN style="color:#007F00">' However, virus scanning software kept flagging this file as infected if</SPAN>
<SPAN style="color:#007F00">' the Open() method included an InsertLines method.  However, just moving</SPAN>
<SPAN style="color:#007F00">' it here seems to fool the virus scanning software into letting it go.</SPAN>

<SPAN style="color:#007F00">' This procedure is called by the Open method and is designed to always</SPAN>
<SPAN style="color:#007F00">' ensure that there are corresponding "AutoLoadX()" subroutines for each</SPAN>
<SPAN style="color:#007F00">' menu option that would be created by adding items to the Customer List</SPAN>
<SPAN style="color:#007F00">' All of this nonsense could be avoided if one could set a menu's</SPAN>
<SPAN style="color:#007F00">' Action property in manner that permitted passing a parameter.</SPAN>

    #<SPAN style="color:#00007F">If</SPAN> booUseNewCode <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> strCode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
        strCode = vbCrLf & "Sub AutoLoad" & Trim(Str(x)) & "()" & vbCrLf
        strCode = strCode & vbTab & "AutoLoad " & x & vbCrLf
        strCode = strCode & "End Sub"
        vbc.CodeModule.InsertLines vbc.CodeModule.CountOfLines + 1, strCode
    #<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Example of Subroutine created by above code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AutoLoad1()
    AutoLoad 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> AutoLoad2()
    AutoLoad 2
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Regards,

 

[Greg Truby]

Forgot to say:
Though it's implied in the comments in the code, Sub BuildAutoLoads() is in a Standard Module - not the WB's module.

And this bit of code is near the top of the WB_Open() procedure...

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> vbp <SPAN style="color:#00007F">As</SPAN> VBProject, vbc <SPAN style="color:#00007F">As</SPAN> VBComponent
    </FONT>
<font face=Courier New>    #<SPAN style="color:#00007F">If</SPAN> booUseNewCode <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> vbp = Me.VBProject
        <SPAN style="color:#00007F">Set</SPAN> vbc = vbp.VBComponents("basXL7512Autoload")
    #<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
</FONT>

 

[herilane]

An alternative approach would be to use checkboxes from the Forms toolbar, and link them to cells (for example, in a hidden column in the corresponding row). Then use the Worksheet_Change event to respond to checkbox checking/unchecking.

 

[rkcim]

I am still baffled. Basically the hole idea is not to have to edit code. That way every thing is streamlined, as well as accidently adding code where its not supposed to go, creating problems and slowing down the user.

Each time I click on Update( a button on Sheet1 ) the click event for that button would have this pseudo code in it:

Private Sub Update_Click()
Insert row of data
then add a checkbox at the end of that row
add this code : Call DoSomething(CheckBoxN) into the change event of the
corresponding CheckBoxN
End Sub


That way each time I click on a checkbox the DoSomething function is called

Any Ideas? I was a little confused on adapting Greg Truby's and herilane's reply to my problem and if they would work?

 

[herilane]

Let me make my idea a bit clearer then

What I was proposing originally was that you'd use checkboxes from the Forms toolbar instead of checkboxes from the Controls toolbox. Then link them to a cell in the worksheet, using something like

ActiveSheet.CheckBoxes.Add(...).LinkedCell = "$G$7"

Then use the Worksheet_Change event to call the DoSomething macro.
However it appears that when a checkbox changes its linked cell, that's not picked up by the Worksheet_Change event (which I think is bizarre... but maybe I'm missing something here).

So here's an alternative idea... Again, I would use checkboxes from the Forms toolbar. Assign the same macro to all of them:

Private Sub Update_Click()
ActiveSheet.CheckBoxes.Add(...).OnAction = "DoSomething"
End Sub

Then CallDoSomething can check which checkbox triggered it, and act accordingly:

Public Sub DoSomething()
Dim strCaller As String
strCaller = Application.Caller     'the name of the checkbox
'do something with the caller checkbox
End Sub

Let me know if this makes sense!

 

[rkcim]

I forgot to mention that I don't know how many checkboxes there will be. They are only added to Sheet1 when a user clicks a company name in a ComboBox. The list of names in the ComboBox changes as well.

What I am trying to get around is not have to insert any code into any one of the CheckBox_Click events because I only want to operate on the excel user interface not have to go into editing code each time I create a checkbox. Also the checkboxes are sorted with there corresponding company(row).

Any Ideas?

 

[herilane]

With the solution I've proposed, you don't need to edit code for each checkbox. The first bit of code I posted goes into the sub that creates the checkbox, and the second bit goes into your DoSomething, which you already have. There is no Checkbox_Click event involved.

 

[Greg Truby]

RKCIM,

I definitely like Herilane's approach better than what I originally suggested.

I played with it a just bit and was quickly able to add a Forms Checkbox to the WorkSheet with "scalable" code using this approach. Your original post indicated that the checkbox is going onto the Worksheet but then you say

...on each new checkbox added to the form...

Assuming you are using "form" as a synonym for "worksheet" and did not intend to indicate a UserForm, Herilane's code should work nicely for your problem. If you've tried implementing it and are having problems, post back.

Herilane - Application.Caller - didn't know that one. Thanks