VBA Help - is it Possible to Have 2 Worksheet_Calculate events in one Sheet?

[Johnny Thunder]

Hello All,

I am working with a piece of code that works for the first if statement but is not triggering anything on the second If Statement.

Purpose of the code is to scan a validation column (Column W) and if there are any errors autofit the column so that the errors can be read easily. As the user corrects certain cells the validation comment shrinks and also does another Autofit. I have a second range in Column X that is a comment field that I would also like to autofit as the user enters text. Currently the code will only do the Column W Autofitbut bypasses the Column X.

Any idea what is wrong?

'Re-Autofit column V for Validation
Private Sub Worksheet_Calculate()

Dim rng As Range, cell As Range, rng2 As Range
Dim LastR As Long

LastR = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

    'see if any changes are in the monitored range...
    Set rng = Range("W4:W" & LastR)

    If Not rng Is Nothing Then

        For Each cell In rng.Cells
            If cell.Value <> "" Then
                Range("W4").EntireColumn.AutoFit
            End If
        Next

    End If

Set cell = Nothing

'see if any changes are in the monitored range...
    Set rng2 = Range("X4:X" & LastR)

    If Not rng2 Is Nothing Then

        For Each cell In rng2.Cells
            If cell.Value <> "" Then
                Range("X4").EntireColumn.AutoFit
            End If
        Next

    End If

Range("E3:X3").EntireColumn.AutoFit

With ActiveSheet
    .EnableSelection = xlNoSelection
End With

End Sub

 

[jasonb75]

A calculate event only works with formulas, data typed in by the user needs to be checked with a worksheet change event.

 

[Johnny Thunder]

In that case, is it possible to have two seperate Worksheet Change Events in one sheet? or do you just add both ranges to search in with one change event?

 

[jasonb75]

Just join it all together, you can only have one of each type of event in each sheet.

If the code works for column W then that suggests it contains formulas, so you will need to keep the calculate code for that.

 

[jasonb75]

See if this works, I've gone on the assumption that the calculate code works on column W because the entry is affecting a formula somewhere else and that the data in column W is actually being typed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W:X")) Is Nothing Then
    Range("W:X").EntireColumn.AutoFit
End If
End Sub

 

[Johnny Thunder]

Worked great! Thanks