MsgBox open Twice

Hi!
Have a combobox in spreasheet with dropdown list from dates.
When a user select from a dropdown combobox without first entering dates, then msgbox inform user to insert date. And then macro clear out combobox
.
When I Use ComboBox1.ListIndex = -1 in macro to clear out combobox, then msgbox open twice.

Here is a part of the macro:

Private Sub ComboBox1_Change()
 
  Dim Ans As Integer
     Ans = MsgBox(" Insert Date", vbYesNo)
       Select Case Ans
          Case vbYes
            frmDateAndResthours.Show 0
            ComboBox1.ListIndex = -1
            Range("C11").Select
          Case vbNo
            ComboBox1.ListIndex = -1
            Range("C11").Select
            Exit Sub
       End Select
   
  If ComboBox1.ListIndex = 0 Then
    Call DayWork1
    Else
      If ComboBox1.ListIndex = 1 Then
        Call DayWork2
        Else
          If ComboBox1.ListIndex = 2 Then
            Call DayWork3


Jan-1Asked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
I would alter your change event code to:
Private bSkipEvents As Boolean
Private Sub ComboBox1_Change()
   Dim Ans               As Integer
   If bSkipEvents Then Exit Sub

   If Range("C13") = "" Then
      bSkipEvents = True
      Ans = MsgBox(" Insert Date", vbYesNo)
      Select Case Ans
         Case vbYes
            frmDateAndResthours.Show 0
            ComboBox1.ListIndex = -1
            Range("C11").Select
         Case vbNo
            ComboBox1.ListIndex = -1
            Range("C11").Select
            Exit Sub
      End Select
      bSkipEvents = False
   Else
      If ComboBox1.ListIndex > -1 Then
         Run "DayWork" & ComboBox1.ListIndex + 1
      End If
   End If
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Try adding

application.enableevents = false

before the ...listindex=-1 and

application.enableevents = true

after the ...listindex=-1
0
 
Jan-1Author Commented:
I tried this solution. Still same problem with msgbox open twice.

Also tried in IF-Block, same problem

If Range("C13") = "" Then
      MsgBox "You have to Insert Date"
      Application.EnableEvents = False
      ComboBox1.ListIndex = -1
      Application.EnableEvents = True
      Exit Sub
   Else
 
  If ComboBox1.ListIndex = 0 Then
    Call DayWork1
    Else
      If ComboBox1.ListIndex = 1 Then
        Call DayWork2
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Saqib Husain, SyedEngineerCommented:
Is it possible to upload a sample file which demonstrates this problem?
0
 
Jan-1Author Commented:
Here is a sample file which demostrate the problem.

Try combobox with red backgroun. Reshours-Test-1.xls
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Ok, I have just learnt that comboboxes do not obey enableevents=false so delete the above enableevent lines.

Here is a workaround found from the web


Insert these lines immediately after combobox1.change

If Application.EnableEvents Then
Application.EnableEvents = False


and these lines just before the end sub

Application.EnableEvents = True
End If
0
 
Jan-1Author Commented:
Tanks, both solutions works fine. I had to delete End Sub from Case vbNo to make macro work properly.

Rorya you gave me a good hint to simplify the macro.

Tanks to both again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.