×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

For anyone who has a little spare time...

thread707-41173

For anyone who has a little spare time...

For anyone who has a little spare time...

(OP)
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:

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 Sub


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

RE: For anyone who has a little spare time...

Hi Kevin!

First of all: No i can't solve your problem!

You said somthing about an error-message ("...can't find required library or object..." ).
I have no idea about this progress bar but it sounds like the progress bar is not a standard component of Excel(or VBA).
I think that you have to copy the file (library, object, class, or whatever) which includes the progress bar to the girl's machine where your macro is meant to run on.

RE: For anyone who has a little spare time...

I agree with frag. Check the project references and make sure to include all of the non-standard object libraries with the project.

RE: For anyone who has a little spare time...

(OP)
The progress bar is a simple form I made using the form builder in VBA.  Nothing special.

Shoot me an e-mail if you want and I'll send you guys the app.  You don't have to do anything with it, but you can see the bar is just a form.

Kevin
slanek@ssd.fsi.com

RE: For anyone who has a little spare time...

Hi Kevin,

this if frag again!


I don't know if you already have solved your problem (you wanted to send me a mail).

Now I've got a very similar problem!!

I am using the standard VBA-functions Mid() and Date() in Access97 on NT machines. The only machine where my code runs is the machine on which I have developed the programm. On nearly all other machines I got compiling-errors (Can't find project or library).

I guess that there are missing some dll's... but I have copied all dll-files from my machine (the good one) to another machine... no effect. I used the files from "C:\Program Files\Common Files\Microsoft Shared\VBA".

I am really running mad on this!!!

Help me out of this... please!

frag

patrick.metz@epost.de

RE: For anyone who has a little spare time...

(OP)
Hey Patrick,
I never figured out how to make it work.  I actually ended up leaving the progress bar out.

Kevin
slanek@ssd.fsi.com

RE: For anyone who has a little spare time...

Frag: Did you package it all up and install it properly or just copy the exe file?
If the former you have to make sure you include all the DLL references needed when making the distribution disk.
If the latter, you wont have installed the DLLs needed.

RE: For anyone who has a little spare time...

Hi tedsmith!

I didn't package it and I have no exe file. (By the way: How the hell do I package an Access97-database or get an exe file out of it ?!?) It is just the mdb file with to forms in it! I want to put the mdb file on a networkdrive because serveral people have to use it at the same time for storing data in the database. That's all.

cya

frag

patrick.metz@epost.de

RE: For anyone who has a little spare time...

TO KEVIN AND TEDSMITH!!

This is frag with some news:

Tata... I DID IT! I solved my problem! How? Here we go (believe it or not):

I went to the machine where my code didn't want to run and opened two(!) Access applications. I opened my database in both apps. Then I kicked all forms out of one application and copied the form objects (radio buttons, textfields, etc.)in formwizard mode via drag and drop to new (empty) forms in the second app. Then I copied the whole code from app 1 to app 2. Save and done! Now it workes on all machines. Don't ask why!!

Perhapse Kevin should try this with his status bar too?!

cya

frag *very happy*

patrick.metz@epost.de

RE: For anyone who has a little spare time...

(OP)
No kidding?  I guess anything is worth a shot!

Kevin
slanek@ssd.fsi.com

RE: For anyone who has a little spare time...

In the Access2 there was a developers program that you could use to produce a runtime royalty free program so you did not have to have a legal copy of Access in the computer. I presume the is an update for Access 97 that will do the same? You then had only the data in the mdb file which you attached to the main program. This makes it much easier to update the forms, queries and reports without affecting existing data.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close