Obtain the checkbox linked cell address in code

[AusSteelMan]

Hi everyone,

I have some code that removes a range of checkboxes (which were inserted using some insert checkbox code). The checkboxes are removed but the linked cells are not cleared. I am trying to modify the code so that the linked cell is cleared. I have made a change that will clear the linked cell, but ONLY if the linked cell is where the checkbox is located. For my current application of checkboxes this is fine, but for practice I am trying to change the code so the linked cell can be anywhere. I think I am close, but just can't quite get it.

Here is the original insert code for reference:

Sub insertCheckboxes_InColumns()
'Found via Chandoo.org at:
'http://www.terminally-incoherent.com/blog/2008/09/04/excel-adding-checkboxes-the-easy-way/
 
  Dim myBox As CheckBox
  Dim myCell As Range
 
  Dim cellRange As String
  Dim cboxLabel As String
  Dim linkedColumn As String
 
  cellRange = InputBox(Prompt:="Cell Range", _
    Title:="Cell Range")
 
  linkedColumn = InputBox(Prompt:="Linked Column", _
    Title:="Linked Column")
 
  cboxLabel = InputBox(Prompt:="Checkbox Label", _
    Title:="Checkbox Label")
 
  With ActiveSheet
    For Each myCell In .Range(cellRange).Cells
      With myCell
        Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
          Width:=.Width, Left:=.Left, Height:=.Height)
 
        With myBox
          .LinkedCell = linkedColumn & myCell.Row
          .Caption = cboxLabel
          .Name = "checkbox_" & myCell.Address(0, 0)
        End With
 
        .NumberFormat = ";;;"
      End With
 
    Next myCell
  End With
End Sub

Here is the code modified to clear the linked cell when at checkbox cell:

Sub deleteCheckboxes()
'found in comments of same page as insert, but changed the .select and selection.delete to just .delete
'added ".clear" to remove the LinkedCell value, but only when the linked cell is the same as the checkbox
Dim myBox As CheckBox
Dim myCell As Range
Dim myLink As Range
Dim cellRange As String
cellRange = InputBox(Prompt:="Cell Range", Title:="Cell Range")
 
With ActiveSheet
For Each myCell In .Range(cellRange).Cells
    With myCell
        ActiveSheet.Shapes("checkbox_" & myCell.Address(0, 0)).Delete
        [COLOR=red].Clear[/COLOR]
 
End With
Next myCell
End With
End Sub

The .Clear is what I added (original delete code did not have this)

Here is what I am up to in clearing the linked cell regardless of its location:

Sub deleteCheckboxes_andLinkedCell()
'found in comments of same page as insert, but changed the .select and selection.delete to just .delete
'added ".clear" to remove the LinkedCell value, but only when the linked cell is the same as the checkbox
'changed code so the linked cell is cleared
Dim myBox As CheckBox
Dim myCell As Range
Dim myLink As Range
Dim cellRange As String
cellRange = InputBox(Prompt:="Cell Range", Title:="Cell Range")
 
With ActiveSheet
For Each myCell In .Range(cellRange).Cells
    With myCell
        Set myBox = .Range(myCell.Address(0, 0))
        Set myLink = .Range(myBox.LinkedCell)
        ActiveSheet.Shapes("checkbox_" & myCell.Address(0, 0)).Delete
        .Range(myLink).Clear
 
 
End With
Next myCell
End With
End Sub

When I run the code with a breakpoint at the Set myBox... line and hover the mouse over myCell.Address(0, 0) the displays shows myCell.Address(0, 0) = "J7" (where J7 is the first cell of the range I entered to delete the checkboxes). By my thinking, I figured that if I made it into a Range and Set a variable, then I could use the variable to lookup the LinkedCell, Range that into a new variable, then use the new variable to Clear that range.

Doesn't work.

I get a Run-time error'13': Type mismatch.

Previously I had tried using code similar to the line that actually deletes the checkbox in order to try and extract the linked cell value. Don't recall exactly what that code was but it didn't work. So I modified until I had the above code.

Since this code I tried the following mods:
changing the declaration from range to string - no luck
Set myBox = myCell.Address(0, 0) - no luck
Removing the (0, 0) in both cases - no luck
and some other things that I am losing track of - no luck on any of them.


Can somebody help please. It started as a practice exercise and now I'm getting frustrated a little.

Thanks very much,
Darren

 

[tweedle]

practice, practice, practice

will probably need to play with this a bit ...

With ActiveSheet
 
Dim obj As OLEObject
For objIdx = 1 To .OLEObjects.Count
Set obj = .OLEObjects(objIdx)
    Debug.Print obj.Name, obj.LinkedCell, Range(obj.LinkedCell).Value
    Range(obj.LinkedCell).Clear
    Set obj = Nothing
Next

 

[AusSteelMan]

Thanks tweedle.

I will give this a try, but will have to do some research into OLE. No idea about this part of the game.

I'll plug it in and see what happens and then try and figure out why.

Regards,
Darren