Solved: Problem in creating status bar

[arrun]

Dear all, I was trying to put a status-bar for my project, however could not achieve what I am wanting :(

Attaching my rough Excel file. My goal is once user click Buton1 the status bar should show indicating various stages of calculation. I created 3 indicators as UserForm1, UserForm2, UserForm3 (please refer to the Excel file). However it seems that, the contents of the UserForms is not appearing.

Can somebody help me with the right approach?

Thanks and regards,

[Trebor76]

Hi arrun,

Try this - just assign the macro My_Code directly to the macro button on Sheet1:


Option Explicit
Sub My_Code()
'Adapted by Trebor76
'Visit my website www.excelguru.net.au (http://www.excelguru.net.au)

UserForm1.Show

Dim i As Long, j As Long, Sum1 As Double

'Some Dummy code...
Sum1 = 0
For i = 1 To 10000
Sum1 = Sum1 + i
Next i

Call CloseUserForm("UserForm1")

UserForm2.Show

'Some Dummy code...
Sum1 = 0
For i = 1 To 10000
Sum1 = Sum1 + i
Next i

Call CloseUserForm("UserForm2")

UserForm3.Show

'Some Dummy code...
Sum1 = 0
For i = 1 To 10000
Sum1 = Sum1 + i
Next i

Call CloseUserForm("UserForm3")

End Sub
Sub CloseUserForm(strFormName As String)
Dim intFormCount As Integer

For intFormCount = VBA.UserForms.Count - 1 To 0 Step -1
If UserForms(intFormCount).Name = strFormName Then
Unload VBA.UserForms(intFormCount)
Exit For
End If
Next intFormCount

End Sub

Regards,

Robert

[arrun]

it is not working as I wanted :( Basically, I can feel that UserForms are changing however, the ***contents of the UserForms*** are not visible..

In the 'Module1', I have pasted following code:


Sub Button1_Click()
My_Code
End Sub

Sub My_Code()
'Adapted by Trebor76
'Visit my website www.excelguru.net.au

UserForm1.Show

Dim i As Long, j As Long, Sum1 As Double

'Some Dummy code...
Sum1 = 0
For i = 1 To 10000000
Sum1 = Sum1 + i
Next i

Call CloseUserForm("UserForm1")

UserForm2.Show

'Some Dummy code...
Sum1 = 0
For i = 1 To 10000000
Sum1 = Sum1 + i
Next i

Call CloseUserForm("UserForm2")

UserForm3.Show

'Some Dummy code...
Sum1 = 0
For i = 1 To 10000000
Sum1 = Sum1 + i
Next i

Call CloseUserForm("UserForm3")

End Sub
Sub CloseUserForm(strFormName As String)
Dim intFormCount As Integer

For intFormCount = VBA.UserForms.Count - 1 To 0 Step -1
If UserForms(intFormCount).Name = strFormName Then
Unload VBA.UserForms(intFormCount)
Exit For
End If
Next intFormCount

End Sub

Am I missing something?

Thanks,

[Trebor76]

Hi arrun,

See how the attached goes where I've put the code into the forms themselves.

Part of the issue is that the code is going so fast that the forms aren't staying open long enough to be noticed, plus there were a few fields on the forms whose visible properties had been set to false (i.e. not to be shown).

Regards,

Robert

[arrun]

Thank you so much Trebor76. This is what I wanted. Thanks for your effort. :)

[Trebor76]

Thanks for the feedback and I'm glad we got it solved for you.