Worksheet_Calculate disabling undo in entire workbook

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
335
Office Version
  1. 365
  2. 2010
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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

Active Member
Joined
Nov 2, 2005
Messages
335
Office Version
  1. 365
  2. 2010
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

Active Member
Joined
Nov 2, 2005
Messages
335
Office Version
  1. 365
  2. 2010
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,991
Messages
5,834,764
Members
430,319
Latest member
Excelhelppll

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top