Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Passing values from Userform to Routine

Passing values from Userform to Routine

Passing values from Userform to Routine

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
    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


    .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...

RE: Passing values from Userform to Routine

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...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close