Worksheet_Calculate disabling undo in entire workbook

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
427
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,452
Messages
6,101,939
Members
447,764
Latest member
gopalgriffith

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
Back
Top