Passing values from Userform to Routine

[waubain]

I am not a programmer, but have used enough vba in other MS applications that I was volunteered for this project. I am working in Reflection for Unix and OpenVMS which supports vba macros. A programmer in another division wrote a very lengthly macro: sub rxconverter() to automate the processing of prescriptions. We do not have a programmer at our facility, so I was volunteered to get it going and was able to accomplish that task. Now I am trying to automate values that change often so I do not have to go into the code and change each time. Also there are about 20 decision steps with a yes or no Msgbox. Since many of our steps are always a yes or no, I am trying to create a UserForm so each user can make combobox selections and check boxes to set the Msgboxes rules without changing the original code. In Word or Excel I would have all my code within the UserForm. In this case rxconverter() works by itself, but if I cut and paste it into my userform, and then call it from a cmdButton in the userform, I get an error message that the a variable is not defined. There are many variables in rxconverter() that are initialized in the code: strSomething = " " . Why it works by itself and not when called is beyond my knowledge.

Although it will work without a UserForm, I am trying to learn. What I would like to do( mostly because of ignorance) is leave rxconverter() as it's own sub and either call the UserForm from rxconverter() or open Userform and pass the variable values to rxconverter. Since rxconverter is very long I will cut pieces as needed since it basically is the same process over and over.

I read about ByRef and ByVal and other ways to possibly pass variable, but none seem to fit my situation.


Here is my pic of simplified form and the code:

Private Sub UserForm_Initialize()

'ComboBox values

With cboDivision
    .AddItem "Eastern"
    .AddItem "Central"
End With

End Sub

Private Sub cmdConvert_Click()

'*** This button runs the macro *****

End Sub

Below is one small portion of the code to show you how it was written. In my simplified version I want (about 15 lines down) strLoginDivision = cboDivision.Text or whatever would be the method for

passing the UserForm combobox

Sub rxconverter()   'very abridged version

       On Error GoTo ErrorHandler ' Error Handle
    
    'If not logged into a Division - This will log you in
    Dim strLoginDivision As String 'Declares Variable - Select Division
    Dim strLoginLBLPrinter As String 'Declares Variable - Select Default printer
    Dim strLoginMargin As String 'Declares Variable - Set Printer Margin
    Dim strLoginAlignment As String 'Declares Variable - Confirms that alignment
    Dim strLoginSummary As String 'Declares Variable - Show Summary of rxs
    Dim strLoginBingoBoard  As String 'Declares Variable - Assigns Bingo Board
    
    'Login Info
    Dim ArrayLogin(1 To 5) As String 'Array to Login to Division
    ArrayLogin(1) = "Division:" ' Searches for the phrase - Used for Division
        strLoginDivision = "CENTRAL" ' Sets variable - ENTER DIVISION HERE
    ArrayLogin(2) = "Select LABEL PRINTER:" ' Searches for the phrase - Used for Printer
        strLoginLBLPrinter = "HOME" ' Sets variable - ENTER PRINTER HERE
        strLoginMargin = "80" ' Sets variable - ENTER MARGIN HERE
    ArrayLogin(3) = "OK to assume label alignment is correct" ' Searches for the phrase - Used for Alignment check
        strLoginAlignment = "YES" ' Sets variable - Yes - assumes correct alignment
    ArrayLogin(4) = "Bingo Board Display" ' Searches for the phrase - Used for selecting Bingo Board
        strLoginBingoBoard = "CENTRAL WAITING AREA" '
    ArrayLogin(5) = "Do you want an Order Summary?" ' Searches for the phrase - Used for Displaying Order Summary
        strLoginSummary = "NO" ' Sets variable - NO - skips summary
    
    LF = Chr(10) ' set LF as LineFeed - Chr(rcLF) = Chr(10)
    CR = Chr(13)  ' Sets CR as Carriage ReturnCR - Later Chr(rcCR) = Chr(13) = Carriage return
    
    With Session ' Starts Session
    strPRESCRIPTION = UCase(InputBox("PLEASE ENTER PRESCRIPTION # HERE", "ENTER RX#"))
    
    If strPRESCRIPTION = "" Then Exit Sub
    
    .PrintToFile = Environ$("UserProfile") & "\Desktop\RX CONVERTER-" & Format(Now, " mm-dd-yy") & ".txt"  ' Creates File on Users Desktop - Name Based on First Drug, Date, Time
    .PrinterLogging = True ' Turn Logging On
    
    .Transmit "^Discontinue Prescription(s)" & CR    'Path to Rx processing
    
    Intcell = 0 ' Initiates Line counter
    'strLogin_Check = "" ' Initiates Login Check
    
    Do Until strLogin_Check Like "Discontinue*" Or strLogin_Check Like "Division:*" Or strLogin_Check Like "Do you want an Order Summary?*" ' Loop until Login Starts
    strLogin_Check = .ReadLine("00:00:01")  ' Reads each line in Vista
    Intcell = Intcell + 1 ' Counts each line
    If Intcell > 6 Then GoTo TheEnd ' If NOT ready to Login then will exit Macro
    Loop  ' Loop back up
        
    If strLogin_Check Like "Discontinue*" Then GoTo StartDCRX 'If Already Logged in will go to Delete Rx
    If strLogin_Check Like "Division:*" Then .Transmit strLoginDivision & CR: GoTo LoginRx ' If Not logged in will go to Login Info
    If strLogin_Check Like "Do you want an Order Summary?*" Then .Transmit strLoginSummary & CR: GoTo StartDCRX 'If logged in and Summary is asked will answer

