• Hey all! It's a wonderful day in the neighborhood I see :) :)

    Here's another head-scratcher for you. I have a main form defined and a function that is called from there via a button control takes about 40-60 seconds to run. So, in keeping with the basic GUI rules, I change the mousepointer to the hourglass and set the userform.enabled=false property so the can't accidentally (or intentionally) move or close the form.

    First step, as usual, is to step through the new code and all seemed to work well. Then, I ran it normally and when the mousepointer changes back, everything should be done. The only problem was that I couldn't click on any of the controls nor could I close the form with the closebox (upper right).

    Hmmmmmmm I said to myself. Back to single step mode and all works fine. After the sub finishes, I can click on any control and/or close the form. I did that 5-6 times in a row without actually closing it and then just told it to run and I couldn't close it again. I even tried adding in a "sleep 100" before and after the userform.enabled=true.

    Since my head already hurts, I thought I'd share the joy with you guys and see if anyone has any ideas.

  • Re: userform.enable

  • Re: userform.enable

    Well, based on the fact that most of the threads here get suggestions/answers/whatever relatively quickly and noone has replied, I'm guessing noone has any idea.

    So, for now at least, I guess I'll turn off the enable=false stuff but leave the hourglass.

    Thanks guys!!!!!

  • Re: userform.enable

    Try adding some additional code to your userform to stop movement and closing.
    [vba]Option Explicit

    Private m_blnRunningCode As Boolean
    Private m_sngLeft As Single
    Private m_sngTop As Single
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Private Sub btn_UpdateBoard_Click()

    m_blnRunningCode = True
    m_sngLeft = Me.Left
    m_sngTop = Me.Top
    Me.MousePointer = fmMousePointerHourGlass

    Sleep 1200
    ' Call Module1.CalculateHandicap
    ' Call Module1.Update_StatsBoard
    ' Call UserForm_Initialize

    Me.MousePointer = fmMousePointerDefault
    m_blnRunningCode = False

    End Sub
    Private Sub UserForm_Layout()
    ' prevent moving userform whilst running code
    If m_blnRunningCode Then
    Me.Move m_sngLeft, m_sngTop
    End If
    End Sub
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Prevents use of the Close button whilst run your code
    If m_blnRunningCode Then
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    End If
    End If
    End Sub[/vba]

  • Re: userform.enable

    I see what you are trying to do and I really appreciate it.

    But, I'd rather try to figure out why, what seems like simple code, does not work as expected. If the enable=true works in single step mode, why does it not work in normal run mode?

    Is there something else that Excel does to the form when you set enable=false? I searched the help and found nothing else on it except how it's managed for controls.

  • Re: userform.enable

    What ever is happening in single step mode is something that is behind the scenes. The same code in xl97 works as expected.
    If I come across a more detail answer for the non working of the code I will post it.

    In the meantime this seems to work.
    [vba]Private Sub btn_UpdateBoard_Click()
    DisableUserform Me
    frm_Home.MousePointer = fmMousePointerHourGlass
    Call Module1.CalculateHandicap
    Call Module1.Update_StatsBoard
    Call UserForm_Initialize
    Sleep 100
    EnableUserform Me
    Sleep 100
    frm_Home.MousePointer = fmMousePointerDefault
    End Sub[/vba]
    Standard Code Module[vba]Sub DisableUserform(Frm As MSForms.UserForm)
    Frm.Enabled = False
    End Sub
    Sub EnableUserform(Frm As MSForms.UserForm)
    Frm.Enabled = True
    End Sub[/vba]

  • Re: userform.enable

    Andy, AWESOME!!! I've seen similar things before on PCs and don't know why I didn't think to try it.

    I have verified that it does work to fix my problem! I owe you a cold one!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!