.XLA won't run / errors

[Mixture]

Hi guys,

I'm creating a .xla file so I can distribute my macro as an add-in to my friends and collegues.
However, the macro doesn't seem to work at all.
I made an .xla file, opened it and it works perfectly.
I can click the buttons I'm supposed to and some of the 'code' seems to run.

But as soon as I call subs, it errors on stuff like:
- Sheets("Sheet").Range("A1:A10000").NumberFormat = "@"
- c (for cell) is an undefined variable

Is this because what I'm trying to do is in a different workbook/project or something?
If I drag the module over to the workbook where the sheets are it crashes too.
I would love to hear what I can do to troubleshoot/figure out what I'm doing wrong.
And please start at the basics, it could be something as silly as not having my monitor plugged in

 

[RoryA]

What error are you getting on that line? Is the code in a normal module (not a worksheet or ThisWorkbook)?

 

[Mixture]

In the workbook I have:

With ToolsMenuControl
.Caption = "&Check voor bladen"
.OnAction = "'" & ThisWorkbook.Name & "'!Test"
.Tag = C_TAG
End With

And I just run it in ThisWorkBook.
However the sheets I want to reach are in a different project/workbook but that seems to go just fine.
I'm doing my best to make it work and so far I'm getting ahead quite nicely.
it seemed like it didn't take kindly to 'cell' because I didn't 'Dim Cell As Range' at the start of the sub

 

[Mixture]

I think the errors just came from a lack of defining variables.
It's sort of solved for me now. (this problem is, but there are many more to come)

Thanks for your help

 

[Mixture]

It actually seems like I do need some more help.
For some reason the code wouldn't run because there is already code present.
Is there any way to override the code a sheet already has?
Because at the moment the code is erroring and that won't let me execute my code.

 

[RoryA]

Unless you post your code and tell us what error is occurring and where, it makes it pretty hard for us to help.

 

[Mixture]

The code is pretty huge, the problem is when I drag a sheet from a file(destination) into a different file(source) it says stuff like cannot find object and compiler error.
40036 is the errorcode I believe. (already checked mrexcel for that, didn't help me)
It will only work when I delete the VBA code from that sheet/macro.
Is there a way to prevent that or something?
Maybe by creating my own sheet and overriding the file I used to drag in?
That way I can make everything directly from the source and copy it to destination?

or maybe I'm just going off on a tangent.

 

[RoryA]

That would suggest that your code requires a non standard reference that is not set in the target workbook or refers to an object that doesn't exist in that workbook. You either need to late bind (to fix the reference) or change the code so that it's not workbook specific.

Beyond that I can't really comment as your question basically boils down to "My code doesn't work - how do I fix it" and without the code, I can't answer that.

 

[Mixture]

The problem is that I can't mess with that code.
I only have my part of the code that only works when the other parts is not misbehaving like it is.
I'm sorry that I'm no help whatsoever in supplying the needed things.
That said, I really appreciated your help.
I will try to figure it out for myself.
Thanks again