having a problem with if then elseif

[Zygoid]

what I am trying to do is

if textbox15 = "" then run some code and exit sub
or
if textbox15 <> "" then run some code and exit sub

what is happening is

if textbox15 = "" then runs some code (not exiting sub)and then runs if textbox15 <> "" then runs the some code.

problem comes because when "manual" is placed in textbox15 then runs the second if statement. I cannot seem to get it to not run the second if statement once "manual" is placed in textbox15.

anyone have an idea?

Private Sub TextBox15_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Rng As Range

With TextBox15
    If .Value = "" Then
        TextBox15.Value = "Manual"
        TextBox1.Value = Format$(Date, "m/d/yyyy")
        userform3.Show
        Exit Sub
    End If

    If .Value <> "" Then
        Set Rng = ActiveWorkbook.Sheets("database").Columns(9).Find(TextBox15.Value)
        If Not Rng Is Nothing Then
            If MsgBox("text for message1") = vbOK Then
            Cancel = True
            End If
            With TextBox15
                .SelStart = 0
                .SelLength = Len(.Value)
                .SetFocus
            End With
            Exit Sub
        ElseIf Rng Is Nothing Then
            If MsgBox("text for message2") = vbNo Then
                Cancel = True
                With TextBox15
                    .SelStart = 0
                    .SelLength = Len(.Value)
                    .SetFocus
                End With
                Exit Sub
            ElseIf vbYes Then
                TextBox15.Value = "Manual"
                TextBox1.Value = Format$(Date, "m/d/yyyy")
                userform3.Show
                Exit Sub
            End If
        End If
    End If
End With

End Sub

 

[Bong]

Not sure but why not use Else for the second case?

_________________
Bob Rashkin

 

[Zygoid]

I have tried that too. elseif too. even tried select case and still coming up with the same problem. This is just the last code i have tried to get the same results

 

[ck1999]

Can you explain this better:

problem comes because when "manual" is placed in textbox15 then runs the second if statement. I cannot seem to get it to not run the second if statement once "manual" is placed in textbox15.

ck1999

 

[Bong]

Something doesn't seem right. In an If...Then...Else...End If structure, the code, in one pass through, should not be able to execute in both sections.

Now, I don't know if this has any possible connection, but I notice that in your second code (what would be the Else part, you again have With TextBox15...End With while still inside the first With TextBox15...End With. This shouldn't be necessary and might (although I don't know why) screw up the interpreter.

_________________
Bob Rashkin

 

[ck1999]

Not sure what you are saying about the second if statement. But if you are refering to the second after .value<>""

which is

  If MsgBox("text for message2") = vbNo Then
                Cancel = True
                With TextBox15
                    .SelStart = 0
                    .SelLength = Len(.Value)
                    .SetFocus
                End With
                Exit Sub
            ElseIf vbYes Then
                TextBox15.Value = "Manual"
                TextBox1.Value = Format$(Date, "m/d/yyyy")
                userform3.Show
                Exit Sub
            End If

It might be that vbno and vbyes are not choices since the msgbox is only an "OK" button. So therefore it always (don't know why) is going to vbyes.

ck1999

 

[HughLerwill]

'Manual' <> 'manual'

 

[strongm]

I would suspect that

TextBox15.Value = "Manual"
TextBox1.Value = Format$(Date, "m/d/yyyy")
userform3.Show

is triggering a chain of events which are in turn causing TextBox15_Exit to be called a second time after the Value is set to 'Manual'

 

[macropod]

Hi Zygoid,

As with the other commentators, it seems to me you have some redundant code that might be part of the problem. How about something like:

Private Sub TextBox15_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Rng As Range
With TextBox15
    If Trim(.Value) <> "" Then
        Set Rng = ActiveWorkbook.Sheets("database").Columns(9).Find(TextBox15.Value)
        If Not Rng Is Nothing Then
            MsgBox "text for message1"
            Cancel = True
            .SelStart = 0
            .SelLength = Len(.Value)
            .SetFocus
            Exit Sub
        Else
            If MsgBox("text for message2", vbYesNo) = vbNo Then
                Cancel = True
                .SelStart = 0
                .SelLength = Len(.Value)
                .SetFocus
                Exit Sub
            ElseIf vbYes Then
                .Value = "Manual"
                TextBox1.Value = Format$(Date, "m/d/yyyy")
                userform3.Show
                Exit Sub
            End If
        End If
    Else
        .Value = "Manual"
        TextBox1.Value = Format$(Date, "m/d/yyyy")
        userform3.Show
    End If
End With
End Sub

Cheers

[MS MVP - Word]