VB corrupted

[svjack9]

I'm getting compile errors running code that ought to work and I'm wondering if vb is corrupted. Is there a way to tell. For example, the following lines are raising errors:

Set wb = Application.Workbooks("General GG Checklist.xlsm")
Set ws = wb.Worksheets("AF_CB_FV")
ws.Range("H185") = ws.Range("H185") & Me.txtMaterial2

The underlined .range is raising the error: "Method or data member not found."

I have no idea how to resolve the problem and it's happening to textboxes on forms, as well.
Thanks for any ideas you might have to offer.
svjack9

 

[shg]

In the VBE, Tools > References. Look at the ticked items (which are all at the top) for one that starts with MISSING. Untick it.

 

[svjack9]

In the VBE, Tools > References. Look at the ticked items (which are all at the top) for one that starts with MISSING. Untick it.

thanks, SHG. I tried that, but the list of ticked references doesn't show a 'missing'.
sjack9

 

[shg]

Download and run CodeCleaner (http://www.appspro.com/Utilities/CodeCleaner.htm). If that doesn't work, put a workbook on box.net and post a link.

 

[svjack9]

Download and run CodeCleaner (Excel VBA Code Cleaner Add-in). If that doesn't work, put a workbook on box.net and post a link.

I have two spreadsheets that work together in that the second,"MyMacros 1.xlsm" contains the macros and forms that are used in the first workbook: "General GG Checklist.xlsm".
Thanks so much for your help. I'm on the road quite a bit which is the reason my response is somewhat delayed. Here are the links:
https://app.box.com/s/6fdh41sso1ju1lz035rstwau8b51c8am
https://app.box.com/s/ywpo0fmvk23wvrrbrflgp4w3wpvlo3ty

svjack9

 

[shg]

Did you try CodeCleaner?

 

[svjack9]

Did you try CodeCleaner?

Yes, I ran codecleaner and it didn't fix the problem with VB not recognizing the "range" object. Incidentally, the form where the error is occurring is frmChemApplication. it occurs in the 'chemical' tab when you open the combo box and select 'apples'.

 

[shg]

Neither of those projects compile for me.

In MyMacros frmChemApplication, for example, there is no control named cboChemCrop or txtChemBlock.

Many functions (e.g., IsValidControl) in MyMacros are called from the other project. Do you set a reference? Likewise, you use classes defined in MyMacros in the other workbook.

What I don't see is the error you describe.

These workbooks contain thousands and thousands of lines of code. Did you write this?

 

[svjack9]

Neither of those projects compile for me.

In MyMacros frmChemApplication, for example, there is no control named cboChemCrop or txtChemBlock.

Many functions (e.g., IsValidControl) in MyMacros are called from the other project. Do you set a reference? Likewise, you use classes defined in MyMacros in the other workbook.

What I don't see is the error you describe.

These workbooks contain thousands and thousands of lines of code. Did you write this?

That is the problem, it won't compile. In form frmChemApplication there is a multipage control with a tab named "Chemicals". cboChemCrop and txtChemBlock should be there.
As to your other questions, yes, I've been working on this thing for two years and there is dead code that I don't use anymore as I've experimented and found better ways to accomplish certain tasks. However, I'm afraid to delete it because at this point I'm not sure if a sub or function is still being referenced. Bad programming, I know. Maybe I should bag it and start over.
I'm a food safety auditor and this checklist makes up the core of my audit. I've been creating forms to assure that the elements of each control point (e.g., CB 8.6.4) are covered and my comments pasted in column H of the General GG Checklist conform with the requirements of technical review.

 

[shg]

That's curious -- cboChemCrop appears in IntelliSense, but still won't compile. This is typical of the kind of problem that CodeCleaner frequently fixes, but no dice today.

I tried renaming the control, and that was going well for a while -- but after changing the last instance in the code, the error came back.

I'd keep hunting the forums for a day or so before tackling the big rework. Sorry I don't have a better suggestion.