Incorporating 'Zoom' code into an already existing code

**billyster** · 03-25-2011, 07:52 PM

Hi all, I am still having this issue with incorporating a 'zoom' like function into my current worksheet's code. There is already exisiting code on my current worksheet. I understand that there a few ways to 'zoom' into a highlighted cell in a worksheet, but I want to know if I can incorporate this 'zoom' code with my already existing code (both on the same worksheet).

I am not sure how to post my current worksheets code on the forum, but I can upload my worksheet if that helps out. Please let me know if what I am asking for is unclear and I will try to clarify my wording. Thanks

-billy

**6StringJazzer** · 03-26-2011, 09:59 AM

Originally Posted by billyster

I understand that there a few ways to 'zoom' into a highlighted cell in a worksheet....

What kind of zooming do you mean? Might help to tell us those ways that you know about. Do you mean like the Zoom command on the ribbon/toolbar? That doesn't zoom in on a cell, it zooms the view, although it keeps the selected cell in the window.

In any case it should be straightforward to incorporate into existing code.

I am not sure how to post my current worksheets code on the forum...

Hit Reply, and in the reply window scroll down, where you will see a button that says Manage Attachments. You can upload files within fairly generous size limits.

**6StringJazzer** · 03-26-2011, 10:18 AM

Oh, by the way

ActiveWindow.Zoom = percentZoom

**billyster** · 03-26-2011, 05:22 PM

6StringJazzer,

The kind of zooming I would like is either to zoom say to 120+% when a certain cell is highlighted (such as a data validation list cell so it is possible to actually view the list since the list is too small at a zoom of 40% or less). The other type of zoom that I'm interested in is when a cell is highlighted, that cell is 'expanded' larger right next to that cell. The attached excel has an example of this type of zoom. Now I am curious about how I would incorporate either of these separate 'zoom' codes into my one of my excel worksheets with existing code.

On your second comment about code. I don't understand how to paste my code into this message thread like you did on your second post.

-billy

**6StringJazzer** · 03-26-2011, 06:58 PM

To zoom when a particular cell is selected, you would do this:

The workbook you attached is odd in that I can't select any cells or do anything else, but protection does not seem to be turned on. Where did you get it?

It appears to me that the rectangle with 68 is a rectangle shape that is linked to a cell. This is not any kind of "zoom" functionality. You create a shape, select it, then in the formula box type

=A1

or whatever cell you want to show in the shape. I tried recording a macro for this but it did not capture how to insert a shape and assign a formula to it. You can do some research on that, depending on your level of inquisitiveness

But do let us know if you get stuck.

As far as including code in this forum, please read example in my signature below.

This code will detect that a particular cell has been selected (in this case B10), and if so zoom in (in this case 120%). When another cell is selected, the zoom returns to the level it was before B10 was selected. It must be installed as code for the worksheet it affects.

Dim LastZoom As Long

Public Sub Worksheet_SelectionChange(ByVal Target As Range)

   Const ZoomIn As Long = 120

   If Target.Address = [B10].Address Then
      LastZoom = ActiveWindow.Zoom
      ActiveWindow.Zoom = ZoomIn
   ElseIf ActiveWindow.Zoom = ZoomIn Then
      ActiveWindow.Zoom = LastZoom
   End If

End Sub

An alternative to this technique would be to bring up a combobox control if the cell is selected, which wouldn't require any zooming. The value of the combobox could be linked to the cell. I didn't work out the details but let me know if you want to pursue it.

Also let me know if you need an actual example workbook.

**billyster** · 03-26-2011, 07:56 PM

6StringJazzer,

I'm not sure which method (zooming vs combo boxes) I would prefer to use in terms of 'zooming' in on a highlighted cell, but it seems to me both methods require VBA code. In the worksheet that I would like to incorporate either of these 'zooming' functions I already have some VBA code as follows:

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim cell As Range
Dim RNG As Range

For Each cell In Target
    If Not Intersect(cell, Range("J11,H17")) Is Nothing Then
        Set RNG = Range("J11,H17")
    ElseIf Not Intersect(cell, Range("J13,J20")) Is Nothing Then
        Set RNG = Range("J13,J20")
    ElseIf Not Intersect(cell, Range("H26,J32,J41")) Is Nothing Then
        Set RNG = Range("H26,J32,J41")
    ElseIf Not Intersect(cell, Range("H27,H28,H31,H34")) Is Nothing Then
        Set RNG = Range("H27,H28,H31,H34")
    ElseIf Not Intersect(cell, Range("H19,H29,H36,H38")) Is Nothing Then
        Set RNG = Range("H19,H29,H36,H38")
    ElseIf Not Intersect(cell, Range("C5,H31,H40")) Is Nothing Then
        Set RNG = Range("C5,H31,H40")
    End If
    
    If Not RNG Is Nothing Then
        Application.EnableEvents = False
        RNG = cell
        Application.EnableEvents = True
    End If
Next cell

End Sub

How would I adjust this current code to incorporate either of the 'zoom' code functions while retaining the full functionality of the current one?

Also the last worksheet I uploaded was from a bad lot. The good worksheet is attached and was given to me from another contributor from this forum.

-billy

**6StringJazzer** · 03-27-2011, 09:59 AM

The first If condition checks to see whether the selected cell should trigger the zoom:

   If Target.Address = [B10].Address Then

You can't just say

   If Target = [B10] Then

because VBA will say, "Are Target and [B10] the same object? Well, no, Target is a variable and [B10] is a Range literal so even if they refer to the same cell, they are not the same object." The explanation for this is much bigger and probably takes up a class session on object-oriented programming. Suffice it to say that for Excel Ranges, you have to compare Address if you want to see if two Ranges refer to the same block of cells.

If you want to test whether the cell belongs to some block of cells, you can do this:

   If Not Intersect (Target, [A1:C5]) Is Nothing Then

If you want to test that the cell belongs to a certain column, you can do this:
The two are equivalent; in the first case we counted columns to figure out what column was E, and in the second case we let VBA count the columns for us. Including cells in a given Row would work the same way. You can combine multiple conditions with Or

    If Target.Column = [A1].Column Or Target.Column = [E1].Column Then

Hopefully that will teach you to fish, but let me know if you want help catching one

Incorporating 'Zoom' code into an already existing code

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Incorporating 'Zoom' code into an already existing code

Re: Incorporating 'Zoom' code into an already existing code

Re: Incorporating 'Zoom' code into an already existing code

Re: Incorporating 'Zoom' code into an already existing code

Re: Incorporating 'Zoom' code into an already existing code

Re: Incorporating 'Zoom' code into an already existing code

Re: Incorporating 'Zoom' code into an already existing code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions