I was immensely helped by folks such as RomperStomper, TMS, JosephP, SHG and many more whom I can't thank enough in getting some checkboxes to facilitate creating a chart; now I am running into a problem trying to upgrade the previous work and am afraid I need genius the likes of them, once again...
Optimally what I want to do is to create a new checkbox and position it. All checkboxes are ActiveX controls.
The workbook simply imports a text file which has a person's or several people's name(s) in it along with their performance metrics. When it encounters a new name not already on a sheet called "Data" which is a database of historical information, it locates the next available area horizontally, creates a table, names the table after that student's ID number and places the data in the appropriate columns. The basic structure of this sheet is that each individual has their own table.
On another sheet which i use to chart any individual's, or combination of individuals', metrics are checkboxes (the afore cited ActiveX items), one for each person. What I would like to do if possible is to get the "identity" and location of the last checkbox (they are laid out in a column), copy that last checkbox and paste it to a position "just a bit" under the last one. Hopefully it will also be copied along with the small code block that is always the same for every checkbox, hopefully then I can name the new checkbox based on the numeric sequence that the others are in (ckb01, ckb02, ckb03,...), then give that checkbox the name of the student as it's caption.
So, I need to
1) determine the last check box, which I can do with the code you all have provided me with -
For Each obj In Worksheets("Chart").OLEObjects
If obj.progID = "Forms.CheckBox.1" Then
'is it a Checkbox?
iTest = iTest + 1
End If
Next obj
2) Determine that checkbox's name, which is a simple matter of if it is #7 then it is "ckb07"
3) Somehow assign the new checkbox the next sequential name (In the example, "ckb08")
4) Pray that when the checkbox is created, the code for it can magically appear - Given our example, on the sheet named "Chart", the following block to be added:
Private Sub ckb08_Click()
Call BTP_Click
End Sub
Parenthetical note - Perhaps I can change this so that whenever sheet "Chart" is changed, i check to see if it was a checkbox and if so then "Call BTP_Click" is called... Might make the problem of magically creating code to appear go away...
5) Locate that new check box relative to the last checkbox's position by somehow getting that last checkbox's position, spacing down by a constant factor and assigning that checkbox to that location.
Am I biting off way too much? I can continue to do this for each instructor individually, but if it could be automated it would be one less PITA to mess with, and I feel rather certain everyone can relate to that!
Best Regards,
Bruce
Bookmarks