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,082,106
Messages
5,363,185
Members
400,721
Latest member
eileen123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top