?
Solved

MsgBox open Twice

Posted on 2011-10-23
7
Medium Priority
?
290 Views
Last Modified: 2012-05-12
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


0
Comment
Question by:Jan-1
  • 3
  • 3
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37014169
Try adding

application.enableevents = false

before the ...listindex=-1 and

application.enableevents = true

after the ...listindex=-1
0
 

Author Comment

by:Jan-1
ID: 37015002
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37015016
Is it possible to upload a sample file which demonstrates this problem?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Jan-1
ID: 37015177
Here is a sample file which demostrate the problem.

Try combobox with red backgroun. Reshours-Test-1.xls
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 37015983
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37017265
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
 

Author Closing Comment

by:Jan-1
ID: 37021018
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question