MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12Next
Return Post new threads
View: 562|Reply: 12

How do I change a userform while calculations are ongoing.

[Copy link]

2

Topics

4

Posts

14

Integral

Member

Rank: 2

Integral
14
Post on 12-13-2016 11:34:18 | All posts |Read mode
I have written a form based application which interactively allows a user to set up a model puzzle.  The structure is entirely form based.  After all the setup is done the user may activate an algorithm to solve the puzzle.  This algorithm may take many minutes to run.  I would like to be able to update the user, as to what is happening, while the processing is going on.  Thank you very much for any suggestions or help you can provide.
Joe




Reply

Props Report

2

Topics

804

Posts

1720

Integral

King

Rank: 6Rank: 6

Integral
1720
Post on 12-13-2016 13:08:43 | All posts



How about using the Status Bar
'some processing code
Application.StatusBar = Step1 completed
'more code
Application.StatusBar = Step2 completed
more code
Application.StatusBar = All steps completed
'To return control of the status bar back to Excel, set its value to FALSE.
Application.StatusBar = False
Reply Support Opposition

Props Report

2

Topics

804

Posts

1720

Integral

King

Rank: 6Rank: 6

Integral
1720
Post on 12-13-2016 18:03:45 | All posts



How about using the Status Bar
'some processing code
Application.StatusBar = Step1 completed
'more code
Application.StatusBar = Step2 completed
more code
Application.StatusBar = All steps completed
'To return control of the status bar back to Excel, set its value to FALSE.
Application.StatusBar = False




Reply Support Opposition

Props Report

2

Topics

4

Posts

14

Integral

Member

Rank: 2

Integral
14
 Author| Post on 12-13-2016 19:40:38 | All posts



I am sure that will work - Thank you very much.
I was hoping there would be a way to either initiate a new form or using the current form change it as the algorithm is processing.
Do some processing update label in current form.
Again Thank you very much.
Reply Support Opposition

Props Report

2

Topics

4

Posts

14

Integral

Member

Rank: 2

Integral
14
 Author| Post on 12-13-2016 19:55:40 | All posts



I am sure that will work - Thank you very much.
I was hoping there would be a way to either initiate a new form or using the current form change it as the algorithm is processing.
Do some processing update label in current form.
Again Thank you very much.




Reply Support Opposition

Props Report

2

Topics

804

Posts

1720

Integral

King

Rank: 6Rank: 6

Integral
1720
Post on 12-13-2016 20:03:29 | All posts



Try this and see if you prefer it


Open the file, enable macros and click on Run
(The VBA contains a loop to simulate your code running)
At various stages in the code (here at the end of each loop)
- the code writes a status update to cell A1
- the UserForm is refreshed by being re-initialized
- TextBox1 takes the latest value from cell A1
Private Sub cb_Run_Click()
Range(A1).Select
DoEvents
Do Until x = 10
    Application.ScreenUpdating = False
        x = x + 1
            For i = 1 To 3000
                ActiveCell.Offset(1, 0).Select
            Next i
    Application.ScreenUpdating = True
    Range(A1).Value = Stage   x
    If x = 10 Then Range(A1).Value = Status Dormant
    DoEvents
    Range(A1).Select
    UserForm_Initialize
    Application.ScreenUpdating = True
Loop
   
End SubPrivate Sub UserForm_Initialize()
TextBox1.Text = Range(A1).Value
DoEvents
End SubPrivate Sub CommandButton1_Click()
    UserForm1.Show vbModeless
End SubPrivate Sub Workbook_Open()
    UserForm1.Show vbModeless
