Combobox change event

[Diving_Dan]

Evening all,

I'm having a few issues with a combobox and a change event.

I've got the following code on a combobox within a userform. It works fine and makes the changes that I want it to make by changing the background colour and showing a messagebox if the value is changed to "Cancelled".

The only issue I am having is that everytime the userform is launched and the value in the combobox is "Cancelled" I get the messagebox which I don't want to happen. Is there anyway of preventing this from happening? I did consider trying the AfterUpdate procedure but I need the change to happen as I select the item from the combobox. I hope I make sense.

Any help will be appreciated.

Private Sub cboStatus_Change()
    
    If cboStatus.Value = "Open" Then
    Me.cboStatus.BackColor = &H80FF80
    End If
    
    If cboStatus.Value = "Complete" Then
    Me.cboStatus.BackColor = &HFF&
    End If
    
    If cboStatus.Value = "Cancelled" Then
    Me.cboStatus.BackColor = &HC0C0C0
    End If
    
    If cboStatus.Value = "Cancelled" Then
        
        Dim answer As Integer
        
        answer = MsgBox("Are you sure you want to cancel this invoice?" & vbNewLine & "This cannot be undone.", vbYesNo + vbCritical, "Cancel invoice?")
        If answer = vbNo Then
            Me.cboStatus.Value = "Open"
        End If
        If answer = vbYes Then
            For Each ctl In Me.Controls
            If TypeName(ctl) = "TextBox" Then ctl.Enabled = False
            Next ctl
            
            For Each ctl In Me.Controls
            If TypeName(ctl) = "ComboBox" Then ctl.Enabled = False
            Next ctl

        End If
    End If
    
End Sub

 

[Norie]

How are you populating the combobox?

 

[Diving_Dan]

By doubleclicking on any cell on a worksheet opens the userform using this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Cells(ActiveCell.Row, 1).Activate
    With frmViewUpdateRecord
    .textRecordNumber.Value = ActiveCell.Value
    .cboStatus.Value = ActiveCell.Offset(, 1).Value
    .Show
    End With
    
End Sub

 

[NoSparks]

try setting the combo text to Cancelled in its properties window then if you load the combo with Cancelled when the form opens I don't think it will be a change.

 

[Diving_Dan]

Excellent problem solved. Thanks for your help