#1
|
|||
|
|||
passing variable from one sub to another
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 |
#2
|
|||
|
|||
Hi
Try changing the code to the below & see if it works for you. I've extracted bits of your code & changed it Cheers Code:
'========================== Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, _ Cancel As Boolean) Dim Position As String Position = Target.Address TempCombo_LostFocus Position errHandler: Application.EnableEvents = True Exit Sub End Sub '========================================= Private Sub TempCombo_LostFocus(ByVal Position As String) MsgBox (Position) End Sub |
#3
|
|||
|
|||
Thanks for the help!! and the quick reply. I tried the changes you suggested but on the Lostfocus sub routine I get the error message: "procedure declaration does not match description of event or procedure." That was the same message I got every time I tried changing and passing the variable. Do you have any other suggestions I can try?
|
#4
|
|||
|
|||
Deleted as I posted in the wrong thread
Last edited by Philb1; 03-17-2016 at 03:39 PM. Reason: Wrong post |
#5
|
|||
|
|||
Hi
Can you post a example workbook? It's probably something to do with the formula, maybe referring to things that don't exist? |
#6
|
|||
|
|||
i will be happy to post a copy of the workbook, as soon as i figure out how to do it
|
#7
|
|||
|
|||
Here is a copy of my file. When I tried to save it I keep getting a message that asked if I wanted to recalculate before saving. each time I pressed yes, the message came back. I don't know why it acting up. Thanks again for trying to help. I'm going to go back over my file and see if I can find out why the calculation is acting up.
|
#8
|
|||
|
|||
Philb1 - I have some code on the top of my code that seems to be messing things up. I blocked it out by putting a ' in front of the code. Still not working but I'm getting closer, thanks to your help.
|
#9
|
|||
|
|||
Hi Greg
You were getting errors in the userform initialise module because you were trying to sum the whole of columns L and N. L1 and N1 contain text which caused it to throw errors. I've changed the ranges to go from row 3 to the last used row of those columns. The userform shows when the button is pressed, but doesn't do anything. With the worksheet change code it appears you want to check the dropdowns in column C for the word Mileage. I changed the code to do that & for demo purposes, put the formula in column O. It's probably not the final product but should hopefully set you in the right direction. I didn't know what all the original code was for, but it did look a bit over complicated for such a simple task. Or have I got it wrong lol Cheers Phil |
#10
|
|||
|
|||
Thanks for taking a look at my project, sorry I didn't get back to you sooner but I had to go out of town. Yes I did have a lot of extra code, I was experimenting with some routines and forgot to delete them. Thanks for the tip about the text in the sum column, I forgot about that too.
Yes the worksheet is a work in progress. I don't know how to thank you for your help. You were able to get me going in the right direction. Thanks, I'm trying to learn as I go and you have helped with that too. Thanks again!! |
#11
|
|||
|
|||
No problem
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
userform help (having trouble passing info from form to macro) | cyraxote | Word VBA | 16 | 09-08-2015 04:16 AM |
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge | Berryblue | Mail Merge | 1 | 11-13-2014 05:36 PM |
Run-time error 91 object variable or with block variable not set | JUST ME | Word VBA | 4 | 03-25-2014 06:56 AM |
object variable or with block variable not set | MJP143 | Excel | 1 | 02-11-2013 05:07 AM |
Run-time error '91': Object variable or With block variable not set | tinfanide | Excel Programming | 2 | 06-10-2012 10:17 AM |