MsgBox Displays Twice

  • Thread starter Thread starter DogLover
  • Start date Start date
D

DogLover

I have 2 combo boxes for Dates; ComboBx1 = StartDAte, Combo2=EndDate. I
have in Combo box VB Code If EndDate<StartDate, then MsgBx dislays DAte Input
Error. Then, when I add a code to set the EndDateCombo.Value=Default Value,
the MsgBox Displays AGAIN. Does anyone know how to not have it reappear????
 
This is on the View Code attached to the EndDate Combo Box.

Private Sub ComboBoxEndDate_Change()
If ComboBoxEndDate.Value < ComboBoxStartDate.Value Then
MsgBox ComboBoxEndDate.Value + " MUST be GREATER than Start
Date!", vbOKOnly + vbWarning, "Date Input Error"
Worksheets("RFJ").Range("N1") = 1
ComboBoxEndDate.Text =
Format(CDate(Worksheets("Demo").Range("A22")), "mm/yyyy") ' Reset to Default
Orient End Date

Else
ComboBoxEndDate.Text = Format(CDate(ComboBoxEndDate.Value),
"mm/yyyy")
End If
End Sub
 
the change event itself chnages the value, so that raises the event
again....add the two lines indicated

Private Sub ComboBoxEndDate_Change()
If ComboBoxEndDate.Value < ComboBoxStartDate.Value Then
MsgBox ComboBoxEndDate.Value + " MUST be GREATER than Start
Date!", vbOKOnly + vbWarning, "Date Input Error"
Worksheets("RFJ").Range("N1") = 1

Application.EnableEvents = FALSE

ComboBoxEndDate.Text =
Format(CDate(Worksheets("Demo").Range("A22")), "mm/yyyy") ' Reset to Default
Orient End Date

Else
ComboBoxEndDate.Text = Format(CDate(ComboBoxEndDate.Value),
"mm/yyyy")
End If
Application.EnableEvents = TRUE
End Sub
 
Still popping up twice. I wonder if there is another event to use rather
than the change?
 
application.enableevents won't help with these controls.

But you can keep track of it yourself.

At the top of the userform (it's a userform, right?) module:

Dim BlkProc as boolean
.....

Private Sub ComboBoxEndDate_Change()

if blkproc = true then
exit sub
end if

If ComboBoxEndDate.Value < ComboBoxStartDate.Value Then
'use & to concatenate text and + to add numbers
MsgBox ComboBoxEndDate.Value & _
" MUST be GREATER than Start Date!", &
vbOKOnly + vbWarning, "Date Input Error"
Worksheets("RFJ").Range("N1") = 1
blkproc = true
ComboBoxEndDate.Text = Format(CDate(Worksheets("Demo").Range("A22")), _
"mm/yyyy") ' Reset to Default Orient End Date
blkproc = false
Else
blkproc = true
ComboBoxEndDate.Text = Format(CDate(ComboBoxEndDate.Value), "mm/yyyy")
blkproc = false
End If
End Sub

==============
An alternative that I'd use if I were you would be to add a label (make it a red
font) and instead of displaying a msgbox, you could change the .caption of the
label.

I think it's a little "cleaner" for the user, too:

Private Sub ComboBoxEndDate_Change()

if blkproc = true then
exit sub
end if

me.label1.caption = "" 'if it's ok, make it invisible

If ComboBoxEndDate.Value < ComboBoxStartDate.Value Then
'use & to concatenate text and + to add numbers
me.label1.caption = ComboBoxEndDate.Value & _
" MUST be GREATER than Start Date!"
Worksheets("RFJ").Range("N1") = 1
blkproc = true
ComboBoxEndDate.Text = Format(CDate(Worksheets("Demo").Range("A22")), _
"mm/yyyy") ' Reset to Default Orient End Date
blkproc = false
Else
blkproc = true
ComboBoxEndDate.Text = Format(CDate(ComboBoxEndDate.Value), "mm/yyyy")
blkproc = false
End If
End Sub

(You'll still want to stop the extra processing, though.)
 
Back
Top