Problems with multiple userforms

[sbe70]

Hi,

I am running iterative calculations in a worksheet and want to show the progress. I am using a userform for the progress report. The problem is that I am getting a new userform every time I am showing the progress.

I have seen in other threads that I can use xxx.Repaint but fails (it complains). Can someone please show me a very easy example of how to use .Show and .Repaint for userforms?

Thanks
Stefan

 

[Norie]

Stefan

What code are you using to show the progress?

 

[sbe70]

Hi,

I am using Visual Basic, not Java.

Sending the code so you can see what I do.

--------------------------------------------------------------------
Sub Show_status(cab_opt_eff, cab_pen_tot, i, improv, nmx, nummx)
Dim Status As Object
Dim NewLabel As MSForms.Label
Dim tit(1 To 10) As String
Dim X As Integer
Dim pr(1 To 5) As Double
'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False
Set Status = ThisWorkbook.VBProject.VBComponents.Add(3)

'Create the User Form
With Status
.Properties("Caption") = "Optimisation Status"
.Properties("Width") = 800
.Properties("Height") = 300
End With
Set NewLabel = Status.designer.Controls.Add("Forms.label.1")
With NewLabel
.Name = "FieldLabel" & X + 1
.Caption = "Performance"
.Top = 20
.Left = 20
.Width = 100
.Height = 16
.Font.Size = 8
.Font.Name = "Arial"
.Font.Bold = True
.Font.Underline = True
End With
'Create year titles
For X = 2012 To 2018
Set NewLabel = Status.designer.Controls.Add("Forms.label.1")
With NewLabel
.Name = "FieldLabel" & X + 1
.Caption = X
.Top = 20
.Left = 200 + (X - 2012) * 75
.Width = 75
.Height = 16
.Font.Size = 8
.Font.Name = "Arial"
.Font.Bold = True
.Font.Underline = True
End With
Next X
'Create performance titles
tit(1) = "Optimisation efficiency"
tit(2) = "Total cabinet penalty"
tit(3) = "Number of iterations"
tit(4) = "Number of improvements"
tit(5) = "Maximum number"

For X = 1 To 6
Set NewLabel = Status.designer.Controls.Add("Forms.label.1")
With NewLabel
.Name = "FieldLabel" & X + 1
.Caption = tit(X)
.Top = 20 + X * 25
.Left = 20
.Width = 150
.Height = 16
.Font.Size = 8
.Font.Name = "Arial"
.Font.Bold = True
End With
Next X
For y = 2012 To 2018

' pr(1) = Format((cab_opt_eff - cab_pen_tot) / cab_pen_tot, "#.##%")
pr(1) = (cab_opt_eff - cab_pen_tot) / cab_pen_tot
pr(2) = Format(cab_pen_tot, "###,###,###")
pr(3) = i
pr(4) = improv
pr(5) = nummx

For r = 1 To 5
Set NewLabel = Status.designer.Controls.Add("Forms.label.1")
With NewLabel
' .Name = "FieldLabel" & r + 1
.Caption = pr(r)
.Top = 20 + r * 25
.Left = 200 + (y - 2012) * 75
.Width = 150
.Height = 16
.Font.Size = 8
.Font.Name = "Arial"
' .Font.Bold = True
End With

Next r

Next y

'Show the form
If improv(2012) = 1 Then
VBA.UserForms.Add(Status.Name).Show vbModeless
DoEvents
Else
VBA.UserForms.Add(Status.Name).Repaint vbModeless
DoEvents
End If
End Sub
----------------------

I also have problems with format numbers in the userform, see commented line after the for statement.

Stefan

 

[Norie]

Stefan

Who said anything about Java?

Anway, why are you creating a new form from scratch?

That's going to happen every time you run that code.

Now I'm not 100% sure but that doesn't sound like a good idea.

I can't even see how that would work with something that is meant to show progress, unless you were basing the new form on the previous form.

Couldn't you just create a form right at the start, or even create a form in design mode?

Apart from that, what's the problem with the formattin?

No formatting, wrong formatting?

 

[sbe70]

Hi,

Thanks for your patient. I am quite good in Excel but not in Visual Basic.

I think I don't have a hang of how I can create a userform and then update the values. Could you please explain how I should go about this? When shall I create the userform with "Set NewLabel=..." and apply .Show and .Repaint or should I use another method?

Thanks,
Stefan

 

[Norie]

Stefan

Just open the Visual Basic editor, goto Insert>Userform and you'll have a userform.

Then you can add all the controls you want, in your case probably a label that can be resized and display a message - that would be your 'progress bar'.

I can't really help with the exact code for progress bars but you'll find examples if you search here and elsewhere.

The way I it's work, as far as I know anyway, is you call code to update the form at regular intervals in the code you are monitoring.

Updating would involve things like resizing the label so it appears to expand across the form and/or displaying messages in the label to inform the user of progress.

You would repaint after each update.

That's the basics, I'm sure there are other things involved.

 

[sbe70]

Hi,

I am sorry for my lack of knowledge.

I have tried according to your lineout but still get multiple userforms unless if I use repaint. Then I get an error. Could you please write an very short example with a sub Main calling a sub Show_progress where I "create/update" the userform and show it?

Stefan

 

[sbe70]

Sorry, forgot to add that the calling sub Show_progress from sub Main should be in a loop in sub Main....

 

[Norie]

Stefan

What exactly did you try?

Did you create a form in design view, or are you still using code to create it?

 

[sbe70]

Code