• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Activate/deactivate MsgBox based on Combo Box value

I have a userform and the following code notifies users if the sales proposal is current or expired. It works fine. Once the order is completed, I don't want these messages to keep popping up. So I need to add a condition based on the value (stage) in a combo box.

I want the messages to be 'disabled' if the stage selected in the combo box is Completed or Canceled.
txtExpDate = expiration date (proposal date + 60 days)
txtPropDate = proposal date
txtPORecDate = PO received date
cboStage = combo box list: Waiting to Process, Waiting for PO, Waiting Approval, Standby Completed, Canceled

I've been wrestling with this and no luck. I'm still new to VBA.
Thank you in advance for your consideration.

Code:
'***Expiration Date***
Dim x As Date
Dim y As Date
    If IsDate(DateValue(txtPropDate.Value)) Then
            txtExpDate.Value = DateValue(txtPropDate.Value) + (60)
            txtExpDate.Enabled = False
            x = DateValue(txtPropDate.Value) + (60)
            y = DateValue(txtPORecDate.Value)
   With txtExpDate
    If x < y Then
            MsgBox "This proposal is expired. Requires management approval or an updated proposal."
            txtExpDate.BackColor = vbYellow
    ElseIf x >= y Then
            MsgBox "This proposal is current. Proceed with order entry."
            .BackColor = 12648384
    End If
    End With
    End If
 
Ok, I went back and identified the list index for each stage as follows:
StageList Index
Waiting to Process0
Waiting PO/Quote Revision1
Waiting Approval2
Standby3
Completed4
Canceled5

Here is my code, but the messages are still popping up if the Stage is Completed or Canceled. This sub is larger than I've included. Is saying 'Exit Sub' appropriate since I want it to continue onto the next line of code, not the next Private Sub?

Code:
Private Sub UpdateandSaveOrder_Click()
On Error Resume Next

'***Expiration Date***
Dim x As Date
Dim y As Date
  If IsDate(DateValue(txtPropDate.Value)) Then
            txtExpDate.Value = DateValue(txtPropDate.Value) + (60)
            txtExpDate.Enabled = False
            x = DateValue(txtPropDate.Value) + (60)
            y = DateValue(txtPORecDate.Value)
  End If
  With txtExpDate
    If x < y Then
            MsgBox "This proposal is expired. Requires management approval or an updated proposal."
            txtExpDate.BackColor = vbYellow
    ElseIf x >= y Then
            MsgBox "This proposal is current. Proceed with order entry."
            .BackColor = &HFFFFC0
  End If
  End With
  With cboStage
   If cboStage.ListIndex > 3 Then Exit Sub
   End With

UPDATE: I had to remove this code it seemed to 'break' other lines that were working fine before. Back to the drawing board...
 
Last edited:
Back
Top