Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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. -- JNW |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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 "JNW" wrote: 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. -- JNW |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
I just saw the last part of your post. What does the following do/where does
it go? CStr(ReportName) Thanks -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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. -- JNW "JNW" wrote: 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. -- JNW |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
Converts to string... Google is a fine thing.
-- JNW "JNW" wrote: I just saw the last part of your post. What does the following do/where does it go? CStr(ReportName) Thanks -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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 "JNW" wrote: I just saw the last part of your post. What does the following do/where does it go? CStr(ReportName) Thanks -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: I just saw the last part of your post. What does the following do/where does it go? CStr(ReportName) Thanks -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting form caption based on global variable
Great!
Thanks for marking that the question was answered. So few people remember to do that. Mike "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: I just saw the last part of your post. What does the following do/where does it go? CStr(ReportName) Thanks -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW "crazybass2" wrote: 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 "JNW" wrote: 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. -- JNW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting a Custom Class as a Global variable | Excel Programming | |||
Call Event and Load Form based on Variable | Excel Programming | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Form Controls; setting properties based on cell values | Excel Programming | |||
Excel Global Variable Setting | Excel Programming |