stephen antoine
Board Regular
- Joined
- Jun 4, 2008
- Messages
- 128
Hello all,
Is there a way to transfer variables between modules and between forms and modules? There's quite a bit of code here, but I figure the VBA gurus that I need help from won't be scared off by it...
I have the following code:
Everything works just fine, but I want to be able to take the variables I established when the user clicks OK and use them in a different module.
The other module has the following code. I tried to highlight where I'll be using the variables:
If someone can help on this, it would really be apprecated...
Is there a way to transfer variables between modules and between forms and modules? There's quite a bit of code here, but I figure the VBA gurus that I need help from won't be scared off by it...
I have the following code:
Rich (BB code):
Private Sub cmdOK_Click()
'This sets up the variables I want to use
CMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & CMFolder.Value & "\"
CM_Current_Payments_Export = "Export_Current_Payments_Active_" & CMFile.Value & ".XLS"
CM_PP_Rec = "Rent_Rec_" & CMFile.Value & ".XLS"
PMPath = "J:\Utilities\Building Rent\FY2008\Rent Reconciliation\" & PMFolder.Value & "\"
PM_PP_Rec = "Rent Rec - " & PMFile & ".XLS"
PPRentForm.Hide
End Sub
Private Sub UserForm_Initialize()
'This populates the list boxes with the choices I want available
With PPRentForm.CMFolder
.AddItem "01 - Jan"
.AddItem "02 - Feb"
.AddItem "03 - Mar"
.AddItem "04 - Apr"
.AddItem "05 - May"
.AddItem "06 - Jun"
.AddItem "07 - Jul"
.AddItem "08 - Aug"
.AddItem "09 - Sep"
.AddItem "10 - Oct"
.AddItem "11 - Nov"
.AddItem "12 - Dec"
End With
With PPRentForm.CMFile
.AddItem "Jan-2008"
.AddItem "Feb-2008"
.AddItem "Mar-2008"
.AddItem "Apr-2008"
.AddItem "May-2008"
.AddItem "Jun-2008"
.AddItem "Jul-2008"
.AddItem "Aug-2008"
.AddItem "Sep-2008"
.AddItem "Oct-2008"
.AddItem "Nov-2008"
.AddItem "Dec-2008"
End With
With PPRentForm.PMFolder
.AddItem "01 - Jan"
.AddItem "02 - Feb"
.AddItem "03 - Mar"
.AddItem "04 - Apr"
.AddItem "05 - May"
.AddItem "06 - Jun"
.AddItem "07 - Jul"
.AddItem "08 - Aug"
.AddItem "09 - Sep"
.AddItem "10 - Oct"
.AddItem "11 - Nov"
.AddItem "12 - Dec"
End With
With PPRentForm.PMFile
.AddItem "Jan-2008"
.AddItem "Feb-2008"
.AddItem "Mar-2008"
.AddItem "Apr-2008"
.AddItem "May-2008"
.AddItem "Jun-2008"
.AddItem "Jul-2008"
.AddItem "Aug-2008"
.AddItem "Sep-2008"
.AddItem "Oct-2008"
.AddItem "Nov-2008"
.AddItem "Dec-2008"
End With
End Sub
Everything works just fine, but I want to be able to take the variables I established when the user clicks OK and use them in a different module.
The other module has the following code. I tried to highlight where I'll be using the variables:
Rich (BB code):
Sub PP_Rent_Rec()
'
' PP_Rent_Rec Macro
' Set up PP Rent Rec (created 10/4/07)
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
PPRentForm.Show ' This code shows the form
'INITIAL SETUP
ActiveWindow.FreezePanes = False
ActiveSheet.Name = "Rent_Rec"
Sheets.Add.Name = "Current_Payments"
Sheets("Current_Payments").Move After:=Sheets(Sheets.Count)
Workbooks.Open FileName:= _
CMPath & CM_Current_Payments_Export
Cells.Copy
Windows(CM_PP_Rec).Activate
Sheets("Current_Payments").Paste
Workbooks(CM_Current_Payments_Export).Close savechanges:=False
Sheets("Rent_Rec").Select
Columns("U:U").Copy
Columns("V:Y").Select
ActiveSheet.Paste
Selection.ClearContents
Range("U10").Select
Selection.Copy
Range("U10:Y10").Select
ActiveSheet.Paste
' END INITIAL SETUP
' FORMATTING
Range("V11").Select
ActiveCell.FormulaR1C1 = "CAM, Txs, Ins"
Range("V12").Select
ActiveCell.FormulaR1C1 = "& Utilities"
Range("V11").Select
Range("W11").Select
ActiveCell.FormulaR1C1 = "Current Pymts"
Range("W12").Select
ActiveCell.FormulaR1C1 = "Export"
Range("X12").Select
ActiveCell.FormulaR1C1 = "Difference"
Range("Y12").Select
ActiveCell.FormulaR1C1 = "Explanation"
Range("R10:Y10").Select
With Selection
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
' END FORMATTING
' TEXT TO COLUMNS
Columns("B:C").Insert shift:=xlToRight
Range("A15").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(2, 2), Array(4, 9), Array(5, 2), Array(10, 9), _
Array(11, 2)), TrailingMinusNumbers:=True
Columns("B:C").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 5
Columns("D:V").Group
' END TEXT TO COLUMNS
' FORMULAS
'PP Rent SumIf'
Range("C3").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF(Current_Payments!C6,RC[-23],Current_Payments!C34)"
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
'Difference'
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-(RC[-3]+RC[-2])"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
'CAM, Txs, Ins, & Util SumIF'
Workbooks.Open FileName:= _
PMPath & PM_PP_Rec
Windows(CM_PP_Rec).Activate
Selection.End(xlDown).Select
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF('[Rent Rec - May-2008.XLS]Rent Rec'!C2,C2,'[Rent Rec - May-2008.XLS]Rent Rec'!C24)"
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Sum Totals'
Selection.End(xlDown).Select
ActiveCell.Offset(3, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-70]C:R[-1]C)"
Selection.Copy
ActiveCell.Range("A1:D1").Select
ActiveSheet.Paste
' END FORMULAS
'FREEZE PANES
Range("D13").Select
ActiveWindow.FreezePanes = True
'CLOSE SHEETS'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
If someone can help on this, it would really be apprecated...