Combobox change event

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
149
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,268
Office Version
  1. 365
Platform
  1. Windows
How are you populating the combobox?
 

Diving_Dan

Board Regular
Joined
Oct 20, 2019
Messages
149
By doubleclicking on any cell on a worksheet opens the userform using this code.

VBA 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

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,340
Messages
5,624,106
Members
416,011
Latest member
chengkoonwing

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top