Refresh on VBA

[trsisko]

Can anyone confirm if Refresh works for VBA or if I can use something similar?


Me.Label2.Refresh
Me.Label2.Width = 361.1 * (m_QID / m_TotalUniqueQuestions)
Me.Label2.Caption = Format((m_QID / m_TotalUniqueQuestions), "0.0%")
Me.lblQID.Caption = m_QID
Me.lblQuestion.Caption = m_Question
Me.LblDesc.Caption = strQInfo(2)

 

[Richard Schollar]

Depends what you want a command such as Refresh to do - you can Repaint a userform, but neither a label nor a Userform have a Refresh property/method.

 

[trsisko]

Heres the code, Baiscally its a progress bar. Prior to this bar I copy a sheet to the use the code. The control label2 doesnt update the progress bar length according to the new sheet.

    'Load Controls with Data
    Me.Label2.Width = 361.1 * (m_QID / m_TotalUniqueQuestions)
    Me.Label2.Caption = Format((m_QID / m_TotalUniqueQuestions), "0.0%")
    Me.lblQID.Caption = m_QID
    Me.lblQuestion.Caption = m_Question
    Me.LblDesc.Caption = strQInfo(2)

 

[Andrew Poulsom]

Here is an example of a progress bar:

http://www.j-walk.com/ss/excel/tips/tip34.htm

 

[trsisko]

I've updated the code with something I found in Google. Looks like if I change the Label properties to Visible then not visible in may update. I'll unit test this and see if it works.

'Load Controls with Data
Dim n As Long
For n = 1 To 10
Label2.Visible = Not Label2.Visible
DoEvents
Me.Label2.Width = 361.1 * (m_QID / m_TotalUniqueQuestions)
Me.Label2.Caption = Format((m_QID / m_TotalUniqueQuestions), "0.0%")
Me.lblQID.Caption = m_QID
Me.lblQuestion.Caption = m_Question
Me.LblDesc.Caption = strQInfo(2)
Next n

 

[Richard Schollar]

If you are doing this using a userform (seems likely) then you can use the Repaint method of the userform.

 

[trsisko]

Didn't seem to work as expected. How would I define the repaint method?

label2.repaint?

 

[Richard Schollar]

No, as I said it is a method of the Userform, so you would run your process (ie update the label size) then call the Repaint method of the Userform:

Me.Label2.Width = 361.1 * (m_QID / m_TotalUniqueQuestions)
Me.Label2.Caption = Format((m_QID / m_TotalUniqueQuestions), "0.0%")
Me.lblQID.Caption = m_QID
Me.lblQuestion.Caption = m_Question
Me.LblDesc.Caption = strQInfo(2)
Me.Repaint

Note that you must have these controls on a userform for this to work (a worksheet has no Repaint method).