PDA

View Full Version : Solved: trigger msgbox once



av8tordude
08-21-2010, 12:17 PM
Is it possible for a message to display once and not trigger again if Need4 label or Need5 label is visible? currently the msgbox triggers each time an event is changed


Public Sub Enable_Enter_Button()
If Len(Trim(frmPDCalc.cboCntry.Value)) = 0 Then
frmPDCalc.Ent1.Enabled = True
Else
If UCase(Trim(frmPDCalc.cboCntry.Value)) = "UNITED STATES" Then
If CDate(frmPDCalc.Date1.Value) > Sheets("Calculator").Range("T8") Then
frmPDCalc.Need4.Visible = True
frmPDCalc.Need5.Visible = False
frmPDCalc.Rate1 = "$0.00"
frmPDCalc.Ent1.Enabled = False
If CDate(frmPDCalc.Date1.Value) < Format(CDate("30-Sep-" & Year(frmPDCalc.Date1)), "mm/dd/yyyy") Then
MsgBox "The Domestic Per Diem Rates Database Has Expired. You Must Download The Per Diem Rates for: " & vbLf & _
"" & vbLf & _
" Fiscal Year: " & Format(frmPDCalc.Date1, "yyyy") & "", vbExclamation, "FlightLog - Professional Edition"
Else
If CDate(frmPDCalc.Date1.Value) > Format(CDate("30-Sep-" & Year(frmPDCalc.Date1)), "mm/dd/yyyy") Then
MsgBox "The Domestic Per Diem Rates Database Has Expired. You Must Download The Per Diem Rates for: " & vbLf & _
"" & vbLf & _
" Fiscal Year: " & Format(frmPDCalc.Date1, "yyyy") + 1 & "", vbExclamation, "FlightLog - Professional Edition"
End If
End If
Else
frmPDCalc.Ent1.Enabled = True
frmPDCalc.Need4.Visible = False
frmPDCalc.Need5.Visible = False
End If
Else
If CDate(frmPDCalc.Date1.Value) > Sheets("Calculator").Range("Z8") Then
frmPDCalc.Need4.Visible = False
frmPDCalc.Need5.Visible = True
frmPDCalc.Rate1 = "$0.00"
frmPDCalc.Ent1.Enabled = False
MsgBox "The International Per Diem Rates Database Has Expired. You Must Download The Per Diem Rates for: " & vbLf & _
"" & vbLf & _
" " & UCase(Format(DateSerial(Year(CDate(frmPDCalc.Date1.Value)), Month(CDate(frmPDCalc.Date1.Value)), 1), "mmmm yyyy")) & "", vbExclamation, "FlightLog - Professional Edition"
Else
frmPDCalc.Ent1.Enabled = True
frmPDCalc.Need4.Visible = False
frmPDCalc.Need5.Visible = False
End If
End If
End If
End Sub

Artik
08-21-2010, 03:56 PM
I will not answer you that question, because I know very little English. But I have some comments to your code.

1. Maybe in US version of Excel the following code snippet works, but in Polish version :nya: certainly did not.Format(CDate("30-Sep-" & Year(frmPDCalc.Date1)), "mm/dd/yyyy")It is safer to use DateSerial.

2. In this line you compare the date with the text!CDate(frmPDCalc.Date1.Value) < Format(CDate("30-Sep-" & Year(frmPDCalc.Date1)), "mm/dd/yyyy")Although VB can cope with it, this is not sure good method. Always compare the same data type. Take a look below.

3. Unnecessarily many times you reference the same object (frmPDCalc) or the object properties (frmPDCalc.Date1.Value). Just once, using With...End With (in the first) or variable (in second). Take a look below.

4. Probably, instead of:If Len(Trim(frmPDCalc.cboCntry.Value)) = 0 Thenyou can use:If frmPDCalc.cboCntry.ListIndex <> -1 Thenif the cboCntry it is ComboBox control.

5. You use: " "(38 spaces!), and just use Space(38) If you want to write eg. 20 "=" characters, it then use String(20,"=")

Look at the changed macro. I expect, that in Date1 control and in cells T8, Z8 are correct date.Sub Enable_Enter_Button()
Dim dDate1 As Date
Const sTitle As String = "FlightLog - Professional Edition"

On Error GoTo Enable_Enter_Button_Error

With frmPDCalc

If .cboCntry.ListIndex <> -1 Then
.Ent1.Enabled = True

Else
If UCase(Trim(.cboCntry.Value)) = "UNITED STATES" Then
dDate1 = CDate(.dDate1.Value)

If dDate1 > Sheets("Calculator").Range("T8").Value Then
.Need4.Visible = True
.Need5.Visible = False
.Rate1 = "$0.00"
.Ent1.Enabled = False

If dDate1 < DateSerial(Year(dDate1), 9, 30) Then

MsgBox "The Domestic Per Diem Rates Database Has Expired. " & _
"You Must Download The Per Diem Rates for: " & _
vbCr & vbCr & _
Space(36) & "Fiscal Year: " & Year(dDate1), _
vbExclamation, _
sTitle
Else
If dDate1 > DateSerial(Year(dDate1), 9, 30) Then

MsgBox "The Domestic Per Diem Rates Database Has Expired. " & _
"You Must Download The Per Diem Rates for: " & _
vbCr & vbCr & _
Space(36) & "Fiscal Year: " & Year(dDate1) + 1, _
vbExclamation, _
sTitle
End If
End If

Else
.Ent1.Enabled = True
.Need4.Visible = False
.Need5.Visible = False
End If

Else
If dDate1 > Sheets("Calculator").Range("Z8") Then
.Need4.Visible = False
.Need5.Visible = True
.Rate1 = "$0.00"
.Ent1.Enabled = False

MsgBox "The International Per Diem Rates Database Has Expired. " & _
"You Must Download The Per Diem Rates for: " & _
vbCr & vbCr & _
Space(38) & UCase(Format(DateSerial(Year(dDate1), Month(dDate1), 1), "mmmm yyyy")), _
vbExclamation, _
sTitle
Else
.Ent1.Enabled = True
.Need4.Visible = False
.Need5.Visible = False
End If

End If
End If
End With

Enable_Enter_Button_Exit:
On Error GoTo 0
Exit Sub

Enable_Enter_Button_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Enable_Enter_Button"
Resume Enable_Enter_Button_Exit
End Sub It is true that a better "Professional Edition"? ;)

Good luck.

Artik

Bob Phillips
08-21-2010, 03:57 PM
Create a static Boolean, that outputs the MsgBox if false, then set it to True. Next time through, the MsgBox will not fire as the Boolean has a true value.