I have one code that uses the change event to add validation boxes to any row
that doesn't have them.

I have another code to insert a special row, that has special validation
boxes(apart from the change event). My change event has a variable that stops
the change event from running while it's in process.

The problem is that when I activate the cahnge event, the validation runs
smoothly and is inserted into the sheet, using named ranges from another
sheet as the values for the drop down box. When I try to insert the special
row, and I use the exact same code to add the validation, it errors on the
add line:

With ActiveSheet.Range(Cells(iRow, iColumn).Address).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sRange

As you can see, I delete the current validation so that I can always use an
add routine. When I set a watch VBA sees the validation, but won't run the
line. I have tried removing all variables and tired not referring to a anmed
range and still get the error. I am using Excel 2000. Any ideas on why the
code will work in one procedure but not in another?

*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Are you using Protection on your sheets? sometimes, I have found it is a
simple as having not unlocked the sheet - or having locked it from a
different section of code - particularly when it works sometimes and not
others.

another possibility could be using ActiveSheet within the code, maybe the
activesheet isn't the one you think it is, are other procedures activating
other sheets? - can you refer to the specific sheet name?

"J Streger" wrote:

I have one code that uses the change event to add validation boxes to any row
that doesn't have them.

I have another code to insert a special row, that has special validation
boxes(apart from the change event). My change event has a variable that stops
the change event from running while it's in process.

The problem is that when I activate the cahnge event, the validation runs
smoothly and is inserted into the sheet, using named ranges from another
sheet as the values for the drop down box. When I try to insert the special
row, and I use the exact same code to add the validation, it errors on the
add line:

With ActiveSheet.Range(Cells(iRow, iColumn).Address).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sRange

As you can see, I delete the current validation so that I can always use an
add routine. When I set a watch VBA sees the validation, but won't run the
line. I have tried removing all variables and tired not referring to a anmed
range and still get the error. I am using Excel 2000. Any ideas on why the
code will work in one procedure but not in another?

*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

I was using active sheet, but after removing the instances of that, the error
still occurs. Here is the subroutine I'm using to add validation. It works in
one line of code and not the other. The break occurs at the .add line:

Private Sub Add_Validation(ByVal iRow As Integer, ByVal iColumn As Integer, _
ByVal sRange As String, ByVal oSheet As
Worksheet, Optional sInputTitle As String, _
Optional sInputMessage As String, Optional
sErrorTitle As String, _
Optional sErrorMessage As String)

Dim bShowInput As Boolean
Dim bShowError As Boolean

If IsMissing(sInputTitle) And IsMissing(sInputMessage) Then
bShowInput = False
Else
bShowInput = True
End If
If IsMissing(sErrorTitle) And IsMissing(sErrorMessage) Then
bShowError = False
Else
bShowError = True
End If
If IsMissing(sInputTitle) Then sInputTitle = ""
If IsMissing(sInputMessage) Then sInputMessage = ""
If IsMissing(sErrorTitle) Then sErrorTitle = ""
If IsMissing(sErrorMessage) Then sErrorMessage = ""

sRange = "=" & sRange

With oSheet.Range(Cells(iRow, iColumn).Address).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sRange
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = sInputTitle
.ErrorTitle = sErrorTitle
.InputMessage = sInputMessage
.ErrorMessage = sErrorMessage
.ShowInput = bShowInput
.ShowError = bShowError
End With



End Sub

"Simon Shaw" wrote:

Are you using Protection on your sheets? sometimes, I have found it is a
simple as having not unlocked the sheet - or having locked it from a
different section of code - particularly when it works sometimes and not
others.

another possibility could be using ActiveSheet within the code, maybe the
activesheet isn't the one you think it is, are other procedures activating
other sheets? - can you refer to the specific sheet name?

"J Streger" wrote:

I have one code that uses the change event to add validation boxes to any row
that doesn't have them.

I have another code to insert a special row, that has special validation
boxes(apart from the change event). My change event has a variable that stops
the change event from running while it's in process.

The problem is that when I activate the cahnge event, the validation runs
smoothly and is inserted into the sheet, using named ranges from another
sheet as the values for the drop down box. When I try to insert the special
row, and I use the exact same code to add the validation, it errors on the
add line:

With ActiveSheet.Range(Cells(iRow, iColumn).Address).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sRange

As you can see, I delete the current validation so that I can always use an
add routine. When I set a watch VBA sees the validation, but won't run the
line. I have tried removing all variables and tired not referring to a anmed
range and still get the error. I am using Excel 2000. Any ideas on why the
code will work in one procedure but not in another?

*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

After lots of trial and error I found that the validation error was fixed
merely by activating the cell that I wanted to add validating to before
adding it. I even activated a cell in the same row and it worked. Thanks for
your help.

"Simon Shaw" wrote:

Are you using Protection on your sheets? sometimes, I have found it is a
simple as having not unlocked the sheet - or having locked it from a
different section of code - particularly when it works sometimes and not
others.

another possibility could be using ActiveSheet within the code, maybe the
activesheet isn't the one you think it is, are other procedures activating
other sheets? - can you refer to the specific sheet name?

"J Streger" wrote:

I have one code that uses the change event to add validation boxes to any row
that doesn't have them.

I have another code to insert a special row, that has special validation
boxes(apart from the change event). My change event has a variable that stops
the change event from running while it's in process.

The problem is that when I activate the cahnge event, the validation runs
smoothly and is inserted into the sheet, using named ranges from another
sheet as the values for the drop down box. When I try to insert the special
row, and I use the exact same code to add the validation, it errors on the
add line:

With ActiveSheet.Range(Cells(iRow, iColumn).Address).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=sRange

As you can see, I delete the current validation so that I can always use an
add routine. When I set a watch VBA sees the validation, but won't run the
line. I have tried removing all variables and tired not referring to a anmed
range and still get the error. I am using Excel 2000. Any ideas on why the
code will work in one procedure but not in another?

*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003