Setting form caption based on global variable

[Guest]

I have created a file menu that acts as a way for users to easily create
standard reports. All of the reports need the same initial information to
begin (i.e. which periods to compare).

What I want to happen:

1) When a user chooses the report from the file menu I want to create a
global variable based on the selection. I thought I could capture this with
the following.
.OnAction = "'Create_Reports_Start""" & .Caption & """'"
This refers to the following sub:
Sub Create_Reports_Start(ReportName)
Application.StatusBar = "Creating report: " & ReportName
Form_Reports.Show
End Sub

2) I have declared ReportName globally with the following:
Global ReportName As Long

3) When Form_Reports has been filled out then I would like the following to
run upon pressing the "Create" button, using the 'Select Case' to call the
desired report.
Private Sub Button_Create_Click()
Dim Year1, Year2 As Long
Year1 = Box_Year1.Value
Year2 = Box_Year2.Value
Application.ScreenUpdating = False
Select Case ReportName
Case "Events by Level" '<< code stops here with a mismatch error
Call Create_Report_EventByLevel(Year1, Year2)
'Continue with the rest of the reports...
End Select
Unload Form_Reports
End Sub


The global ReportName variable seems to work some of the time and not other
times. It places the text in the status bar, but when form_Reports loads it
does not place ReportName in the caption of the form (this is in the
User_Form_Initialize code).

I would greatly appreciate any ideas either to fix what I've got or better
suggestions.

 

[Guest]

J,

You've defined ReportName as Long. The Long data type only accepts numeric
data. If your report names are numeric that will work fine, but then you use
the ReportName as your select case value which is compared to a text string
"Events by Level." Try defining ReportName as String and see if your results
change.

Mike

 

[Guest]

Thank you for the reply. That didn't seem to do it. I even tried declaring
it as variant.

It is strange to me that I can insert a msgbox command and the message box
will open and give me ReportName (even if it is classified as long), but
that's about it.

 

[Guest]

Have you tried running the case statement with a variable set locally? For
instance,

Private Sub Button_Create_Click()
Dim Year1, Year2 As Long
Dim testword As String
testword = "Test"
Year1 = Box_Year1.Value
Year2 = Box_Year2.Value
Application.ScreenUpdating = False
Select Case testword
Case "Test"
Call Create_Report_EventByLevel(Year1, Year2)
'Continue with the rest of the reports...
End Select
Unload Form_Reports
End Sub


Try that and see if it works. If it does then ReportName is probably not a
string, or it is not an exact match with the case. Try using
CStr(ReportName).

Mike

 

[Guest]

That doesn't work either. It doesn't seem to trap the variable when the
report is selected from the file menu. My next resort is to control report
selection from a user form but I'd like to avoid that if I can.

 

[Guest]

I just saw the last part of your post. What does the following do/where does
it go?

CStr(ReportName)

Thanks

 

[Guest]

I figured it out. My ReportName variable was not unique. This is why it
wouldn't work. I renamed the public to PReportName and now everthing works.
Thanks for the help.

 

[Guest]

Converts to string... Google is a fine thing.

 

[Guest]

CStr() will convert the contents in the () to a string. Since the error you
are getting is stating that there is mismatch error I thought perhaps your
ReportName was somehow not a string when it got to the Select Case. I would
use the CStr() call right in the Select Case

Select Case CStr(ReportName)
Case "Events by Level"
....etc.

As another check, place the following line of code just before the Select
Case ReportName. It should yield a message box that states "Events by Level
/ String"

msgbox(ReportName & ReportName.Type)

Let me know how this works out.

Mike

 

[Guest]

Hey mike. thank you for the help. I had a non-unique variable that was
causing the problem. I changed the public variable ReportName to PReportName
and everything works fine.

Thanks again.

 

[Guest]

Great!

Thanks for marking that the question was answered. So few people remember
to do that.

Mike