application.screenupdating problems

[purpleozzie]

Hi,

I've assigned the following macro to a shape.. I want to simulate a button click when the shape is pressed hence the reason I've called simulatebutt*******

Sub FREEZE()
Call SimulateButt*******
Application.ScreenUpdating = False
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 28")).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "UNFREEZE"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 8). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 8).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 13
        .Name = "+mn-lt"
    Selection.OnAction = "UNFREEZE"
    End With


    ActiveWindow.ScrollRow = 1
    ActiveWindow.SmallScroll Down:=13
    Rows("19:19").Select
    ActiveWindow.FreezePanes = True
    Range("A19").Select
Application.ScreenUpdating = True
End Sub

This only works when I remove the application.screenupdating = false line.. but this line comes below the Call simulatebutt******* line..

The simulatebutt******* maro looks like this:

Sub SimulateButt*******()
Dim vTopType As Variant
Dim iTopInset As Integer
Dim iTopDepth As Integer


Application.ScreenUpdating = False


'Record original button properties
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        vTopType = .BevelTopType
        iTopInset = .BevelTopInset
        iTopDepth = .BevelTopDepth
    End With
'Button Down
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        .BevelTopType = msoBevelSoftRound
        .BevelTopInset = 12
        .BevelTopDepth = 4
    End With
    Application.ScreenUpdating = True


'Button Up - set back to original values
    With ActiveSheet.Shapes(Application.Caller).ThreeD
        .BevelTopType = vTopType
        .BevelTopInset = iTopInset
        .BevelTopDepth = iTopDepth
    End With
End Sub

Not sure why this is happening, can anyone see what I'm doing wrong...

 

[CatOnASpreadsheet]

Hi Purpleozzie,

I'd say the reason for that is the fact that when your simulate macro is called it does both the button down and the button up in short sequence - it does what it's meant to, it just does it very, very fast. What I tend to do in such cases is have two different subs, one for button down which is indeed called at the start of the main sub and the other one for button up, which I call at the end. This basically just puts a delay between the two, the length of which depends on the length of time it takes for the main sub to run.

You can probably achieve the same by putting in a delay timer in your simulation sub - something along the lines of

Application.Wait(Now + #0:00:01#)

before your Button Up section will force a one second delay before the button pops back up.

I hope this helps.

Cheers,
Catalin

 

[purpleozzie]

Hi Catalin,

Thanks for your response, alas that's not the problem because if I assign the simulatebutt******* sub directly to the button it makes the click animation

 

[CatOnASpreadsheet]

Hi Purpleozzie,

Have you tried removing the "Call" bit you have before the simulatebutt*******? From MSDN:

[FONT=&quot]To call a [/FONT]Sub[FONT=&quot] procedure from another [/FONT]procedure[FONT=&quot], type the name of the procedure and include values for any required [/FONT]arguments[FONT=&quot]. The [/FONT]Call[FONT=&quot]statement is not required, but if you use it, you must enclose any arguments in parentheses.

[/FONT]https://msdn.microsoft.com/en-us/library/office/gg251432.aspx[FONT=&quot]

Cheers,
Catalin[/FONT]

 

[purpleozzie]

'fraid so.. It's definitely calling that sub but it's not showing me the button press..

 

[CatOnASpreadsheet]

Hi Purpleozzie,

I'm not able to test at the moment, but would you do me a favour and try this?

Add this before calling the sub:

Dim strName As String
strName = ActiveSheet.Shapes(Application.Caller).Name

Change the call like this:

Call simulatebutt*******(strName)

And in the button sub:

Sub simulatebutt*******(strName as String)

And replace all the Application.Caller in the button sub with strName.

What I'm thinking is that the Application.Caller might look at the main sub as its caller, instead of looking at the button.

Let me know how it goes.

Cheers,
Catalin

 

[purpleozzie]

Hi Catalin,

Thanks for your speedy replies, alas it doesn't work however I think you might be right in that application.caller is looking at the main sub rather than the button..

 

[CatOnASpreadsheet]

Hi Purpleozzie,

Sorry for the long delay before answering again, I just got back to a computer where I can actually test stuff.

Just for testing purposes, I threw together the following code:

Sub test()


Dim i As Integer


Call test2
Application.ScreenUpdating = False


i = 1


Do While i < 100
i = i + 1
Loop


Application.ScreenUpdating = True


MsgBox "End"


End Sub


Sub test2()


MsgBox ActiveSheet.Shapes(Application.Caller).Name


End Sub

It looks like the test2 macro still returns the correct Application.Caller name even when the main sub is assigned to the button, so that's not it, either. So I went a step further and replaced my test2 with the body of your simulatebutt******* macro, and it still works fine. The content of the main macro should have no impact on anything unless the button which calls the macro is actually "Rounded Rectangle 28", but even then, the main macro does not have any effect on the 3d format of the button.

Just to test if your code actually goes through the secondary sub, can you please add the following to the end of your simulatebutt*******?

MsgBox "Test"

If it does, I'd say just replace "Application.Caller" with the actual name of one of the buttons which would be calling the main sub, resize the VB Editor window so you have full view of the button, and step through the main sub using F8 to be able to see what changes are applied to it and at what point in the code.

Let me know how it goes.

Cheers,
Catalin