vbNo doesnt stop the execute

[mysticmario]

Hi, For some reason when vbNo is pressed the code inlucding input boxes still executes. How can I break it? and move on to next With ws execution?

Set cell = rng.Find(what:="Płyta", LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell <> "" And cell.Offset(, 1) <> 0 Then
    MsgBox "Dodać płytę HDF? ", vbYesNo
    If vbYes Then
    kolor = InputBox("biała czy surowa?", "Kolor HDF")
    ile = InputBox("Ile szt?", "Ilość")
    
    With ws
T = Application.WorksheetFunction.CountA(.Range("B11:B30,K11:K30,T11:T30"))
        If T >= 60 Then MsgBox "Full line.Please check data": Exit Sub
            If T > 0 Then
            X = Int(T / 20)
            lr = .Cells(30, S(X)).End(3).Row
            Else
            lr = 10
            X = 0
            End If
        End With
        lr = lr + 1
    If lr < 31 Then
        If X <= 2 Then
        ws.Cells(lr, S(X)).Value = "Płyta HDF" & kolor
        ws.Cells(lr, S(X)).Offset(, 1).Value = ile & "szt"
    Else
            MsgBox "Check the return area": Exit Sub
        End If
    Else
        X = X + 1
        lr = lr - 20
        If X <= 2 Then
        ws.Cells(lr, S(X)).Value = "Płyta HDF" & kolor
        ws.Cells(lr, S(X)).Offset(, 1).Value = ile & "szt"
                Else
                    MsgBox "Check the return area": Exit Sub
                End If
            End If
        End If
     End If

 

[Ruddles]

vbYes is always true. You have to store the result of the MsgBox and test that:-

If cell <> "" And cell.Offset(, 1) <> 0 Then
    variablename=MsgBox ("Dodać płytę HDF? ", vbYesNo)
    If variablename=vbYes Then

 

[mysticmario]

vbYes is always true. You have to store the result of the MsgBox and test that:-

If cell <> "" And cell.Offset(, 1) <> 0 Then
    variablename=MsgBox ("Dodać płytę HDF? ", vbYesNo)
    If variablename=vbYes Then

Thank you, I always had this problem - now I know why

 

[Ruddles]

Great! Click the 'Like' thumb?

 

[rlv01]

FWIW, you no not necessarily have to store the result in a variable. You can also test the msgbox statement directly

    If cell <> "" And cell.Offset(, 1) <> 0 Then
          If MsgBox("Dodac plyte HDF? ", vbYesNo) = vbYes Then
                kolor = InputBox("biala czy surowa?", "Kolor HDF")
                ile = InputBox("Ile szt?", "Ilosc")

or use a Select Case statement

    If cell <> "" And cell.Offset(, 1) <> 0 Then
        Select Case MsgBox("Dodac plyte HDF? ", vbYesNo)
            Case vbYes
                kolor = InputBox("biala czy surowa?", "Kolor HDF")
                ile = InputBox("Ile szt?", "Ilosc")

 

[mysticmario]

so many cool solutions. Thanks I'll be going back to this thread from tiem to time so it's good to have alternatives

 

[mysticmario]

I'm back at it.
Now I encountered a different issue

Set cell = rng.Find(what:="Tandembox", LookIn:=xlFormulas, _
        lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell <> "" And cell.Offset(, 1) <> 0 Then
    variablename = MsgBox("Czy dodać zestaw sprzęgieł do prowadnic Tandembox?", vbYesNo)
     If variablename = vbYes Then

    With ws
T = Application.WorksheetFunction.CountA(.Range("B11:B30,K11:K30,T11:T30"))
        If T >= 60 Then MsgBox "Full line.Please check data": Exit Sub
            If T > 0 Then
            X = Int(T / 20)
            lr = .Cells(30, S(X)).End(3).Row
            Else
            lr = 10
            X = 0
            End If
        End With
        lr = lr + 1
    If lr < 31 Then
        If X <= 2 Then
        ws.Cells(lr, S(X)).Value = "Zest. sprzęgieł (P/L) z regulacją do prowadnicy Tandembox"
        ws.Cells(lr, S(X)).Offset(, 1).Value = cell.Offset(, 1)
    Else
            MsgBox "Check the return area": Exit Sub
        End If
    Else
        X = X + 1
        lr = lr - 20
        If X <= 2 Then
        ws.Cells(lr, S(X)).Value = "Zest. sprzęgieł (P/L) z regulacją do prowadnicy Tandembox"
        ws.Cells(lr, S(X)).Offset(, 1).Value = cell.Offset(, 1)
                Else
                    MsgBox "Check the return area": Exit Sub
                End If
            End If
        End If
     End If

This bad boy is trying to find word "tandembox"
If it finds it and I press yes, it adds extra items to the list, and if I press no it doesnt do that, and moves on.
However if the word "tandembox" does not appear in my sheet which may and will happen the code breaks here:

If cell <> "" And cell.Offset(, 1) <> 0 Then

Any idea how to fix this issue?

 

[Ruddles]

Go here: https://excelmacromastery.com/excel-vba-find/ and look for the section titled The Find Return Value. That should sort you out.

Or here: https://www.educba.com/vba-find/ and look for Deal with Error Values in Excel VBA Find.

 

[rlv01]

...However if the word "tandembox" does not appear in my sheet which may and will happen the code breaks here:

If cell <> "" And cell.Offset(, 1) <> 0 Then

Any idea how to fix this issue?

With any find operation your code must be prepared to handle the case where what you are searching for is not found.

    If Not cell Is Nothing Then
        If cell <> "" And cell.Offset(, 1) <> 0 Then

Also, since this thread is already marked as solved, you should not continue to post your problems here, but instead start a new thread.

 

[mysticmario]

With any find operation your code must be prepared to handle the case where what you are searching for is not found.

    If Not cell Is Nothing Then
        If cell <> "" And cell.Offset(, 1) <> 0 Then

Also, since this thread is already marked as solved, you should not continue to post your problems here, but instead start a new thread.

Thanks for your assitance, it was solved until it wasn't. I was actually trying to reach the same person that help me before, that is why i went bacdk to this thread.
Thank you your give solution helped. I sometimes forget machines are dumb.