End Sub








  • SimpleUserFormUpdatingOnTheHoof.xlsm
    (22.6 KB, 0 views)
    Download

  • Reply Support Opposition

    Props Report

    2

    Topics

    804

    Posts

    1720

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1720
    Post on 12-14-2016 00:31:47 | All posts



    Try this and see if you prefer it


    Open the file, enable macros and click on Run
    (The VBA contains a loop to simulate your code running)
    At various stages in the code (here at the end of each loop)
    - the code writes a status update to cell A1
    - the UserForm is refreshed by being re-initialized
    - TextBox1 takes the latest value from cell A1
    Private Sub cb_Run_Click()
    Range(A1).Select
    DoEvents
    Do Until x = 10
        Application.ScreenUpdating = False
            x = x + 1
                For i = 1 To 3000
                    ActiveCell.Offset(1, 0).Select
                Next i
        Application.ScreenUpdating = True
        Range(A1).Value = Stage   x
        If x = 10 Then Range(A1).Value = Status Dormant
        DoEvents
        Range(A1).Select
        UserForm_Initialize
        Application.ScreenUpdating = True
    Loop
       
    End SubPrivate Sub UserForm_Initialize()
    TextBox1.Text = Range(A1).Value
    DoEvents
    End SubPrivate Sub CommandButton1_Click()
        UserForm1.Show vbModeless
    End SubPrivate Sub Workbook_Open()
        UserForm1.Show vbModeless
    End Sub








  • SimpleUserFormUpdatingOnTheHoof.xlsm
    (22.6 KB, 2 views)
    Download

  • Reply Support Opposition

    Props Report

    2

    Topics

    804

    Posts

    1720

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1720
    Post on 12-14-2016 00:51:23 | All posts



    Try this and see if you prefer it


    Open the file, enable macros and click on Run
    (The VBA contains a loop to simulate your code running)
    At various stages in the code (here at the end of each loop)
    - the code writes a status update to cell A1
    - the UserForm is refreshed by being re-initialized
    - TextBox1 takes the latest value from cell A1
    Private Sub cb_Run_Click()
    Range(A1).Select
    DoEvents
    Do Until x = 10
        Application.ScreenUpdating = False
            x = x + 1
                For i = 1 To 3000
                    ActiveCell.Offset(1, 0).Select
                Next i
        Application.ScreenUpdating = True
        Range(A1).Value = Stage   x
        If x = 10 Then Range(A1).Value = Status Dormant
        DoEvents
        Range(A1).Select
        UserForm_Initialize
        Application.ScreenUpdating = True
    Loop
       
    End SubPrivate Sub UserForm_Initialize()
    TextBox1.Text = Range(A1).Value
    DoEvents
    End SubPrivate Sub CommandButton1_Click()
        UserForm1.Show vbModeless
    End SubPrivate Sub Workbook_Open()
        UserForm1.Show vbModeless
    End Sub








  • SimpleUserFormUpdatingOnTheHoof.xlsm
    (22.6 KB, 4 views)
    Download

  • Reply Support Opposition

    Props Report

    2

    Topics

    804

    Posts

    1720

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1720
    Post on 12-14-2016 11:22:57 | All posts



    Try this and see if you prefer it


    Open the file, enable macros and click on Run
    (The VBA contains a loop to simulate your code running)
    At various stages in the code (here at the end of each loop)
    - the code writes a status update to cell A1
    - the UserForm is refreshed by being re-initialized
    - TextBox1 takes the latest value from cell A1
    Private Sub cb_Run_Click()
    Range(A1).Select
    DoEvents
    Do Until x = 10
        Application.ScreenUpdating = False
            x = x + 1
                For i = 1 To 3000
                    ActiveCell.Offset(1, 0).Select
                Next i
        Application.ScreenUpdating = True
        Range(A1).Value = Stage   x
        If x = 10 Then Range(A1).Value = Status Dormant
        DoEvents
        Range(A1).Select
        UserForm_Initialize
        Application.ScreenUpdating = True
    Loop
       
    End SubPrivate Sub UserForm_Initialize()
    TextBox1.Text = Range(A1).Value
    DoEvents
    End SubPrivate Sub CommandButton1_Click()
        UserForm1.Show vbModeless
    End SubPrivate Sub Workbook_Open()
        UserForm1.Show vbModeless
    End Sub








  • SimpleUserFormUpdatingOnTheHoof.xlsm
    (22.6 KB, 10 views)
    Download

  • Reply Support Opposition

    Props Report

    2

    Topics

    804

    Posts

    1720

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1720
    Post on 12-14-2016 14:11:10 | All posts



    Try this and see if you prefer it


    Open the file, enable macros and click on Run
    (The VBA contains a loop to simulate your code running)
    At various stages in the code (here at the end of each loop)
    - the code writes a status update to cell A1
    - the UserForm is refreshed by being re-initialized
    - TextBox1 takes the latest value from cell A1
    Private Sub cb_Run_Click()
    Range(A1).Select
    DoEvents
    Do Until x = 10
        Application.ScreenUpdating = False
            x = x + 1
                For i = 1 To 3000
                    ActiveCell.Offset(1, 0).Select
                Next i
        Application.ScreenUpdating = True
        Range(A1).Value = Stage   x
        If x = 10 Then Range(A1).Value = Status Dormant
        DoEvents
        Range(A1).Select
        UserForm_Initialize
        Application.ScreenUpdating = True
    Loop
       
    End SubPrivate Sub UserForm_Initialize()
    TextBox1.Text = Range(A1).Value
    DoEvents
    End SubPrivate Sub CommandButton1_Click()
        UserForm1.Show vbModeless
    End SubPrivate Sub Workbook_Open()
        UserForm1.Show vbModeless
    End Sub








  • SimpleUserFormUpdatingOnTheHoof.xlsm
    (22.6 KB, 12 views)
    Download

  • Reply Support Opposition

    Props Report

    12Next
    Return Post new threads

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 11-18-2017 04:58 , Processed in 0.192369 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!