Help with a code - to all the experts!

[sbrazil]

Below is a code which Weaver very kindly helped me with - Thanks Weaver!

While it is awesome it doesn't quite do as I need it to do. Please look at the code below and help out with it if you think you can.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0,0) = "A1" Then
Target.Columns.ColumnWidth = 80
ActiveWindow.Zoom = 130
Else
Columns(1).ColumnWidth = 8.5
ActiveWindow.Zoom = 100
End If
End Sub

I want it to zoom in to 130 on all data validation cells on the one page, and at the same time extend the column to 80.

Weaver's did this but only for cell A1, not all data validation cells.

And then the challenging part - I want it to go back to it's original size once I click on any other non-data validated cells.

any experts think they are able to crack this one for me....????

Thanks guys,
speak soon,
Scott

 

[xld]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim dv As Variant

    On Error Resume Next
    dv = Target.Validation.Type
    On Error GoTo 0
    If Not IsEmpty(dv) Then
    
        Target.Columns.ColumnWidth = 80
        ActiveWindow.Zoom = 130
    Else
    
        Columns(1).ColumnWidth = 8.5
        ActiveWindow.Zoom = 100
    End If
End Sub

 

[sbrazil]

Hi XLD, wow!!! that was a quick response. Mate almost there - it works well except when i click on any other cell the zoom goes back to it's original size, but the column width does not.

Are you able to tweak it for me to do this? Or any clues on how to?

Thanks mate,
Scott

 

[xld]

TRy this tweak

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static PrevCol As Range
Dim dv As Variant

    On Error Resume Next
    dv = Target.Validation.Type
    On Error GoTo 0
    If Not IsEmpty(dv) Then
    
        Target.Columns.ColumnWidth = 80
        ActiveWindow.Zoom = 130
    Else
    
        If Not PrevCol Is Nothing Then PrevCol.ColumnWidth = 8.5
        ActiveWindow.Zoom = 100
    End If
    Set PrevCol = Target.EntireColumn
End Sub

 

[Weaver]

Public lastCol As Long, lastWidth As Double

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim s As Long
    If Target.Rows.Count > 1 And Target.Columns.Count > 1 Then Exit Sub
    On Error GoTo noValidation
    s = Target.Validation.Type
    If Target.Column <> lastCol Then
        lastWidth = Target.EntireColumn.ColumnWidth
        lastCol = Target.Column
        Target.Columns.ColumnWidth = 80
        ActiveWindow.Zoom = 130
    End If
    Exit Sub
noValidation:
    If lastCol <> 0 Then
        Columns(lastCol).ColumnWidth = lastWidth
        ActiveWindow.Zoom = 100
        lastWidth = 0
        lastCol = 0
    End If
End Sub