Zoom a specified range when active?

[Brian F]

Windows 8, Excel 2010; The cells AK11:AX11 are merged, I want to have have the worksheet zoom to a specified level (lets say 85%) when the range "AK11:AX11" is active. (when I click on the range "AK11:AX11" I want those cells to zoom to 85 percent). Can some one help me with this?

 

[MARK858]

Try putting this code in the Worksheet module (right click the sheet tab, click view code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("AK11")) Is Nothing Then
        ActiveWindow.Zoom = 85
    Else
        ActiveWindow.Zoom = 100
    End If
End Sub

 

[Brian F]

Thank you Mark858,

Your code works in a blank worksheet but not in my document. Moving some code from my document to the “blank” worksheet reveals that it is because of some code I am using in my document to zoom any cell with a drop down menu (I got that code from the internet and while I understand a lot of it, a good deal of it is over my head). That code does what I need except the drop down in AK11:AX11 is too small to read. I had hoped that putting in separate code for that range might solve the problem, but not surprisingly, they conflict. Here is that code. If you see any way to add a special case to the code for cells AK11:AX11 it would be appreciated. If not, thank you so much for your efforts. (I thought there may be a way to add a third case for zoom values for the specific cells in question)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long

lZoom = Range("CL9").Value
lZoomDV = Range("CL10").Value
lDVType = 0
‘ ”CL9” = 57%
‘ ”CL10” = 65%

Application.EnableEvents = False
On Error Resume Next
lDVType = Target.Validation.Type

On Error GoTo errHandler
If lDVType <> 3 Then
With ActiveWindow
If .Zoom <> lZoom Then
.Zoom = lZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> lZoomDV Then
.Zoom = lZoomDV
End If
End With
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler

 

[MARK858]

Maybe just (untested)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long


ActiveWindow.Zoom = 100
If Not Intersect(Target, Range("K11")) Is Nothing Then
    ActiveWindow.Zoom = 85
        GoTo exitHandler
        End If


lZoom = Range("CL9").Value
lZoomDV = Range("CL10").Value
lDVType = 0
' ”CL9” = 57
' ”CL10” = 65


Application.EnableEvents = False
On Error Resume Next
lDVType = Target.Validation.Type


On Error GoTo errHandler
If lDVType <> 3 Then
With ActiveWindow
If .Zoom <> lZoom Then
.Zoom = lZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> lZoomDV Then
.Zoom = lZoomDV
End If
End With
End If


exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

 

[Brian F]

Ha! I works great. That is so cool! Thank's so much. Could I ask you for one modification?
Is there a way to make the zoom of 100% apply to the range "K12" as well as "K11" (as it does now)?
In other word if I am in the cell "K12" or "K11" the sheet zooms to 100%.
(and that really is the end [I think] of a huge project I've been working on for months)

 

[MARK858]

My typo error as I put K11 rather than AK11

The cells AK11:AX11 are merged

If the cells are merged then the only active cell is AK11

In light of the above can you just clarify your requirements as obviously

Is there a way to make the zoom of 100% apply to the range "K12" as well as "K11" (as it does now)?

doesn't fit the original request in post #3
(by the way the zoom should be 85%, can you double check this)

 

[Brian F]

Yes, I caught the "K" rather than "AK" and made the change. And yes, as you say the merged cells AK11:AX11 behave as if they are AK11. The zoom was an arbitrary number, I will fiddle with it and see what looks right.
Yes, what you did corresponds exactly to what I hoped you'd be able to do and I'm very appreciative. What I was asking is if the cells BELOW (and separate from) "AK11:AX11" (now merged to form AK11), that would be the cells "AK12:AX12" (now merged to form AK12) could be make to behave in the same way as does AK11. So, if I were in cell AK11 the chosen zoom would apply OR if I were in cell AK12 the same chosen zoom factor would once again apply. There are many drop down lists in the work sheet. I can see them all well enough save for the one in AK11 and the one in AK12 (separate drop downs dependent upon one another) [if I were dealing with an excel formula it would be an "OR" statement, i.e. If either "AK11" OR "AK12" are chosen, zoom to the same chosen value]. Thanks again for your help and for sticking with me on this. I'm hopping what I'm asking for requires a simple change and is not too much of a bother for you.

 

[MARK858]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long


ActiveWindow.Zoom = 100
If Not Intersect(Target, Range("[COLOR=#ff0000]AK11,AK12[/COLOR]")) Is Nothing Then
    ActiveWindow.Zoom = 85
        GoTo exitHandler
        End If


lZoom = Range("A11").Value
lZoomDV = Range("A12").Value
lDVType = 0
' ”CL9” = 57
' ”CL10” = 65


Application.EnableEvents = False
On Error Resume Next
lDVType = Target.Validation.Type


On Error GoTo errHandler
If lDVType <> 3 Then
With ActiveWindow
If .Zoom <> lZoom Then
.Zoom = lZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> lZoomDV Then
.Zoom = lZoomDV
End If
End With
End If


exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

 

[Brian F]

Yep, works perfectly! This will make my worksheet far more friendly. Thank you again.

 

[MARK858]

Your welcome, thanks for the feedback.