VB corrupted

svjack9

New Member
Joined
Feb 9, 2014
Messages
32
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 9, 2014
Messages
32
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
 

svjack9

New Member
Joined
Feb 9, 2014
Messages
32

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
Did you try CodeCleaner?
 

svjack9

New Member
Joined
Feb 9, 2014
Messages
32

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
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?
 
Last edited:

svjack9

New Member
Joined
Feb 9, 2014
Messages
32
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,784
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,804
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top