Sub runs, but gives error when called.

[salgud]

The following sub runs fine when I run it directly:

Public Sub DataValidationClientID()
'Validate that Client ID field has a correct entry before allowing entry of
Client Last Name
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow & "))>64,CODE(UPPER(B"
& lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B" &
lCurRow & ",6))))" <---ERROR
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Incorrect ClientID"
.InputMessage = ""
.ErrorMessage = "There is no Client ID or an incorrect Client ID. " _
& "Please enter a correct Client ID in Column B before entering a Client
Name"
.ShowInput = False
.ShowError = True
End With
End Sub

But when I run it from the routine below, I get an "application or object
defined error on the .Add Type:=... line

Public Sub EnterFacilData()
'Enters Facility names and formulas for Columns I,M,N,O,P
'and formats spreadsheet
lFinalRow = lCurRow + lFacilRowsUI - 1
lPrevSumRow = lCurRow - 1
lNextSumRow = lFinalRow + 1
'Application.ScreenUpdating = False

With ws

'Enter Facil Name in Col B
.Cells(lCurRow, 3) = sFacilNameUI

'Insert formula =IF(H7<>"",DATEDIF(H2,I2,"D")+1,"") with lCurRow as the
row
.Cells(lCurRow, "J").Formula = _
"=IF(ISERROR(DATEDIF(H" & lCurRow & ",I" & lCurRow & ",""d"")),""DATE
ERROR"",DATEDIF(H" & lCurRow & ",I" & lCurRow & ",""d""))"

'Insert formula =$J3*K3 with lCurRow as the row
.Cells(lCurRow, "N").Formula = _
"=if(iserror($J" & lCurRow & "*K" & lCurRow & "),"""",$J" & _
lCurRow & "*K" & lCurRow & ")"

'Autofill from Col N lCurRow to Col P lCurRow
.Range("N" & lCurRow).Select
Selection.AutoFill Destination:=Range("N" & lCurRow & _
"" & lCurRow), Type:=xlFillDefault

'Enter formula to sum Monthly costs for client's monthly total in Col Q
.Cells(lCurRow, "Q").Formula = "=sum(N" & lCurRow & _
"" & lCurRow & ")"

'Autofill Facil name in Col C and formulas in cols I thru P from
' lCurRow down to lFinalRow if lFacilRowsUI > 1
If lFacilRowsUI > 1 Then
'Fill down Facil name
.Cells(lCurRow, "C").Select
Selection.AutoFill Destination:=.Range(.Cells(lCurRow, "C"), _
.Cells(lFinalRow, "C"))

'Fill down formulas
.Range("J" & lCurRow & ":Q" & lCurRow).Select
Selection.AutoFill Destination:=Range("J" & lCurRow _
& ":Q" & lFinalRow), Type:=xlFillDefault
End If

'Conditionally format row if it is to be paid in blue
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A" &
lCurRow & "=""pay"""
Selection.FormatConditions(1).Font.ColorIndex = 5
Selection.Copy
Range("B" & lCurRow & ":Q" & lFinalRow).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'************************

..Range("D" & lCurRow, "D" & lFinalRow).Select
Call DataValidationClientID

..Range("H" & lCurRow, "H" & lFinalRow).Select
Call DataValidationDOB

..Range("I" & lCurRow, "I" & lFinalRow).Select
Call DataValidationEDOCgtBDOC

BTW, this sub is called from another, not sure if it matters here. If
someone needs to see that one, let me know.

Does anyone see what the problem is?

 

[Jim Cone]

Are you using publicly declared variables you didn't mention?
Or did you Not use "Option Explicit" at the top of your module?
--
Jim Cone
Portland, Oregon USA



"salgud" <[email protected]>
wrote in message
The following sub runs fine when I run it directly:
-snip-
But when I run it from the routine below, I get an "application or object
defined error on the .Add Type:=... line
-snip-
BTW, this sub is called from another, not sure if it matters here. If
someone needs to see that one, let me know.
Does anyone see what the problem is?

 

[salgud]

Are you using publicly declared variables you didn't mention?
Or did you Not use "Option Explicit" at the top of your module?

Thanks for your reply.
My mistake, should have mentioned - all variables publicly declared and
Option Explicit is always on.

 

[Jim Cone]

My first reaction is that the inconsistency may lie with different selections
being used when the sub is run directly vs. when the sub is called by another sub.
--
Jim Cone
Portland, Oregon USA



"salgud" <[email protected]>
wrote in message

Are you using publicly declared variables you didn't mention?
Or did you Not use "Option Explicit" at the top of your module?

Thanks for your reply.
My mistake, should have mentioned - all variables publicly declared and
Option Explicit is always on.

 

[salgud]

My first reaction is that the inconsistency may lie with different selections
being used when the sub is run directly vs. when the sub is called by another sub.

I've rechecked this morning. I don't know what I did yesterday, but the
validation program alone is giving me the same error message I get when I
run it from the other sub. So the error is in the program itself. Any
ideas?

 

[Jim Cone]

You need something in Cells(lCurRow, 2) before you can validate so...
'--
Cells(lCurRow, 2).Value = 0
'your code
Cells(lCurRow, 2).ClearContents
'--
....will work. However, there may be some better way to handle this?
I'll leave that up to you or others.

In addition, I would want to verify that the selection is correct before running the code.
(maybe you do that in other code)
--
Jim Cone
Portland, Oregon USA




"salgud" <[email protected]>
wrote in message

My first reaction is that the inconsistency may lie with different selections
being used when the sub is run directly vs. when the sub is called by another sub.

I've rechecked this morning. I don't know what I did yesterday, but the
validation program alone is giving me the same error message I get when I
run it from the other sub. So the error is in the program itself. Any
ideas?

 

[salgud]

You need something in Cells(lCurRow, 2) before you can validate so...
'--
Cells(lCurRow, 2).Value = 0
'your code
Cells(lCurRow, 2).ClearContents
'--
...will work. However, there may be some better way to handle this?
I'll leave that up to you or others.

In addition, I would want to verify that the selection is correct before running the code.
(maybe you do that in other code)

Not following you here, Jim. Why do I need something in the cell to put in
the validation criteria? The others all work fine.

 

[Jim Cone]

Validation won't accept a formula that is returning an error.
Adding a value to the cell allows the formula to return True or False..
--
Jim Cone
Portland, Oregon USA



"salgud" <[email protected]>
wrote in message On Tue, 21 Jul 2009 10:10:09 -0700,

Not following you here, Jim. Why do I need something in the cell to put in
the validation criteria? The others all work fine.