Passing values from Userform to Routine
Passing values from Userform to Routine
(OP)
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:
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.
Somewhere the value of the checkbox on the Userform is used in this code
I am fumbling around without really know where to start so any help would be appreciated.
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:
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
'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
CODE
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.
CODE
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 #
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
CODE
Sub Rule1()
Dim intAlreadyDC As Integer
If chkRule1 = True Then
intAlreadyDC = 7
Else: intAlreadyDC = 6
End Sub
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...
Talk To Other Members
RE: Passing values from Userform to Routine
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...