Can't run code - Reference Error - Please help

[Upex]

Hi All,

I've been working on a document at work and now want to finish it off at home, yet I'm encountering errors that are making the document useless.

At work we use a thin client (citrix) network, with EX 11 (2003?) and at home I'm using 2007 (shouldnt have bothered for the hasle it gives with things like this).

When I open the document and do something that runs a macro, even a very basic one like:

Private Sub Worksheet_Activate()
    With ActiveWindow
        .SmallScroll Down:=-200
        .SmallScroll ToRight:=-200
    End With
[a1].Activate
End Sub

It comes up with a compile error: Can't find project or library. My references says "MISSING: AppSenseCCDTP 1.0 Type Library"

Anyone have any ideas how I go about getting this in my home XL so I can actually use it?

When it has errored, it debugs to my use of shorthand ranges i.e. [a1] rather than range("a1"). I have amended one to the long hand version and it ran without error. I'm not able to change the all range references to long hand, as there are thousands within the document.

Any suggestions please as I'm stuck!

Also,
With my code above, my scrolls are to move the screen to the very top and very left, i.e. a1 as they have freeze panes and when activating a1, the part past the freeze panes was staying at random places (where last used), is there a way to specifiy this without using the scrolls?

Many thanks for any suggestions and I hope you all have an excellent weekend.

Upex.

 

[Upex]

Has anyone got even the slightest sugestion please?

 

[Upex]

Hi All,

Still struggling with this and have found nothing online. I have also found that its erroring code everywhere. using date to set a datepicker default value, the ranges as previously mentioned, loading user forms etc etc

Does anyone have an idea on how I can get past this?

If not, does anyone know if I can install an older version of Office/Excel (say 2003) on my PC as well as 2007 without problems? and then run this in the older version so it doesnt cause issues?

Many thanks,

Upex.

 

[VoG]

Sorry, I cannot offer any specific advice. Take a look here http://www.cpearson.com/excel/MissingReferences.aspx

If you install an earlier version of Excel you should uninstall the later version first, install the earlier version, then re-install the later version.

 

[Upex]

HI VoG, hope your well.

Thanks for the link, I've checked it out and followed it's suggestions. So far, squat. Just waiting for a repair of Office, then I guess I'm truely stuck, if it doesnt work that is. The missing reference is nothing that I've come across before and as there is nothing on line about it, I'm thinking it may be something specific to my company and their network setup, in which case, again I'm stuffed lol.

O well, guess I'll try the 'unistall of 07, install of 03 or XP and reinstall of 07' route next, or sack off 07 all together.

Many thanks for your reply though.

Enjoy the bank holiday.

Upex.

 

[Norie]

Upex

Is the problem happening both at home and work?

Have you tried locating the missing library?

The path and filename that Excel is trying to use for it should still be shown when you select it in Tools>References.

 

[Upex]

Hi Norie,

The file works perfectly at work, no issues at all. I emailed it home (compressed as 10,636kb), uncompressed and started to work on it. when cam to running a simple macro (the one above) it bugged out because I used [a1].

I just went to check the filepath and decided to uncheck the reference. I then tried running the code (above) with the range as [a1] and it worked. Tried the userform, and that seemed to work as well. FIngers crossed it wasnt actually doing anything then and the file will be OK.

Regards the file path, having just unchecked the reference, it isnt in the list at all. I've checked 3 time under M, A and at the top and bottom of the list. It's vanished.

Any ideas as to what it was, why it was there, will it do any harm to not have it ticked and where it's gone to?

I'm baffled!

Upex.

 

[Norie]

I thought you'd already tried unchecking the reference but it hadn't worked.

Unchecking is the normal way to 'fix' this type of formula.

Anyway it's working now and that's the important thing.

 

[Upex]

Hi Norie,

Yeah, all sorted. Although when I continued to work on the document at work, I once again had missing references (different ones that werent on the reference list at home). Unchecked these, changed all ranges to range(" ") rather than [] and it seems to run OK. I wonder though, how references can be added whilst a document is being emailed from home to work and visa versa, as the references that came up missing (at either end) werent ever included in the original build, so it seems they were somehow included whilst emailing the document home, and then back to work. Strange.

Many thanks for everyones input here.

Regards, Upex.