LoginRx: ' Will log user into Division
    strLogin_Check = "" ' Initiates Login Check
    Do Until strLogin_Check Like "Discontinue*" Or Intcell > 10 ' Loop until RX DC'd
    strLogin_Check = .ReadLine("00:00:01")  ' Reads each line
    Intcell = Intcell + 1 ' Counts each line
    Select Case .Parent.Application.WaitForStrings(ArrayLogin, , rcAllowKeystrokes) ' Array to look for terms
    Case 1 ' looks for Division
    .Transmit strLoginDivision & CR ' Enters Division into Vista
    Case 2 ' Looks for Default Printer
    .Transmit strLoginLBLPrinter & CR ' Enters Printer into Vista
    .Transmit strLoginMargin & CR ' Enters Printer Margin into Vista
    Case 3 ' Looks at Alignment
    .Transmit strLoginAlignment & CR ' Enters Response into Vista
    Case 4 ' Looks at Bingo Board
    .Transmit strLoginBingoBoard & CR ' Enters Response into Vista
    Case 5 ' Looks at Order Summary
    .Transmit strLoginSummary & CR ' Enters Response into Vista
    GoTo StartDCRX ' When logged in will goto Start of RX DC
    End Select ' Ends Case Loop
    If Intcell > 9 Then GoTo TheEnd ' If not logged in within 9 lines will exit Macro
    Loop ' End Loop

TheEnd:

    .PrinterLogging = False ' Turns Logging Off
    .PrintToFile = "" ' Sets printing file to NULL
    MsgBox "The End", vbOKOnly

    End With ' End Session
    Exit Sub ' Exit Sub
    
ErrorHandler: ' Error Handler
    Session.MsgBox Err.description, vbExclamation + vbOKOnly ' On Error - Displays Error Description, Exclamation Mark, and OK Button
End Sub

Here is one piece of code that show a decision point with a message point. At this point I would want to call or already have stored in a variable the value of intAlready. If I call each variable should all

the rules be in one sub or should each rule have its own sub.

Case 3 ' Checks Status of OLD Rx
    strReadline = .ReadLine("00:00:01") ' Reads Status from Vista
    If strReadline Like "*Discontinue*" Then .Transmit "^" & CR & "N" & CR & CR & CR: intAlreadyDC = MsgBox("Rx already discontinued! Do you want to continue??", vbYesNo) ' Checks if OLD Rx 

already DC's if Yes will send message to user
    If intAlreadyDC = 6 Then GoTo VIEWOLDRX ' If Yes will continue to get data from OLD Rx
    If intAlreadyDC = 7 Then GoTo TheEnd ' If NO will Exit Macro
    .Transmit "R" & CR ' Used to select RX #

Somewhere the value of the checkbox on the Userform is used in this code

Sub Rule1()
    Dim intAlreadyDC As Integer
    
    If chkRule1 = True Then
        intAlreadyDC = 7
    Else: intAlreadyDC = 6
End Sub

I am fumbling around without really know where to start so any help would be appreciated.

You don't know what you don't know...

 

[waubain]

I found a few threads on passing variables back and forth with Global variable. Although they are discussed directly with Reflection, it was vba.

One thread was

http://www.tek-tips.com/viewthread.cfm?qid=1513877

Won't be able to try until tomorrow.



You don't know what you don't know...