Called sub not working

Printable View

09-10-2010, 11:00 AM
austenr

Called sub not working

In this code:

Code:

If Sheets("Input + Wksheet").Range("B10").Value = 1 And Sheets("Input + Wksheet").Range("G6").Value > 65 Then DirectBillOnlyIndemnity2Off With Sheets("Direct Bill ONLY") .Range("D11").Value = "X" .Range("E24").Formula = "=if(MEDCVG=1,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12" .Range("F24").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E24" 'DirectBillOnlyIndemnity3On 'DirectBillOnlyIndemnity4On End With With Sheets("Direct Bill with RIA") .Range("D11").Value = "X" .Range("E20").Formula = "=if(MEDCVG=1,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12" .Range("F20").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E20" End With End If

The line in red (the sub being called) does not execute. However if you go to the sub and either press F8 and step through it or execute it with F5 it works fine. Ideas?
09-10-2010, 11:21 AM
Zack Barresse

Is it in an outside module? Does it error? When running the housing code, and stepping through with F8, what happens? Does it go into the code? Does it go into the code and then error? What exactly happens? Is the routine in another module but set to Private?
09-10-2010, 11:25 AM
austenr

Howdy Zack, long time no see.

Its not in an outside module. It does not error. It steps through it just fine using F8 but when you look at the sheet it is suposed to do the proc on it does absolutly nothing. It is not set to private.
09-10-2010, 11:29 AM
austenr

me thinks it is because the proc that is being called:

Code:

Sub DirectBillOnlyIndemnity2Off() Range("J22").ClearContents Range("J23").ClearContents Range("K23").ClearContents Range("H22:K25").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("I24").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone End Sub

was created in the sheet. So when its called its like what range? However, I did switch it to the module and added a qualifier for the sheet for each range with no luck.
09-10-2010, 11:29 AM
Zack Barresse

Can you set a breakpoint in your procedure, inside DirectBillOnlyIndemnity2Off
, then run your code normally. It should break into your routine. I have no idea what your routine does, so it's hard to say if it's working right or not.

Yeah, long time no see! It's been nice being able to get back into the forums again, albeit slowly. :)
09-10-2010, 11:31 AM
Zack Barresse

The procedure has no specific sheet set to it, so it will work on the active sheet. Do you want to pass a worksheet to that routine, to specify it? Or is it the same worksheet every time? You should qualify the worksheet either way.
09-10-2010, 11:34 AM
austenr

Ill post the WB.
09-10-2010, 11:54 AM
austenr

I think i got it working. Was not in the part of the code where it selects the sheet to perform the sub on. Ill post back if i have any more issues.
09-10-2010, 02:32 PM
austenr

1 Attachment(s)

Well I dont have it working. Im posting the workbook. Heres how I tested it.

When you open the WB copy the three highlighted cells to the right of the Retirement Date and paste them in the yellow boxes that correspond to them to the left.

Make sure the medical coverage box is set to 1.

Now, press "Calculate Retiree Rates"

Next, check the 'Direct Bill ONLY" sheet. The boxes and text should be gone on the indemnity row next to Retiree Only but they are not, or if they are not all of it has vanished.

Next, if you open the VBE editor and F8 throught it it works as it should except for the part where it wont remove the Retiree Spouse/DP text. It says its a merged cell but I dont see how seeing as how i un merged it..

Why would it work if you F8 through it and not if you just press the button? I even put break points as it went through the code on every line and it worked with the exception of the merged cell thing.

:dunno
09-10-2010, 04:06 PM
GTO

Hi again :-)

Not well tested... In a junk copy of your wb, try:

Code:

