1

I am trying to create a small tutorial for one of my Excel Applications and I am running into the issue where I'm trying to draw a text shape to the screen to give advice on what to enter into an InputBox but the InputBox gets displayed before the text shape, however, when running in debug mode and stepping through the code it all works fine.

There is one userform ufNext which only contains one button, ufNext. The click event code for this button contains a Select Case clause to determine what to do each time it is clicked. The value the clause is checking is a Public variable, tutSectionsRun

Option Explicit

Private Sub btnNext_Click()

    Select Case tutSectionsRun
    Case 1
        Call Section2
    Case 2
        Call Section3
        Call MPFilterString
'   Case N
'       ...
    End Select

End Sub

The code starts in Section1 which just sets the position of ufNext and shows the form then sets the global variable tutSectionsRun to 1.

The user clicks the "Next" button on the ufNext form and it calls Section2 which re-positions the form (there would normally be other code in these "Section" procedures), and sets the global variable to 2.

Again, the user clicks the "Next" button but this time there is the issue where before the shapes are drawn to the screen, I get the InputBox popping up first and only after it closes the text shape tutText is drawn to the screen.

Option Explicit

Public tutSectionsRun As Long

Sub Section1()

    ufNext.Left = 550
    ufNext.Top = 450
    ufNext.Show

    tutSectionsRun = 1

End Sub

Sub Section2()

    ufNext.Left = 910
    ufNext.Top = 350

    tutSectionsRun = 2

End Sub

Sub Section3()

    Dim tutText As Shape

    Set tutText = ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 600, 300, 200, 100)
    tutText.TextFrame2.TextRange.Text = "Enter the string ""gr"" into the input box."
    tutText.Locked = False

    ufNext.Hide

    tutSectionsRun = 3

End Sub

Sub MPFilterString()

    Dim s As Variant

    Application.ScreenUpdating = False

    s = Application.InputBox("Enter string to filter out.", "Filter String.")

    If s = False Then Exit Sub

End Sub

**Edit : I forgot to mention that the userform is non-modal. Otherwise execution would pause on the call to ufNext.Show and clicking next would call the event handler before the tutSectionsRun variable had been set to 1

MILO
  • 195
  • 1
  • 2
  • 11
  • In how many places do you have ` Application.ScreenUpdating = False`? – AJD Nov 22 '19 at 04:33
  • 1
    This is a bug (it is not that the shape is not added, just the inputbox prevents screen refreshing). It won't work even without `Application.ScreenUpdating = False`. [This answer](https://stackoverflow.com/a/8561483) has some workarounds. At least 2 of those (1st and 3rd) work in this case. My other workaround would be to add `DoEvents` (twice...) before the InputBox. – BrakNicku Nov 22 '19 at 05:31
  • @BrakNicku thank you. I haven't had a chance to try this yet, it will have to wait until I get back to work on Monday (I'm in Australia and it's approx. 20:45 on Friday night here). But I am pretty confident you are correct on this. While driving home it crossed my mind that I often use `Application.Calculation = xlCalculationManual` along with `Application.ScreenUpdating = False` for a performance boost and was thinking I should have used it here and called `ActiveSheet.Calculate` in the places I wanted the user to see changes. It's good to see something that validates my thoughts. – MILO Nov 22 '19 at 09:44
  • @BrakNicku I recreated it at home from what I pasted here and as you stated, and I already knew, removing `Application.ScreenUpdating = False` alone doesn't make a difference. But neither of the three suggested workarounds worked either, alone. However, they __all__ worked in conjunction with the removal of `Application.ScreenUpdating = False`. – MILO Nov 22 '19 at 10:43
  • 1
    It is easy to create [MCVE] exposing the issue - modeless form with a button and 2 lines of code in click event - create shape, show inputbox. And yes - disabling sceen updates when you want it to refresh is a wrong choice:). – BrakNicku Nov 22 '19 at 11:20

1 Answers1

0

Thanks to @BrakNicku who confirmed my suspicions in the comments, saying that the InputBox was preventing the screen from being refreshed for the text shape from ActiveSheet.Shapes.AddLabel to be displayed.

Their link to this answer offered some suggestions.

What I found was that adding either of these before the InputBox was called would force the screen to refresh but only if Application.ScreenUpdating = False was removed, or at least just moved further down in the code.

- ActiveSheet.Calculate    
- ActiveWindow.SmallScroll    
- Application.WindowState = Application.WindowState

I always like to call Application.ScreenUpdating = False at the top of my procedures, so I went with a different approach, thinking that the problem was that the time to refresh the screen with shapes drawn was longer than the time between the instruction to do so and the instruction to draw the InputBox to screen. So, I thought a slight delay before calling the InputBox might be a better choice for me, probably not for everyone but I felt better about doing it this way. (Application.ScreenUpdating = False is a good friend of mine and I didn't want to see her go, or even be relocated) So I just made a small wait procedure.

Sub Wait(secs As Single)

    Dim finishTime As Single

    finishTime = Timer + secs

    Do While Timer < finishTime
        DoEvents
    Loop

End Sub

And called it at the top of the MPFilterString procedure. It only takes about 50ms for the shapes to show on screen but I give it 100ms to be safe.

Sub MPFilterString()

    Dim s As Variant

    WaitFor (0.1)

    Application.ScreenUpdating = False

    s = Application.InputBox("Enter string to filter out.", "Filter String.")

    If s = False Then Exit Sub

    ' more code ...

End Sub
MILO
  • 195
  • 1
  • 2
  • 11