Blank Message Box after procedure execution

[sybot_uk]

Aplogies if this is a bit basic but does anyone now how to get rid of
the blank message box with just and OK button which appears after each
sub procedure I run? I'm using excel 2003 vba, the message box appears
after each procedure whether I call the macro from the macro menu or
using a call statment from within another procedure.

I have tried searching for the answer but to no avail. Hope somone can
help

Toby

 

[Norman Jones]

Hi Toby,

Perhaps you could post an example of a problematic
procedure?

 

[sybot_uk]

Here is an example:


Public mth As Integer, yr As Integer, strDisplayMonth As String,
strDisplayFiscYrStart As String, strDisplayYrStart As String

Sub Get_Params()

On Error GoTo Get_Params_err

'If there is no value in the combo box raise a message otherwise set
the month variable to the value
If frmParameters.Controls("cboMth").Value = "" Or
frmParameters.Controls("cboMth").Value < 1 Or
frmParameters.Controls("cboMth").Value > 12 Then
MsgBox ("Please enter a valid month")
End
Else: mth = frmParameters.Controls("cboMth").Value
End If


'If there is no value in the combo box raise a message otherwise set
the year variable to the value
If frmParameters.Controls("cboYr").Value = "" Or
frmParameters.Controls("cboYr").Value < 2000 Or
frmParameters.Controls("cboYr").Value > 2020 Then
MsgBox ("Please enter a valid year")
End
Else: yr = frmParameters.Controls("cboYr").Value
End If

''''''''''''''Get the values for display''''''''''''''''''''''''
'get month name and year for display
strDisplayMonth = MonthName(mth)
strDisplayMonth = strDisplayMonth & " " & yr
Worksheets("Display").Range("B1").Value = strDisplayMonth
'get year start for display
strDisplayFiscYrStart = "April"
If mth > 3 Then
strDisplayFiscYrStart = strDisplayFiscYrStart & " " & yr
ElseIf mth < 4 Then
strDisplayFiscYrStart = strDisplayFiscYrStart & " " & (yr - 1)
End If
Worksheets("Display").Range("B2").Value = strDisplayFiscYrStart
'Get year start for display
If mth < 12 Then
strDisplayYrStart = MonthName(mth + 1) & " " & yr - 1
ElseIf mth = 12 Then
strDisplayYrStart = MonthName(1) & " " & yr
End If
Worksheets("Display").Range("B3").Value = strDisplayYrStart
'get year for display
Worksheets("Display").Range("B4").Value = yr


Get_Params_err:
MsgBox Error$
Exit Sub

End Sub

The workbook pulls data down from SQL Server by executing stored
proecedures. This sub sorts out the parameters being passed to the
procedure and saves them in public variables for use by the other
procedures.

Thanks in advance,

Toby

 

[Norman Jones]

Hi Toby

Try replacing:

Get_Params_err:
MsgBox Error$
Exit Sub

End Sub

with

Exit Sub
Get_Params_err:
MsgBox Error$
Exit Sub

End Sub

In your code the MsgBox gets called even if there is
no error.

 

[Norman Jones]

Hi Toby,

Exit Sub
Get_Params_err:
MsgBox Error$
Exit Sub

My suggested replacement should have read:


Exit Sub
Get_Params_err:
MsgBox Error$

 

[NickHK]

look like you need to read up on Error Handling. here's the MS take:
http://support.microsoft.com/kb/146864

Hint: Exit before your error handler label.

Also, there is very seldom any reason to use "End" in your code. Read the
Help for its effect.
It would seem you need "Exit Sub" here.

Also, from a design point of view, why do you need this code ?

If frmParameters.Controls("cboMth").Value = "" Or
frmParameters.Controls("cboMth").Value < 1 Or
frmParameters.Controls("cboMth").Value > 12 Then
MsgBox ("Please enter a valid month")

Assuming this is you code, fill cboMth with only 1-12. Possibly default to a
select value. may this month.

NickHK

 

[sybot_uk]

Thankyou both very much for your help.
Norman, I will try what you have suggested.
Nick, I have used end because this sub is called from another
procedure (in fact by several procedures, I seperated it out so I
could re-use it), if the values are incorrect I don't want the calling
procedure to carry on running. I thought this would be OK, perhaps I'm
missing something fundamental, I'll definately read the link you
posted.

Thanks again,

Toby

 

[sybot_uk]

It's now working, I have changed my error handling. I also set the
MatchRequired property of the combo boxes to true so i could remove
the code I was using to check the values were valid.

Many Thanks,

Toby