Clear TextBox Contents with Worksheet_Change Event

[jmitchells5w]

I have a Worksheet_Change event set up so that when a value is entered/selected into cell C3 several textboxes update with values. These values are a result of using the linkedcell property. The problem is that when I delete the contents of C3 the Worksheet_Change event does not trigger and the values previously in the text boxes stay. I would like these to clear out. Here is the code I am currently using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$C$3" Then
Call AssignLinkedCells
End If

End Sub

Sub AssignLinkedCells()
'
Addr = "CompanyInfo!" & Range("D37").Value
Addr2 = "CompanyInfo!" & Range("D72").Value
Addr3 = "CompanyInfo!" & Range("C11").Value
Addr4 = "CompanyInfo!" & Range("C12").Value
Addr5 = "CompanyInfo!" & Range("D58").Value
TextBox1.LinkedCell = Addr
TextBox2.LinkedCell = Addr2
TextBox3.LinkedCell = Addr3
TextBox4.LinkedCell = Addr4
TextBox5.LinkedCell = Addr5

End Sub

 

[Tom Urtis]

Substitute the Worksheet_Change event code you currently have with this, and see if it does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$3" Then Exit Sub
If IsEmpty(Target) Then
Dim i%
For i = 1 To 5
ActiveSheet.OLEObjects(i).Object.Text = ""
Next i
Else
Call AssignLinkedCells
End If
End Sub

 

[jmitchells5w]

No, it does not solve the problem. When I select C3 and hit delete the text boxes do not update. I have been reading other posts and should note that C3 is actually 11 cells merged together (C3 through M3). When I unmerge the cells then the text boxes do update as I want them to however because of the layout of the spreadsheet I really need to keep those cells merged.

 

[Tom Urtis]

Gotta love those merged cells.

See if this does what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" And IsEmpty(Target) = False Then
Call AssignLinkedCells
Else
If Intersect(Target, Range("C3:M3")) Is Nothing Then Exit Sub
Dim i%
For i = 1 To 5
ActiveSheet.OLEObjects(i).Object.Text = ""
Next i
End If
End Sub

 

[jmitchells5w]

I thought that worked but it but causes a problem. If the Textboxes were populated before deleting C3 then that means the linkedcell property of those textboxes hold a value. The code above clears the text boxes which then affect the cells they are linked to. I am working on the code now to clear the linkedcell property before clearing the textboxes. That should solve my problem. Thank you, Tom!

 

[jmitchells5w]

OK, here is the code I'm using and it seems to work fine. Thanks again, Tom!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" And IsEmpty(Target) = False Then
Call AssignLinkedCells
Else
If Intersect(Target, Range("C3:M3")) Is Nothing Then Exit Sub
Dim i%
For i = 1 To 5
ActiveSheet.OLEObjects(i).LinkedCell = ""
ActiveSheet.OLEObjects(i).Object.Text = ""
Next i
End If
End Sub