Drop Down List Zoom

[rockyw]

I found a code to zoom a validation dropdown list and then after selection it goes back to 100%. Problem is it also moves off the screen. Is there a way to keep this centered and still zoom up and down? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWindow.Zoom = 100
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim R As Range
Application.EnableEvents = False
On Error Resume Next
Set R = Range("C:C").SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If R Is Nothing Then
    Application.EnableEvents = True
    Exit Sub
End If
If Not Intersect(Target, R) Is Nothing Then
    ActiveWindow.Zoom = 150
End If
Application.EnableEvents = True
End Sub

 

[rockyw]

I also added a second column

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWindow.Zoom = 100
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim R As Range
Application.EnableEvents = False
On Error Resume Next
Set R = Range("C:C","G:G").SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If R Is Nothing Then
Application.EnableEvents = True
Exit Sub
End If
If Not Intersect(Target, R) Is Nothing Then
ActiveWindow.Zoom = 150
End If
Application.EnableEvents = True
End Sub

 

[rockyw]

I guess all I need added is to move the screen to the left where it started?

 

[rockyw]

Know one knows how to make a code change to make this screen scroll left after this code runs? I have found many things on line but none of them has worked. Thanks

 

[Fluff]

How about

ActiveWindow.Zoom = 150
Application.Goto Target, True

 

[rockyw]

That zooms all over the screen, what I need is it to stay where its at and zoom, then return to the same location. Right now the screen is alright in the C column but when I use column G the screen stays over to the right. I need it to return to the left side. Thanks

 

[Fluff]

When I use that the cell that was changed becomes the top left cell in the window.
Is that not what you want?

 

[rockyw]

What I would like it the cell to stay where its at and just zoom larger, then when selected it returns to the normal size and location. Right now when I select a drop down in G its zooms but then the screen stays to the right. I would like it to return to its original location. Thanks

 

[Fluff]

Whilst that is probably possible, I've no idea how to do it.

 

[rockyw]

I'm close really really close. Now if the selected cell with the drop down would center in screen that would be great...

Private Sub Worksheet_Change(ByVal Target As Range)
 ActiveWindow.Zoom = 71
 Application.Goto reference:=Range("a1"), scroll:=True
 
 End Sub

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim R As Range
 Application.EnableEvents = False
 On Error Resume Next
 Set R = Range("C4:C24", "G4:G24").SpecialCells(xlCellTypeAllValidation)
 On Error GoTo 0
 If R Is Nothing Then
 Application.EnableEvents = True
 Exit Sub
 End If
 If Not Intersect(Target, R) Is Nothing Then
 ActiveWindow.Zoom = 150
 End If
 Application.EnableEvents = True
 
  End Sub