called sub not working

[austenr]

In this 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?

[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?

[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.

[austenr]

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

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.

[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. :)

[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.

[austenr]

Ill post the WB.

[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.

[austenr]

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

[GTO]

Hi again :-)

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


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:

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

[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.

[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

[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.

[GTO]

I'm happy to be of help Austen.


...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

[Paul_Hossler]

Mark,

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



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

[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. :)