Sub DirectBillOnlyIndemnity2Off() '// Use With to easily qualify each range... // With ThisWorkbook.Worksheets("Direct Bill ONLY") '<---OR: With Sheet4 '// BEFORE testing, J22 still needed un-merged // .Range("J22").ClearContents .Range("J23").ClearContents .Range("K23").ClearContents '// ...and you can nest the with for properties of the Range, such as .Borders// With .Range("H22:K25") .Borders.LineStyle = xlNone .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlInsideVertical).LineStyle = xlNone .Borders(xlInsideHorizontal).LineStyle = xlNone End With With .Range("I24") .Borders.LineStyle = xlNone .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone End With End With End Sub

Your ranges were not being qualified, so success would be dependant upon the sheet being active.

I did not read through all the code, but I do not believe you are deleting any of the sheets. If this is the case, might I make a suggestion? See in the very first With, where I noted that you could use the sheet's CodeName, like:

Code:

With Sheet4

As long as you are not copying sheets or deleting them, you might consider using their CodeNames, as two things seem benefitted to me leastwise. (1) If the user renames a sheet, the codename still works, and (2) I find it much easier to write code with intellisense helping me out.

Hope that helps,

Mark
09-10-2010, 04:09 PM
GTO

ACK...

I forgot to mention, if you do try using the codenames, they can be changeed during design time, so you could name them like 'shtDirect_Bill_ONLY' or 'shtDirectBillOnly', so that it would still be intuitive as you are typing along.
09-10-2010, 06:47 PM
Paul_Hossler

Question: why do you have 2 subs named DirectBillOnlyIndemnity1On

One is in Module1 and one is on the code for Sheet4 (DirectBill Only)

(You have other subs duplicated also)

If you REALLY wanted to call a specific one, I think you need to use something like

Call Module1.DirectBillOnlyIndemnity1On

Personnally (and it's only my style) I'd put subs like DirectBillOnlyIndemnity1 in a module and only WS control code and event handlers in the WS code.

You might not be executing the same module with F5 that your WS controls are

Paul
09-10-2010, 06:57 PM
austenr

Thanks to you both. Marks suggestion was spot on and exactly what i needed. Looks like I have a lot of modifying and cleaning up to do. Thanks again Mark.
09-11-2010, 04:42 AM
GTO

I'm happy to be of help Austen.

Quote:

Originally Posted by Paul_Hossler

...If you REALLY wanted to call a specific one, I think you need to use something like

Call Module1.DirectBillOnlyIndemnity1On

...You might not be executing the same module with F5 that your WS controls are

Hi Paul,

I certainly agree with most everything you pointed out, such as the duplication of subs and keeping general code in standard modules. Personally, in cases where it seems advantageous to create a function for something that only needs done as part of an event or two in the object module (let's say Workbook_Open in ThisWorkbook), I find it easier to keep the function in the same object/class module. Usually I'll mark it Private just so its plain to me later. Certainly just a preference as you noted.

As to qualifying the procedure called w/the module name, in this case Austen is using commandbuttons from the Forms toolbar. So when he chose the macro to run from the Assign Macro dialog box, it is already being qualified as necessary. For instance, in Austen's workbook, if you bring up the Assign Macro dialog for one of the buttons, and scroll down, you'll see the duplicate subs prefaced with 'Sheet4.'.

Well... probably already too 'wordy', so I'll stop "flappin' my yap".

A great day to both ya'll,

Mark
09-11-2010, 11:09 AM
Paul_Hossler

Mark,

"Flap away" -- I always learn something, like ...

Quote:

As long as you are not copying sheets or deleting them, you might consider using their CodeNames, as two things seem benefitted to me leastwise. (1) If the user renames a sheet, the codename still works, and (2) I find it much easier to write code with intellisense helping me out.

That was a good catch. I developed the habit of never assuming an Activesheet and always qualifying my .Ranges and .Cells so I never thought about that and read right though it :doh:

Paul
09-11-2010, 12:56 PM
Zack Barresse

I find myself in that habit more and more. I just carried my variable naming convention for worksheets right over to their code names. Simple, and no more variable declarations and Set statements, just go straight to coding. :)