Help Needed With Error

[afrazier1221]

I'm building an estimator for business use. On the work order page of the estimator, there are textboxes for labor hours and labor dollars. Because they affect each other (hours affect the dollars, and the dollars affect the hours), I have the hours and labor dollars interactive insomuch as to say that when you change the hours value, it automatically updates the labor dollar value, and vice versa. This allows me to input the hours and get a dollar amount, or if I need to insert a specific dollar amount, it converts the hours to match. That said, I had to write code to prevent an endless loop, so each of the boxes checks on Change() if the control being changed is the active control. If it's the active control, it changes the other control. If it's not the active control, then it just allows itself to be changed and leaves the corresponding control unaltered.

The code and the setup worked great. No problems. But when I introduced an outside code to auto-populate the textboxes of the work order page, it throws an error. Something about what I've done doesn't like the coding. I'm not sure why.

This works fine by itself:

Private Sub INVEST8_Change()
If INVEST8 = Me.MultiPage1.Pages(3).Frame10.ActiveControl Then
    If Not IsNumeric(INVEST8.Value) = True Then
        If INVEST8.Value = "." Or INVEST8.Value = "-" Then
        Sheet6.Cells(10, 29).Value = INVEST8.Value
        Sheet6.Cells(14, 29).Value = INVEST8.Value
        INVEST9.Value = 0
        INVEST9.Value = Format(Me.INVEST9.Value, "$#,##0.00")
        Else
        INVEST8.Value = ""
        Sheet6.Cells(10, 29).Value = INVEST8.Value
        Sheet6.Cells(14, 29).Value = INVEST8.Value
        INVEST9.Value = ""
        End If
    Else
        Sheet6.Cells(10, 29).Value = INVEST8.Value
        Sheet6.Cells(14, 29).Value = INVEST8.Value
        INVEST9.Value = Sheet5.Cells(2, 38).Value
        INVEST9.Value = Format(Me.INVEST9.Value, "$#,##0.00")
    End If
Else
    If Not IsNumeric(INVEST8.Value) = True Then
        If INVEST8.Value = "." Or INVEST8.Value = "-" Then
        Sheet6.Cells(10, 29).Value = INVEST8.Value
        Sheet6.Cells(14, 29).Value = INVEST8.Value
        Else
        INVEST8.Value = ""
        Sheet6.Cells(10, 29).Value = INVEST8.Value
        Sheet6.Cells(14, 29).Value = INVEST8.Value
        End If
    Else
        Sheet6.Cells(10, 29).Value = INVEST8.Value
        Sheet6.Cells(14, 29).Value = INVEST8.Value
    End If
End If

INVEST9, which is the corresponding labor dollars textbox control, has a similar code and also works just fine, and in harmony with INVEST8.

However, when I change the value of INVEST8 with this other bit of coding . . .

With Me.MultiPage1.Pages(3)
C = 1
D = 0
E = 4
For i = 1 To 117
    F = D + C
    G = "INVEST" & F
    .Controls(G).Value = ""
    .Controls(G).Value = Sheet5.Cells(E, 45).Value
    If C = 6 Or C = 7 Then
    Me.MultiPage1.Pages(3).Controls(G).Value = Format(Me.MultiPage1.Pages(3).Controls(G).Value, "$#,##0.00")
    End If
    If C = 8 Then
    Me.MultiPage1.Pages(3).Controls(G).Value = Format(Me.MultiPage1.Pages(3).Controls(G).Value, "#0.0#")
    End If
    If C = 9 Then
    Me.MultiPage1.Pages(3).Controls(G).Value = Format(Me.MultiPage1.Pages(3).Controls(G).Value, "$#,##0.00")
    C = 1
    D = D + 10
    E = E + 1
    Else
    C = C + 1
    E = E + 1
    End If
Next i
End With

. . . it has a problem with this line in the first code given previously.

If INVEST8 = Me.MultiPage1.Pages(3).Frame10.ActiveControl Then

I did some experimenting. This is the line that it's objecting to. I'm just not sure why. If I change the value of INVEST8 via INVEST9, it all works just fine. When I change the value of INVEST8 via the second bit of code, it objects to it. I get the error, "Object variable or With block variable not set"

