Ball bouncing

[MAX]

I want to make a ball bouncing from left to right of the screen when I open
an excel file. Any help?

Thanks in advance.

 

[ryguy7272]

I think what you need is a progress bar. I don't know about a bouncing ball,
but you should be able to create pretty much any animation routine you want.
Look here for some basic information
http://digiassn.blogspot.com/2006/03/excel-progress-bar-indicator-with-vba.html

Or this:
http://www.cpearson.com/excel/Progress.htm

Or this:
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/

Chip and John are beyond Guru level; you can learn a lot from them!!

I have some code (slightly different form that mentioned above); it works
very well. If you want me to send it I'll send it.

HTH,
Ryan---

 

[MAX]

Thank you for your great help. At last I found someone who gave me a very
great help. Please send it to me and if you can, make it to me very easy to
understand because I'm not so fluid in these type of things.

I APPRICIATE your great help.

Thanks.

 

[ryguy7272]

Ok, below is the code. Be forewarned, you need to do some work yourself now.
BTW, did you look at those resources I referenced???

Create a UserForm. Pup this code into the UserForm:
Private Sub UserForm_Initialize()
UserCancelled = False
End Sub

The UserForm is named 'frmProgress'

Add a TextBox to the UserForm. Name the TextBox 'imgProgFore'


Add a ClassMOdule and enter this code:


Public DisableCancel As Boolean
Public Title As String

Private nVersion As Integer

Private strStatus As String
Private strStat1 As String
Private strStat2 As String
Private strStat3 As String
Private strProgress As String

Property Let Caption1(strCaption As String)

If nVersion < 9 Then

strStat1 = strCaption
UpdateStatus

Else

#If VBA6 Then

frmProgress.lblMsg1.Caption = strCaption
DoEvents

#End If

End If

End Property

Property Let Caption2(strCaption As String)

If nVersion < 9 Then

strStat2 = strCaption
UpdateStatus

Else

#If VBA6 Then

frmProgress.lblMsg2.Caption = strCaption
DoEvents

#End If

End If

End Property

Property Let Caption3(strCaption As String)

If nVersion < 9 Then

strStat3 = strCaption
UpdateStatus

Else

#If VBA6 Then

frmProgress.lblMsg3.Caption = strCaption
DoEvents

#End If

End If

End Property

Sub Finish()

If nVersion < 9 Then

Application.StatusBar = ""
Application.StatusBar = False

Else

#If VBA6 Then

Unload frmProgress

#End If

End If

End Sub

Sub Hide()

If nVersion < 9 Then

Application.StatusBar = ""
Application.StatusBar = False

Else

#If VBA6 Then

frmProgress.Hide

#End If

End If

End Sub

Property Let Progress(nWidth As Integer)
Dim nProgress As Integer

If nVersion < 9 Then

strProgress = CStr(nWidth)
UpdateStatus

Else

#If VBA6 Then

If nWidth > 100 Then nWidth = 100

If nWidth < 0 Then nWidth = 0

With frmProgress.imgProgFore

.Width = 200 - Int(nWidth * 2)
.Left = 12 + Int(nWidth * 2)

End With

DoEvents

#End If

End If

End Property

Sub Reset()

If nVersion < 9 Then

Application.StatusBar = ""
Application.StatusBar = False

Else

#If VBA6 Then

Title = ""
frmProgress.lblMsg1.Caption = ""
frmProgress.lblMsg2.Caption = ""
frmProgress.lblMsg3.Caption = ""
DisableCancel = False

#End If

End If

End Sub

Sub Show()

If nVersion < 9 Then

'probably best to leave the title out of this

Else

#If VBA6 Then

With frmProgress

If DisableCancel = True Then

.Width = 228
'.cmdCancel.Enabled = False

End If

.Caption = Title
.Show vbModeless

End With

#End If

End If

End Sub

Private Sub Class_Initialize()

nVersion = Val(Application.Version)

End Sub

Private Sub Class_Terminate()

If nVersion < 9 Then Application.StatusBar = False

End Sub

Private Sub UpdateStatus()
Dim strStatus As String

strStatus = strStat1
If strStat2 <> "" Then strStatus = strStatus & ", " & strStat2
If strStat3 <> "" Then strStatus = strStatus & ", " & strStat3

If strProgress <> "" Then strStatus = strStatus & ", " & strProgress & "%"

Application.StatusBar = strStatus

End Sub


Create a Module, and pop in this code:
Dim PB As clsProgBar
Set PB = New clsProgBar


With PB

..Title = "Progress Bar"
..Caption1 = "Executing, Please wait, this may take a short while..."
..Show
DoEvents


End With
'Application.ScreenUpdating = False
Dim sh As Worksheet
Dim sht As Worksheet

PB.Progress = 5

....now intersperse your code and more of this...


PB.Progress = 10

PB.Progress = 15

....
are you getting it...??????
....

PB.Progress = 95

PB.Progress = 100

PB.Finish

End Sub

Good luck,
Ryan---

 

[MAX]

I did everything that you wrote to me, but nothing happened in the excel file.
Do I have to put something in it and what are the resources.
Sorry for asking again, please help.

Thanks.

 

[ryguy7272]

Well... 'nothing happened' doesn't exactly help me to help you to figure out
what is wrong. All I can say for sure, is that I warned you to be
forewarned. What you want to do isn't exactly a piece of cake. On the other
hand, it's not terribly difficult either.

You can do a couple things:
#1 - Keep fiddling with it. You have what you need; you probably just have
some piece of code out of oder with the rest of the code.
#2 - You could send me the file and I will set it up for you...as long as
the data is not confidential...

(e-mail address removed)


Good luck,
Ryan---

 

[Per Jessen]

Hi

I tried it as described, and it seems that you have to rename the
ClassModue to "clsProgBar" and you also have to add a lable to the
userform named: "lblMsg1".

Then I used this macro in an ordinary module as an example.

Sub test()
Dim PB As clsProgBar
Set PB = New clsProgBar

With PB
.Title = "Progress Bar"
.Caption1 = "Executing, Please wait, this may take a short
while..."
.Show
DoEvents
End With
'Application.ScreenUpdating = False
Dim sh As Worksheet
Dim sht As Worksheet

PB.Progress = 5


For c = 1 To 1000000
A = A + 1
Next


PB.Progress = 10
For c = 1 To 1000000
A = A + 1
Next

PB.Progress = 15

For c = 1 To 10000000
A = A + 1
Next

PB.Progress = 20

For c = 1 To 10000000
A = A + 1
Next

PB.Progress = 30

For c = 1 To 10000000
A = A + 1
Next

'Increase PB.Progress and add code as desired

PB.Progress = 95

For c = 1 To 1000000
A = A + 1
Next
PB.Progress = 100
For c = 1 To 1000000
A = A + 1
Next

PB.Finish

End Sub

Hopes this helps

 

[ryguy7272]

Thanks for catching that oversight!!

Ryan---

 

[MAX]

Ryan will you please send the file please?

Thanks again.

 

[ryguy7272]

No prob. Just send me an email:
(e-mail address removed)


Thanks,
Ryan---

 

[MAX]

Hello its MAX, please will you send me the complete file Ryan?

E-Mail: (e-mail address removed)

Thanks.