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

 

[svjack9]

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.

You've put a lot more time and effort into it than I expected. Thank you so much.

 

[shg]

You're welcome. There are lots of smart guys on the forum -- I'm optimistic one can help.

Good luck.

 

[RoryA]

Where are txtMachinery and txtPHI?

 

[jaslake]

Hi svjack9

I find several things...

Changing

Me.cboChemCrop

to

Me.cboChemCrop.Value

allows cboChemCrop to be recognized.

You appear to not have a Control named Me.txtMachinery...you do have one named Me.txtChemMachinery
You appear to not have a Control named Me.txtPHI...you do have one named Me.txtChemPhi
You appear to not have a Control named Me.TxtDate

Quick observations...

 

[shg]

Well, here's a pair of white knights now!

 

[snb_]

I have no idea why you should create so many checkboxes at runtime.
I'd prefer to do this in desgn mode.

On top of that your code is unnecessarily complicated.
This might be an alternative to create 900 (!?) checkboxes.

Private Sub UserForm_Initialize()
    sn = Workbooks("0_MyMacros 1.xlsm").Worksheets("Countries").Columns(1).SpecialCells(2)

    For Each pg In multipageRiskAssessments.Pages
       For j = 1 To UBound(sn)
          With pg.Controls.Add("Forms.Checkbox.1", sn(j, 1), True)
            .Top = Workbooks("0_MyMacros 1.xlsm").Worksheets("Countries").Rows(j).Top
            .Caption = sn(j, 1)
          End With
        Next
    Next
End Sub

 

[TnTinMn]

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".
... Here are the links:
https://app.box.com/s/6fdh41sso1ju1lz035rstwau8b51c8am
https://app.box.com/s/ywpo0fmvk23wvrrbrflgp4w3wpvlo3ty

... 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'.

I don't know if you have uploaded new versions of these two workbooks since posting, but if I open both workbooks and run frmChemApplication and select the chemical tab then apples, it works fine. If I select apples from the fertilizer tab, I get an index out of range error in

Sub RestoreFertilizerApplication()Set wb = Application.Workbooks("General GG Checklist")

This is easily corrected by adding the ".xlsm" extension. The allowance for lack of the extension is controlled by the "Hide extensions for know file types" setting in Windows, so this may not be an issue for you, but the extension applied form is supported regardless of the Windows setting.

This same issue occurs in

Sub RestoreChemicalApplication()
and
Sub SaveChemicalApplication()

For what it's worth, I'm using Excel 2007.