VBA, USERFORM, inputbox not accepting "2" as a value

[Sandcastor]

I've built a custom print box userform, with the help of quite a few of the gurus here.

I'm using Von Pookie's code to parse the checkboxes in the form, and iterate an input box so the user can type how many copies of each checked form to print. I'm currently working on the "Sample Labels" button, because it is the easiest, and will add other buttons from there. (Checkboxes 4-12 are disabled for debugging)

The problem I am finding is that since using a For-Loop to fill the Variables, the inputboxes will not accept the number 2 as a response. Every other value works. And 2 is the most likely value to be entered in the box. Full script follows, probem area highlighted.

Private Sub PrintSampleLabels_Click()
Dim CBox As Control
Dim i As Long
Dim P As Variant
Dim N(12) As Variant
Dim NS As Variant
Dim answer
Dim Message, Title, Default
    Title = "Print Copies"                                              'Set title.
    Default = "2"                                                       'Set default.

NS = InputBox("How many sheets of labels for the injection well?", "Print Copies")
If NS = vbCancel Then NS = 0

[COLOR=#800000]For i = 1 To 12
        On Error Resume Next
        Set CBox = Me.Controls("Offset" & i)
                If CBox.Value = False Then
                    P = 0
                    GoTo Nextbox
                    End If
                P = InputBox("How many sheets of labels for offset well #" & i & "?", "Print Copies")
                If P = vbCancel Then P = 0
            N(i) = CInt(P)
Nextbox:
    Next i
[/COLOR]    
                
If WorksheetFunction.Sum(N) + NS = 0 Then GoTo TheEnd
answer = MsgBox("Load " & WorksheetFunction.Sum(N) + NS & " sheets of blank Sample Labels into the printer.", vbOKCancel, "LABELS!")
If answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
Application.ScreenUpdating = False
If NS = 0 Then GoTo Skip
    Sheets("SCHEM Sample Labels").PrintOut copies:=NS, Collate:=True, Preview:=False 'ActivePrinter:=
Skip:
    For i = 1 To 12
        Set CBox = Me.Controls("Offset" & i)
        If CBox.Value = True And N(i) > 0 Then Sheets("SCHEM OFFSET " & i & " Labels").PrintOut copies:=N(i), Collate:=True, Preview:=False 'ActivePrinter:=
    Next i
    
    
TheEnd:
Application.ScreenUpdating = True
End Sub

I have tweaked the code about six times, modified the error handlers, adjusted variable placement, and tried Integer, Long, and Variant variable types, always with the same result.
Help, please?

 

[Norie]

Have you checked the names of the controls?

 

[Sandcastor]

controls (checkboxes) are named Offset1, Offset2, Offset3, Offset4 and so on.

 

[Norie]

Where exactly does the code fail?

 

[Sandcastor]

I can't seem to pinpoint it. The code appears to execute correctly, it doesn't stor or give errors.
When the code finishes executing and the summary messagebox appears

answer = MsgBox("Load " & WorksheetFunction.Sum(N) + NS & " sheets of blank Sample Labels into the printer.", vbOKCancel, "LABELS!")

it sums up all entered values correctly, except that any values of 2 that were entered are treated as a 0.

Example: I choose three offsets to print. The script attempts to print the main labels, and the three offsets. I choose 1, 2, 3, 4copies for the four documents. I end up with 1 main label, 0x Offset#1, 3x Offset#2, 4x Offset#3

 

[Norie]

Right, I thought it was an error rather than an incorrect result.

Is the only thing that's wrong the summary?

One thing you could try is stepping through the code.

To do that put a breakpoint on the first line with F9, then open the form, click the button that triggers the code and when execution halts press F8 to step through.

As you step through you should be able to see the values of variables by hovering over them, or in the Locals Window (View>Locals Window).

You should also be able to see how the code is being executed, something which might be hard to follow because of the GoTos.

Oh, and you might want to remove the On Error Resume Next.

 

[Sandcastor]

Sweet Deal. I've never had to step through before, although I have heard the term. The LOCALS window is very helpful.

The Problem was: My error handler, which prevented a type mismatch, was not set up corectly and the rest of the script was mis-reading it. Specifically, when i used the line:

If P = vbCancel Then P = 0

I didn't realize that hitting cancel would return a null value (whereas a MSGBOX returns a vbCancel string). Secondly, the bit If P = vbCancel was evaluating to TRUE, even though vBcancel does not equal null.

Funny how an error handler can cause errors.

now it goes

For i = 1 To 12
        'On Error Resume Next
        Set CBox = Me.Controls("Offset" & i)
                If CBox.Value = False Then
                    P = 0
                    GoTo Nextbox
                    End If
                P = InputBox("How many sheets of labels for offset well #" & i & "?", "Print Copies")
                If P = "" Then GoTo Nextbox
                N(i) = CInt(P)
Nextbox:
    Next i

And it work very fine. Although i'm still not sure why it was picking on the value 2.

Thanks for the guidance!

 

[RoryA]

vbCancel is a constant which has the value 2, hence your problem.

 

[Norie]

Maybe we'll never know.

By the way, you could easily get rid of the GoTos.

For example instead of this,

For i = 1 To 12
        On Error Resume Next
        Set CBox = Me.Controls("Offset" & i)
                If CBox.Value = False Then
                    P = 0
                    GoTo Nextbox
                    End If
                P = InputBox("How many sheets of labels for offset well #" & i & "?", "Print Copies")
                If P = vbCancel Then P = 0
            N(i) = CInt(P)
Nextbox:
    Next i

you could have this.

 For i = 1 To 12

        Set CBox = Me.Controls("Offset" & i)

        If CBox.Value = False Then
               P = 0
        Else
               P = InputBox("How many sheets of labels for offset well #" & i & "?", "Print Copies")
                If P = vbNullStringThen P = 0
        End If

        N(i) = CInt(P)

  Next i

 

[Sandcastor]

at the time i was being bothered by the malfunctioning handlers, and the GoTo commands were a desperation move. Now Tidied up:

Private Sub PrintSampleLabels_Click()
Dim CBox As Control
Dim i As Long
Dim P As Variant
Dim N(12) As Long
Dim NS As Long
Dim answer

P = InputBox("How many sheets of labels for the injection well?", "Print Copies")
    If P = "" Then P = 0
    NS = CInt(P)
For i = 1 To 12
    Set CBox = Me.Controls("Offset" & i)
    If CBox.Value = False Then
        P = 0
    Else
        P = InputBox("How many sheets of labels for offset well # " & i & "?", "Print Copies")
        If P = "" Then P = 0
    End If
                    
    N(i) = CInt(P)
    Next i
If WorksheetFunction.Sum(N) + NS = 0 Then Exit Sub
    
answer = MsgBox("Load " & WorksheetFunction.Sum(N) + NS & " sheets of blank Sample Labels into the printer.", vbOKCancel, "LABELS!")
If answer = vbCancel Then Exit Sub ' the macro ends if the user selects the CANCEL-button
Application.ScreenUpdating = False
If NS > 0 Then
    Sheets("SCHEM Sample Labels").PrintOut copies:=NS, Collate:=True, Preview:=False 'ActivePrinter:=
End If
    For i = 1 To 12
        Set CBox = Me.Controls("Offset" & i)
        If CBox.Value = True And N(i) > 0 Then Sheets("SCHEM OFFSET " & i & " Labels").PrintOut copies:=N(i), Collate:=True, Preview:=False 'ActivePrinter:=
    Next i
    
Application.ScreenUpdating = True
End Sub

Like a charm.