Worksheet_Calculate disabling undo in entire workbook

kcmuppet

Board Regular
Joined
Nov 2, 2005
Messages
211
Hello,

I've come across some code that using a Worksheet_Calculate event which seems to be disabling undo in every sheet in the workbook. Am I doing something wrong or is it not possible to limit this to just the target?

The idea is that it swaps a picture in cell K2, based on a value in B10, using =VLOOKUP(B10, PicTable, 2, FALSE)

Code:
Private Sub Worksheet_Calculate()
    Dim oPic As Picture
       Me.Pictures.Visible = False
       With Range("K2")
           For Each oPic In Me.Pictures
               If oPic.Name = .Text Then
                   oPic.Visible = True
                   oPic.Top = .Top
                   oPic.Left = .Left
                   Exit For
               End If
           Next oPic
        End With
 End Sub
Apologies if its an obvious blunder, I'm not really very conversant with event stuff - all help welcome
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, kcmuppet,


why would you run the code whenever the sheet is calculated ?
you only need to run it when K2 has changed
I presume you used calculate because K2 contains a formula
if not then the code can even be easier
try this
Code:
Option Explicit

Public OldValue As String

Private Sub Worksheet_Change(ByVal Target As Range)
If OldValue = Range("K2") Then Exit Sub
yourcode
OldValue = Range("K2")
End Sub

Sub yourcode()
    Dim oPic As Picture
       Me.Pictures.Visible = False
       With Range("K2")
           For Each oPic In Me.Pictures
               If oPic.Name = .Text Then
                   oPic.Visible = True
                   oPic.Top = .Top
                   oPic.Left = .Left
                   Exit For
               End If
           Next oPic
        End With
 End Sub
does this work for you ?

kind regards,
Erik
 

kcmuppet

Board Regular
Joined
Nov 2, 2005
Messages
211
Sincere thanks Erik, it appears to be just right. It has taken me a while to I work out where to put it in relation other code I have on the sheet. I haven't tested it all together yet, but I'll report back if I have any problems
why would you run the code whenever the sheet is calculated ?
For two reasons, but neither of them any good(!):
First because I already have two other things triggered by worksheet change events, which are [to me at least] a little complicated!, and second because of lack of skill :oops: For example, I don't really understand the Option Explicit statement and why OldValue is a String & not a variant.
I presume you used calculate because K2 contains a formula
if not then the code can even be easier
Well K2 contains exactly =VLOOKUP(B10, PicTable, 2, FALSE), and your modified code works just fine. Are you saying the whole of this could be much simpler? I'm always in favour of more efficient methods.

Don't go to any trouble if you don't have time, your help already is greatly appreciated.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
For example, I don't really understand the Option Explicit statement and why OldValue is a String & not a variant.
just take a look in the helpfiles
Option Explicit
forces you to declare all variables
I found this very helpful for several reasons
the most important is to avoid typos
run the example with and without option explicit

Code:
Sub multiply()
'typo which could be avoided by using Option Explicit
Dim strng1 As Long
Dim strng2 As Long

Range("A1") = 1
Range("B1") = 2
strgn1 = Range("A1")
strng2 = Range("B1")

MsgBox "range(""A1"") x range(""B1"") is :" & strng1 * strng2
End Sub
OldValue is a String & not a variant.
"variant" is the most general way of declaring variables, "string" is more explicit (using less memory)
take a look in the helpfiles for "datatypes"
Are you saying the whole of this could be much simpler
did I ?
anyway this let me think at just refering directly to the picture
would something like this work ?
Me.Shapes(Range("K2")).Visible = True
of course the name in K2 should exist
(this oneliner would replace the loop For Each oPic In Me.Pictures ... Next oPic )

stuff to study here :)
best regards,
Erik
 

kcmuppet

Board Regular
Joined
Nov 2, 2005
Messages
211
Thanks for the lesson Erik - indeed much to study! I did look at the helpfile but didnt understand them...time to buy a book, I think
 

Forum statistics

Threads
1,078,469
Messages
5,340,507
Members
399,380
Latest member
rovius

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top