Worksheet change event to change font, wrap text and shrink to fit in second cell

[dakota727]

OK I have not been able to get this working. What I am trying to do is to when the value in B8 is changed the format of cell C6 on sheet Diagram has the font, wrap and shrink to fit changed based on the length of the entry. Values greater than 16 small font and wrap text. Values less than 16 large font with shrink to fit.

I'm not sure where I am going wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$8" Then
     
    If Len(Target) > 16 Then

Application.EnableEvents = False
Worksheets("Diagram").Range("C6").Activate


With ActiveCell
    .ShrinkToFit = False
    .Font.Size = 11
    .WrapText = True
End With
Else
 Application.EnableEvents = False
Worksheets("Diagram").Range("C6").Activate
With ActiveCell
.Font.Size = 16
.WrapText = False
.ShrinkToFit = True
End With
End If

stoppit:
Application.EnableEvents = True
End If
  
  

End Sub

I must admit I always struggle with on change events. Any help would be appreciated.

Thanks

 

[dakota727]

I think I found my error. I hade the last enable events inside the IF statement. Looks like this is working,

 Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$8" Then
   Application.EnableEvents = False
    If Len(Target) > 16 Then
        Worksheets("Diagram").Range("C6").WrapText = True
        Worksheets("Diagram").Range("C6").Font.Size = 11
        Worksheets("Diagram").Range("C6").ShrinkToFit = False
        
    Else

        Worksheets("Diagram").Range("C6").WrapText = False
        Worksheets("Diagram").Range("C6").Font.Size = 24
        Worksheets("Diagram").Range("C6").ShrinkToFit = True
End If
Else

Application.EnableEvents = True
End If
stoppit:
Application.EnableEvents = True
 
End Sub

 

[Rick Rothstein]

For the structure you have used, I would think this would be "cleaner"...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$8" Then
    Application.EnableEvents = False
    If Len(Target) > 16 Then
      Worksheets("Diagram").Range("C6").WrapText = True
      Worksheets("Diagram").Range("C6").Font.Size = 11
      Worksheets("Diagram").Range("C6").ShrinkToFit = False
    Else
      Worksheets("Diagram").Range("C6").WrapText = False
      Worksheets("Diagram").Range("C6").Font.Size = 24
      Worksheets("Diagram").Range("C6").ShrinkToFit = True
    End If
    Application.EnableEvents = True
  End If
End Sub

I believe this more compact version will do the same thing as the above code does...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Test As Boolean
  If Target.Address = "$B$8" Then
    Test = Len(Target) > 16
    Application.EnableEvents = False
    Worksheets("Diagram").Range("C6").WrapText = Test
    Worksheets("Diagram").Range("C6").Font.Size = 24 + 13 * Test
    Worksheets("Diagram").Range("C6").ShrinkToFit = Not Test
    Application.EnableEvents = True
  End If
End Sub

 

[dakota727]

I think I follow what you are doing. One typo though, to get to the 11 font I think you meant (24-13 * Test).

I figured there would probably be an easier way and I think you have provided it. Thank you very much.

 

[Rick Rothstein]

I think I follow what you are doing. One typo though, to get to the 11 font I think you meant (24-13 * Test).

I figured there would probably be an easier way and I think you have provided it. Thank you very much.

No, the plus sign was intentional... in VB, True equates to minus one (-1), not plus one. So Test will either be the value 0 or -1.