Worksheet_Calculate disabling undo in entire workbook

[kcmuppet]

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)

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]

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

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]

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 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]

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

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]

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

 

[erik.van.geit]

I did look at the helpfile but didnt understand them

feel free to ask explanation of somedetails