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

Hi Toby,

Perhaps you could post an example of a problematic
procedure?


---
Regards,
Norman



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

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.


---
Regards,
Norman


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

Hi Toby,

> Exit Sub
> Get_Params_err:
> MsgBox Error$
> Exit Sub

My suggested replacement should have read:


Exit Sub
Get_Params_err:
MsgBox Error$


---
Regards,
Norman

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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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

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