VBYes/No Responses

[Pacman52]

Good evening all - my inexperience with VBA is again showing and despite many many google searches I just can't seem to get the code below to work, Please could someone point me in the right direction or let me know what I've got wrong.

Private Sub CkbAncestors_Click()

    If CkbAncestors.Value = True Then
        TextboxAncestors.Enabled = True
        TextboxAncestors.SetFocus
        TextboxAncestors.Text = ""
        TextboxAncestors.BackColor = RGB(204, 255, 255)

    Else
    
        TextboxAncestors.BackColor = RGB(255, 255, 255)

    End If
    
End Sub

Private Sub TextboxAncestors_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    If CkbAncestors.Value = True And Trim(TextboxAncestors.Value) = "" Then
    TextboxAncestors.BackColor = RGB(255, 255, 255)
    
    MsgBox "It looks like you've not entered the number of bottles sold." & vbCrLf & vbCrLf & _
    "Do you want to enter a number?", vbYesNo, "Quantity Required"

    If vbYes Then

    Me.TextboxAncestors.SetFocus
    Me.TextboxAncestors.BackColor = RGB(204, 255, 255)

    Else
    
    CkbAncestors.Value = False
    Me.TextboxAncestors.Text = "0"
    Me.TextboxAncestors.BackColor = RGB(255, 255, 255)


    End If
    End If
End Sub

So the 'CkbAncestors_Click' works fine, the first issue I'm having is setting the focus back to 'TextboxAncestors' if a users clicks yes on the msgbox but the BackColour changes.

The next issue is with the 'Else', again the BackColor bit works ok but thats all that works in that 'block'

Basically what I'm trying to achieve is if a user ticks the checkbox the focus goes to the associated textbox but if the user leaves the textbox empty when they exit it they are prompted with a yes/no msgbox to let them know. If they select yes then the focus is sent back to the textbox for them to enter a number. If they choose no then the checkbox loses it tick, the textbox get a value of 0 and the BackColor goes back to white.

Just in case its important to know I have 47 versions of the above on the form and the Tabstop property is set to false on all of them when the Userform opens,

Hopefully some of the above makes some sense but more than happy to answer any questions if needbe.

Many thanks in anticipation

Paul

 

[Scott Huish]

In General you have to assign the value of the message box to a variable

Dim x
x = MsgBox("It looks like you've not entered the number of bottles sold." & vbCrLf & vbCrLf & _
    "Do you want to enter a number?", vbYesNo, "Quantity Required")

if x=vbYes then ...

 

[DanteAmor]

In the Exit event, you should use Cancel = True instead of setfocus.

Use this:

Private Sub TextboxAncestors_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim res As VbMsgBoxResult
  
  If CkbAncestors.Value = True And Trim(TextboxAncestors.Value) = "" Then
    TextboxAncestors.BackColor = RGB(255, 255, 255)
    
    res = MsgBox("It looks like you've not entered the number of bottles sold." & vbCrLf & vbCrLf & _
                 "Do you want to enter a number?", vbYesNo, "Quantity Required")
  
    If res = vbYes Then
      Cancel = True
      Me.TextboxAncestors.BackColor = RGB(204, 255, 255)
    Else
      CkbAncestors.Value = False
      Me.TextboxAncestors.Text = "0"
      Me.TextboxAncestors.BackColor = RGB(255, 255, 255)
    End If
  End If
  
End Sub

 

[Pacman52]

Thank you both so much for your replies, setting the MsgBox variable has done the trick so I can now move on with the workbook.

If its not to much trouble, could you explain why it has to be done this way? only so I can gain an understanding and know for future reference.

Once again thanks Paul

 

[DanteAmor]

could you explain why it has to be done this way? only so I can gain an understanding and know for future reference.

If you use the msgbox in a simple way, for example:

MsgBox: "Hello"
There is no need to store a response.

But in your case you need to know what the user's response is, it can be VbYes or VbNo, then it is necessary to store that response in a variable, to later verify what the user selected. Just as I explained in my code.

    res = MsgBox("It looks like you've not entered the number of bottles sold." & vbCrLf & vbCrLf & _
                 "Do you want to enter a number?", vbYesNo, "Quantity Required")
  
    If res = vbYes Then

The way you have it you are not verifying the answer that the user selected in the MsbBox. In this case, it will always be true.

If vbYes Then

You can also do it directly as follows:

Private Sub TextboxAncestors_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  
  If CkbAncestors.Value = True And Trim(TextboxAncestors.Value) = "" Then
    TextboxAncestors.BackColor = RGB(255, 255, 255)
    
    If MsgBox("It looks like you've not entered the number of bottles sold." & vbCrLf & vbCrLf & _
                 "Do you want to enter a number?", vbYesNo, "Quantity Required") = vbYes Then
      Cancel = True
      Me.TextboxAncestors.BackColor = RGB(204, 255, 255)
    Else
      CkbAncestors.Value = False
      Me.TextboxAncestors.Text = "0"
      Me.TextboxAncestors.BackColor = RGB(255, 255, 255)
    End If
  End If
  
End Sub

I hope it is clearer now.

 

[Pacman52]

Thank you so much I really appricate you taking the time to explain this for me as now I can understand why its done this way.

Paul