asked on

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

Saqib Husain

Try adding

application.enableevents = false

before the ...listindex=-1 and

application.enableevents = true

after the ...listindex=-1

Jan-1

ASKER

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

Saqib Husain

Is it possible to upload a sample file which demonstrates this problem?

Jan-1

ASKER

Here is a sample file which demostrate the problem.

Try combobox with red backgroun. Reshours-Test-1.xls

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jan-1

ASKER

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.