View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
RyanH RyanH is offline
external usenet poster
 
Posts: 586
Default Pass a Function Value thru several Subs

I currently have a toolbar that has 4 Controls:

Control1 - Sorts Data
Control2 - Prints Data
Control3 - Copies Data to Other Worksheets
Control4 - Prints those Worksheets.

If Control1 is fired it will sort the data and use a MsgBox to ask the user
if they want to print. If Control2 is fired it will sort the data and bypass
the MsgBox, because the user has already demanded to print.

Sub Control1Macro(Optional ByVal StopCode As Boolean)

' sort data on data worksheet

' if StopCode = False ask to print, if print automatically
If StopCode = False Then
' ask if user wants to print
strPrompt = "The Ready Schedule has been produced, would you like to
print?"
intButtons = vbYesNo + vbQuestion
strTitle = "Ready Schedule"
If MsgBox(strPrompt, intButtons, strTitle) = vbYes Then
Application.Dialogs(xlDialogPrint).Show
End If
Else
Application.Dialogs(xlDialogPrint).Show
End If
End Sub

Public Sub PrintReadySchedule()
Call ReadySchedule(True)
End Sub

Control3 and Control4 relate a little differently. Here is my question.
How do I pass the StopCode Value from Control3 to Control4? This is the
procedure setup:

Sub Control3Macro(Optional ByVal StopCode As Boolean)
' Activate UserForm1 (Userform1 is a make shift progress bar)
End Sub

Sub Activate_Userform()
Call CompileSchedules
End Sub

Sub CompileSchedules()
' Copies Data to Other Worksheets
' makes updates to userform while compiling

If StopCode = False then
' Ask User to Print
Else
' print those worksheets
End If

End Sub

Sub Control4Macro()
Call Control3Macro(True)
End Sub


Thanks in Advance!!
--
Cheers,
Ryan