Take a look at this code. It's a very basic progress bar running along with a macro that pulls info from one wookbook into another and formats it a certain way.
PROBLEM: This works great on my machine, but when I gave it to the girl who was going to be using it, it didn't work. It failed at the very beginning and immediately highlighted the line that says newHour = Hour(Now()) If I remove all the code that deals with updating the progress bar, it works fine on her machine. The error says something about "...can't find required library or object..." or something of that nature.
QUESTION 1: Of course I want to know why this thing won't work with the progress bar on one machine, but will work fine on another.
QUESTION 2: I know that I've probably chosen the worst possible way to update the percentage displayed on the progress bar. Any suggestions there?
And here's the code ladies and gentlemen:
The code is pretty darn slpooy, but it works. I know there is a better way to change the percentage on the progress bar, but don't know how. This is for anyone who is bored, can't sleep, is on a coffee high, etc...
Later,
Kevin
slanek@ssd.fsi.com
PROBLEM: This works great on my machine, but when I gave it to the girl who was going to be using it, it didn't work. It failed at the very beginning and immediately highlighted the line that says newHour = Hour(Now()) If I remove all the code that deals with updating the progress bar, it works fine on her machine. The error says something about "...can't find required library or object..." or something of that nature.
QUESTION 1: Of course I want to know why this thing won't work with the progress bar on one machine, but will work fine on another.
QUESTION 2: I know that I've probably chosen the worst possible way to update the percentage displayed on the progress bar. Any suggestions there?
And here's the code ladies and gentlemen:
Code:
Sub EmpNum()
'
' Macro8 Macro
' Macro recorded 11/3/2000 by Kevin A. Slane
'
'Update progress bar
PctDone = 0
Call UpdateProgress(PctDone)
Workbooks.Open Filename:= _
"\\blah\blah\TimeEntry.xls"
Windows("TimeEntry.xls").Activate
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Range("C2:C652").Select
Selection.Copy
Windows("Vacation_Data.xls").Activate
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Sheets("Sheet2").Visible = True
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Sheets("Sheet2").Select
Range("A1").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
ActiveSheet.Paste
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Range("B2").Select
Application.CutCopyMode = False
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
ActiveCell.FormulaR1C1 = _
"=IF((AND((LEN(Sheet2!R[-1]C[-1])=5),(NOT(ISBLANK(Sheet2!R[-1]C[-1]))))),(Sheet2!R[-1]C[-1]-60000),IF(ISBLANK(Sheet2!R[-1]C[-1]),"""",Sheet2!R[-1]C[-1]))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B652"), Type:=xlFillDefault
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Range("B2:B652").Select
Range("B2").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Windows("TimeEntry.xls").Activate
Range("I2:I652").Select
Selection.Copy
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Windows("Vacation_Data.xls").Activate
Range("C2").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
ActiveSheet.Paste
Range("C2").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Range("D2").Select
Windows("TimeEntry.xls").Activate
Range("J2:J652").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Application.CutCopyMode = False
Selection.Copy
Windows("Vacation_Data.xls").Activate
Range("D2").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
ActiveSheet.Paste
Range("D2").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Range("D2").Select
Windows("TimeEntry.xls").Activate
Range("H2:H652").Select
Selection.Copy
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Windows("Vacation_Data.xls").Activate
Range("E2").Select
ActiveSheet.Paste
Columns("C:E").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1
Columns("C:E").Select
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Windows("TimeEntry.xls").Activate
ActiveWorkbook.Close
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
Windows("Vacation_Data.xls").Activate
Sheets("Sheet2").Visible = False
Range("A1").Select
Sheets("Sheet1").Visible = False
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
ActiveWorkbook.Save
'Update progress bar
PctDone = PctDone + 0.05
Call UpdateProgress(PctDone)
'Update progress bar
PctDone = 1
Call UpdateProgress(PctDone)
'Pause for 1 second
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Unload UserForm1
Shell "MSAccess \\blah\blah\vacation_app\vacation", vbMaximizedFocus
End Sub
Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End SubThe code is pretty darn slpooy, but it works. I know there is a better way to change the percentage on the progress bar, but don't know how. This is for anyone who is bored, can't sleep, is on a coffee high, etc...
Later,
Kevin
slanek@ssd.fsi.com
