We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How do you trap a combo box index change and cancel it if required

ahillier
ahillier asked
on
Medium Priority
2,142 Views
Last Modified: 2013-12-25
Hi,

I need to stop and ask the user if they really want to change the selected index in a combo box whenever there are changes to the data on the form.  If the user cancels the index change then I need to set the index back to the original value and prevent the values in any other controls dependent on the combo box selection from being changed.  I got it working but its very convoluted and I figured there must be an easier way.

Thanks
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Use the combo box's BeforeUpdate to read the value, and based on whatever logic you choose function accordingly...

Private Sub YourComboBox_BeforeUpdate(Cancel as Integer)

Select Case Me.YourComboBox.Value
   Case "Banana"
        if msgBox("Are you really sure you want to change this to banana?", vbYesNo, "?") = vbNo then
              'User hit 'No button.  Bail.
              Cancel = True
              Docmd.CancelEvent
              exit sub
       else
             'User hit 'Yes' button. ok.
       end if
   Case "Orange"
       'Do Orange stuff here
End Select

End Sub

Author

Commented:
Am I missing something?  There is no method "BeforeUpdate" in the list of available methods for a combo box.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Please disregard my comment.  I gave you an Access answer, and didn't notice that you asked the question in the VB Controls TA.
Mike TomlinsonHigh School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009

Commented:
Is this less convoluted that what you have?

Option Explicit

Private lastIndex As Integer
Private dirty As Boolean

Private Sub Form_Load()
    lastIndex = -1
    dirty = True ' set this when your "record" has been changed
End Sub

Private Sub Combo1_Click()
    Dim curIndex As Integer
    curIndex = Combo1.ListIndex
    If lastIndex <> -1 And curIndex <> lastIndex Then
        If dirty Then
            If MsgBox("Change without saving?", vbYesNo, "Changes will be lost") = vbNo Then
                Combo1.ListIndex = lastIndex
                Exit Sub
            End If
        End If
    End If
   
    If curIndex <> lastIndex Then
        lastIndex = curIndex
        Debug.Print "Index Changed: " & curIndex
    End If
End Sub
Senior IT Developer
Commented:
'Change combo box style to drop down
'---------------------------------------------------

Dim iComboIndex As Integer
Private Sub Combo1_Click()
    If Not Combo1.Visible Then
        iComboIndex = Combo1.ListIndex
        Exit Sub 'No message when form is loading
    End If
   
    If iComboIndex <> Combo1.ListIndex Then
        If MsgBox("Do you want to change item to " & Combo1.Text, vbQuestion + vbYesNo) = vbYes Then
            iComboIndex = Combo1.ListIndex
        Else
            Combo1.ListIndex = iComboIndex
        End If
    End If
End Sub
Private Sub Form_Load()
Dim i As Integer
    For i = 1 To 10
        Combo1.AddItem "Item" & i
    Next
    Combo1.ListIndex = 0
End Sub

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Shiju SasidharanSenior IT Developer

Commented:
;-)  Little bit late

Mike TomlinsonHigh School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009

Commented:
*sigh*...

Author

Commented:
sorry - next time I'll pay more attention to who answered first
Mike TomlinsonHigh School Computer Science, Computer Applications, Digital Design, and Mathematics Teacher
CERTIFIED EXPERT
Top Expert 2009

Commented:
=)
Shiju SasidharanSenior IT Developer

Commented:
Actually Idle_Mind deserved the grade
he made the move first

Author

Commented:
I already appologized to him :(
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.