VBA to find the position of an Activex checkbox, copy the last checkbox and position it?

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

Hi Bruce,

Here are two of the many renaming options:

Sub CheckBoxRenamingScenarios()

  'NOTE: There will be a runtime error if "CheckBox1" or "ChecBox2" does not exist

  Debug.Print ActiveSheet.OLEObjects(1).Name
  Debug.Print ActiveSheet.OLEObjects("CheckBox1").Name
  ActiveSheet.OLEObjects(1).Name = "cb01"
  Debug.Print ActiveSheet.OLEObjects(1).Name
  
  Debug.Print
  Debug.Print ActiveSheet.OLEObjects(2).Name
  Debug.Print ActiveSheet.OLEObjects("CheckBox2").Name
  ActiveSheet.OLEObjects("CheckBox2").Name = "cb02"
  Debug.Print ActiveSheet.OLEObjects("cb02").Name
  Debug.Print ActiveSheet.OLEObjects(2).Name
  
End Sub

See the attached file that works with Active X checkboxes. It doesn't specifically answer your question, but may provide you with tools to get the job done.

Here are a few excerpts from the file:

Sub AddActiveXCheckBoxToActiveCell()
  Dim sCell As String
  
  sCell = ActiveCell.Address
  Call AddActiveXCheckBox(sCell)
End Sub



Sub AddActiveXCheckBox(sCell As String)

  Const xCheckBoxHeight = 12
  Const xCheckBoxWidth = 12

  Dim r As Range
  
  Dim xHeight As Double
  Dim xLeft As Double
  Dim xTop As Double
  Dim xWidth As Double

  Set r = Range(sCell)
  
  'Get the dimensions of the active cell
  xHeight = r.Height
  xLeft = r.Left
  xTop = r.Top
  xWidth = r.Width

  'Calculate starting point for the new 'Active X Check Box'
  xLeft = xLeft + (xWidth - xCheckBoxWidth) / 2#
  xTop = xTop + (xHeight - xCheckBoxHeight) / 2#

  
  'Add the 'Active X' Check Box
  'Link the 'Check Box' to the underlying Cell
  With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
     Left:=xLeft, _
     Top:=xTop, _
     Width:=xCheckBoxWidth, _
     Height:=xCheckBoxHeight)
        
    .Object.Caption = ""
    .LinkedCell = r.Address
    .Object.Value = False
        
  End With
  
  'Hide the contents of the Underlying Cell
  r.NumberFormat = ";;;"
  
End Sub

Lewis