I'm assuming that something needs to be more properly defined. However, the second code works fine for INVEST1 through INVEST7. It recognizes the variables and is able to retrieve the data I want to populate the controls with, write it to the textbox controls, and format the values as appropriate to dollars or similar. It even works for the INVEST8 control that I'm having trouble with when I take that one line of code out. But when it sees that one line, it gets finicky. And since nothing in that one line of code is even a variable, I'm kind of at a loss as to where I'm going wrong.

Any help is greatly appreciated.

 

[afrazier1221]

UPDATE:

I've isolated the issue to the highlighted line below. The code is writing the value to INVEST8, but throws the error when it tries to format the value. And again, it writes it and formats it just fine without the If INVEST8 = Me.MultiPage1.Pages(3).Frame10.ActiveControl Then line of code in there.

With Me.MultiPage1.Pages(3).Frame10
C = 1
D = 0
E = 4
For i = 1 To 117
    F = D + C
    G = "INVEST" & F
    .Controls(G).Value = ""
    .Controls(G).Value = Sheet5.Cells(E, 45).Value
    If C = 6 Or C = 7 Then
    Me.MultiPage1.Pages(3).Frame10.Controls(G).Value = Format(Me.MultiPage1.Pages(3).Frame10.Controls(G).Value, "$#,##0.00")
    End If
    If C = 8 Then
    Me.MultiPage1.Pages(3).Frame10.Controls(G).Value = Format(Me.MultiPage1.Pages(3).Frame10.Controls(G).Value, "#0.0#")
    End If
    If C = 9 Then
    Me.MultiPage1.Pages(3).Frame10.Controls(G).Value = Format(Me.MultiPage1.Pages(3).Frame10.Controls(G).Value, "$#,##0.00")
    C = 1
    D = D + 10
    E = E + 1
    Else
    C = C + 1
    E = E + 1
    End If
Next i
End With

So I'm guessing that the variable G is somehow conflicting with the operation of the ActiveControl check. I'm just at a loss.

 

[Anthony47]

I recommand using a flag to prevent the loop. For example:
-on top of the vba module you declare a common variable:

dim StayCalm as Boolean

-then at the begin of "change" routine you use

If StayCalm Then Exit Sub
StayCalm=True
'... your Change routine

-before exit the Sub, don't forget

StayCalm=False

Does it make sense?

 

[afrazier1221]

I recommand using a flag to prevent the loop. For example:
-on top of the vba module you declare a common variable:

dim StayCalm as Boolean

-then at the begin of "change" routine you use

If StayCalm Then Exit Sub
StayCal=True
'... your Change routine

-before exit the Sub, don forget

StayCal=False

Does it make sense?

Don't miss afrazier's message, above

It does make sense. It would eliminate that one line of code that's being problematic in this instance.

 

[Peter_SSs]

@afrazier1221
When posting vba code in the forum, please use the available code tags, not quote tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have changed the tags for one of your post 1 codes but not the other so you can clearly see the difference .

 

[afrazier1221]

@afrazier1221
When posting vba code in the forum, please use the available code tags, not quote tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have changed the tags for one of your post 1 codes but not the other so you can clearly see the difference .

Gotcha.

 

[Peter_SSs]

Cheers. So I've altered most of the rest now too.

 

[afrazier1221]

I resolved the problem. I was/am using a MultiPage setup. The Listbox selection that triggered the population event on the Work Order page was on a different page. The MultiPage setup was not agreeable to testing if a control on another page was the ActiveControl. Despite half a dozen different ways of trying to specifically delineate the control that was being tested, right down to the frame, nothing wanted to work. So I ultimately ended up doing:

MultiPage1.Value = 3
INVEST1.SetFocus

And for those of you messing with MultiPage setups, my particular scenario required setting the focus inside the frame itself. Being on the page wasn't enough. To test for the active control, I had to have my focus on the multipage page, and on one of the cells in the frame.
Thanks to anyone who took the time to try and solve this issue.