Solved Why would the zoom % change the way VBA code is run?

December 10, 2014 at 16:24:02
Specs: Windows 7
Why would the zoom % in Excel change the way VBA code is run?

I have two ranges on a sheet and have code attached to ActiveX Command Buttons. The code looks at the value in one range and if it finds the same value in the other range it fills it with the same color.

For some reason when the zoom level changes only a certain portion of the second range will fill. For example, if zoom is at 50% it will match/fill color for values 1-201 in the second range. If I set zoom% to 80 it will match/fill color for values 1-1000. I have values 1-1144 so it just stops short! I am lost as to why this would happen and why the zoom % effects how the code runs?!


See More: Why would the zoom % change the way VBA code is run?

Report •

✔ Best Answer
December 11, 2014 at 12:57:02
...except that it's not happening to me.

Of course, I don't have your data, your system, etc.

Here is something to try:

If you run the code below, it will fill your ranges with hard coded numbers as a test.

Vert_Braid will contain sequential numbers from 1 to 2583 (the number of cells in the range).

Vert_Load will contain random numbers between 1 and 2583.

NOTE: Neither of the ranges I tested this on have any merged cells. I'll leave you to play with this and make the necessary changes to have it work for your merged cells.

After you set up the ranges via this code and then run your macro, it should be fairly easy to tell if the glitch occurs with this data or just yours or just in your workbook (corruption?) or just on your system, etc. When I set up a worksheet in this manner, I see no difference in which cells are colored by the code at any zoom level.

Sub Fill_Ranges()
'Fill Vert_Braid with sequential numbers
  
  For cellVal = 1 To 2583
    Range("Vert_Braid").Cells(cellVal) = cellVal
  Next

'Fill Vert_Load with random numbers
  Range("Vert_Load").Cells.Formula = "=RANDBETWEEN(1,2583)"
  Range("Vert_Load").Cells.Value = Range("Vert_Load").Cells.Value

'Color each column of Vert_Load
  For colNum = 1 To 8
   Range("Vert_Load").Columns(colNum).Interior.ColorIndex = colNum + 2
  Next
 End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
December 10, 2014 at 16:27:20
Hard to say why and even harder to test unless we have some VBA code to try...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 10, 2014 at 21:27:14
DerbyDad03, here is the code, I am sure it will look familiar :) You were amazing in helping with this a while back. The "Vert_Braid" range is C5:K291 and the cells are in a merged and centered in a brick like pattern. The "Vert_Load" range is P5:W147. Both ranges have values from 1-1144. When the sheet is viewed at 80% zoom the "Vert_Braid" will fill with color match to 999. When viewed at other zoom levels I get less. Very odd...

Private Sub CommandButton3_Click()
'Clear fill color in Braid Chart range
   Sheets("Vertical").Range("Vert_Braid").Interior.ColorIndex = xlNone
 'Loop through cells in Loading Chart range looking for cells with fill color
    For Each myCell In Sheets("Vertical").Range("Vert_Load")
     If myCell.Interior.ColorIndex <> xlNone Then
 'If cell has fill color, find value in Braid Chart and match fill color
         With Sheets("Vertical").Range("Vert_Braid")
           Set c = .Find(myCell.Value, lookat:=xlWhole, LookIn:=xlValues)
            If Not c Is Nothing Then
             c.Interior.ColorIndex = myCell.Interior.ColorIndex
            End If
         End With
     End If
    Next
End Sub


Report •

#3
December 11, 2014 at 07:34:11
I tested your code without any merged cells and the zoom level didn't make any difference.

I have to assume that the issue is related to the merged cells but I don't have time to set up a huge range of cells that are "merged and centered in a brick like pattern".

Have you tested your data with non-merged cells just to see what happens?

I don't know if this matters, but when cells are merged, the data actually only exists in one of the cells within the merged set. Depending on how the cells were merged, the data is usually stored in the upper left cell, but that is not true 100% of the time.

I guess I would play around and see if the merging has anything to do with it. Maybe the cells that actually contain the data are in fact being colored, but the zoom level is impacting how it looks to the viewer.

Perhaps inserting some MsgBox instructions to return the address of the cells that are being colored might lead you somewhere.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 11, 2014 at 09:34:44
Great suggestions, I will play with this today! Thank you!

Report •

#5
December 11, 2014 at 10:43:48
Bummer, no luck, I unmerged all the cells and still the same glitch! I think this is one of those things that just doesn't make sense and is a deeper glitch on Microsoft's end.

Report •

#6
December 11, 2014 at 12:57:02
✔ Best Answer
...except that it's not happening to me.

Of course, I don't have your data, your system, etc.

Here is something to try:

If you run the code below, it will fill your ranges with hard coded numbers as a test.

Vert_Braid will contain sequential numbers from 1 to 2583 (the number of cells in the range).

Vert_Load will contain random numbers between 1 and 2583.

NOTE: Neither of the ranges I tested this on have any merged cells. I'll leave you to play with this and make the necessary changes to have it work for your merged cells.

After you set up the ranges via this code and then run your macro, it should be fairly easy to tell if the glitch occurs with this data or just yours or just in your workbook (corruption?) or just on your system, etc. When I set up a worksheet in this manner, I see no difference in which cells are colored by the code at any zoom level.

Sub Fill_Ranges()
'Fill Vert_Braid with sequential numbers
  
  For cellVal = 1 To 2583
    Range("Vert_Braid").Cells(cellVal) = cellVal
  Next

'Fill Vert_Load with random numbers
  Range("Vert_Load").Cells.Formula = "=RANDBETWEEN(1,2583)"
  Range("Vert_Load").Cells.Value = Range("Vert_Load").Cells.Value

'Color each column of Vert_Load
  For colNum = 1 To 8
   Range("Vert_Load").Columns(colNum).Interior.ColorIndex = colNum + 2
  Next
 End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question