Microsoft Office Forums - View Single Post - [Solved] passing variable from one sub to another

gbrew584 · 03-16-2016, 09:09 PM

I have some code that I found on the internet that will allow the user to make a selection in a cell. If the user selects "Mileage", I would like to insert a formula in a cell, in the same row, 5 columns to the right.

The drop down box appears in cell A1, the formula (if Mileage is selected) or empty cell (anything other than Mileage) would be E1. The user would go to the next row and select from the drop down (cell A2) and depending on the selection either a formula or nothing would appear in cell E2 etc.....

I can't find out how to pass the cell address "Target.address" (saved as "Position") of the selection cell to the second sub routine so the 2nd sub routine can enter the formula or nothing into the 5th column.

Ive tried using the call command and I've added message boxes so that I could see what the code is doing. I was able to set the value of Position but now my code just passes over that line and Position ends up being blank.

If anyone can give me a pointer and let me know what line I need to change I would appreciate it.

Code:

'==========================
Private Sub Worksheet_BeforeDoubleClick _
  (ByVal target As Range, _
    Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Dim Expensetype As Range
Dim Position As Range

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If target.Validation.Type = 3 Then
    'if the cell contains
      'a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = target.Validation.Formula1
    str = Right(str, Len(str) - 1)
     
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = target.Left
      .Top = target.Top
      .Width = target.Width + 5
      .Height = target.Height + 5
      .ListFillRange = str
      .LinkedCell = target.Address
     End With
    
    cboTemp.Activate
    'open the drop down list automatically
    Me.Tempcombo.DropDown
  Position = target.Address
      Call TempCombo_LostFocus(Position)
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub
'=========================================
Private Sub TempCombo_LostFocus(Position)
MsgBox (Tempcombo)
MsgBox (Position)

   If Tempcombo.Value = "Mileage" Then Range(Position).Offset(0, 5).FormulaArray = "=IF(C4=""Mileage"",INDEX(Mileage_Table,MAX((Mileage_Eff_Date<=Mileage!$F4)*(Company_Match=Mileage!$A4)*(ROW(Mileage_Eff_Date)-7)),3),0)"
  
   Range("M3") = ""
        

  With Me.Tempcombo
    .Top = 10
    .Left = 10
    .Width = 0
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End Sub
 
'====================================
'Optional code to move to next cell
'if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems,
'change to KeyUp
'Table with numbers for other keys
'such as Right Arrow (39)
'https://msdn.microsoft.com/en-us/
 ' library/aa243025%28v=vs.60%29.aspx