Validation gives Run-time error='1004' Application Defined 1

[MCubitt]

I get a Run-time error='1004' Application Defined or object-defined error when using the following:

        With Worksheets("Users to add to core").Columns(placedcol).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Cells(39, mastercol)
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
        End With

Cells(39,mastercol) is _COL11 which is a valid name and I have successfully performed a cell validation using this name.

I suspect (as per previous forum requests I have logged) it is linked to teh way I am selecting the column.

Any ideas, please?





Applications Support
UK

 

[xlbo]

might want to change:
Cells(39, mastercol)
to
Worksheets("Users to add to core").Cells(39, mastercol).value

just to make it more specific

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[MCubitt]

Actually, cells(39,mastercol) is from current, active sheet which is NOT ("Users to add to core").

I explicity refered to the value, so used ="_COL11" and it still failed with the same message. So I think the "Cells(39, mastercol).Value" is a red herring.






Applications Support
UK

 

[PHV]

What about this ?
With Worksheets("Users to add to core").Columns(placedcol)
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Cells(39, mastercol)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MCubitt]

I tried this:
Worksheets("Users to add to core").Columns(placedcol).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Cells(39, mastercol)
.IgnoreBlank = True

errored on line: Worksheets("Users to add to core").Columns(placedcol).Select

Same error.

Your suggestion failed too, same error, on:
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & Cells(39, mastercol)





Applications Support
UK

 

[byundt]

1) Does the worksheet "Users to add to core" exist in the active workbook?
2) Does placedcol equal an integer between 1 and 256?
3) Could you post the portion of the sub that shows how placedcol is assigned a value and how it is declared in a Dim statement?

 

[MCubitt]

by,
1. Yes, it does.
2. Yes, it does.
3. I could, but I think the problem relates to a teh fact I am accessing an laternative worksheet as referencing the active worksheet in teh same section, in a particualr way.

I trued this:

         With Worksheets("Users to add to core").Columns(9).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=_COL11"
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
        End With

And got the error.

I tried our some variations and get the same error with this line:
Worksheets("Users to add to core").Range("I2:I200").Select

and with
Worksheets("Users to add to core").Columns(9).Select
and
Worksheets("Users to add to core").Range("I2:I2").Select


It seems the following is fine:
With Worksheets("Users to add to core").Range("I1:I20")
but when I do .validate below it, 1004 error.

So I try
With Worksheets("Users to add to core").Range("I1:I20").Select

And it error on that line.

How to I "select" in the non-active workbook without an error?
Is that the problem.




Applications Support
UK

 

[xlbo]

ACTIVATE then select

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[MCubitt]

Didnt work:
Worksheets("Users to add to core").Activate
Worksheets("Users to add to core").Columns(placedcol).Select
With Selection
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & .Worksheets("Master").Cells(39, mastercol)


Or am I getting my .'s in the wrong place?



Applications Support
UK

 

[MCubitt]

Earlier in the script, I do

' Clear all cells
  With Worksheets("Users to add to core").Range("A1:IV6000")
    .ClearContents
  End With

and it works fine.

But when I had:

  With Worksheets(desworksheet)
   .Range("A1:Z10").Select
   Selection.ClearContents
   .Range("A1").Select
  End With

It did not work.

I have been getting varying messages from folk about the use of select and of activate, some say do, some say don't!
Perhaps it depends on circumstance and objective.




Applications Support
UK

 

[MCubitt]

I took a step back to the start.
Opened a blank Excel worksheet. Pasted in MS' example:
Private Sub CommandButton1_Click()
With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

End Sub


Clicked the button.
Runtime 1004.

Is there a VBA reference I need to use Validation, perhaps?



Applications Support
UK

 

[xlbo]

There is very rarely ANY need to activate OR select

I suggested activate as it seemed you were going down the route of using selection

I think there is a lot of confusion in this thread....

Let me get this straight...

You are trying to apply validation to a column in the sheet "Users to add to core" ??
This sheet is NOT the active sheet but IS in the active workbook ??
You are trying to access a value in a cell on the Active worksheet to defined a named range to put in the validation ??
both PLACEDCOL and MASTERCOL return valid integers that complete the cell reference ??

If all the above are correct then this should work:

Dim myNamedRange As String
Const placedcol = 2
Const mastercol = 1 [COLOR=green]' in my test setup, _COLL11 is stored as text in the active sheet in cell A39[/color]
myNamedRange = ActiveSheet.Cells(39, mastercol).Text

With Worksheets("Users to add to core").Columns(placedcol).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & myNamedRange
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
End With

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[MCubitt]

xlbo,

I agree, let's clarify:

You are trying to apply validation to a column in the sheet "Users to add to core" ??

Correct

This sheet is NOT the active sheet but IS in the active workbook ??

Correct

You are trying to access a value in a cell on the Active worksheet to defined a named range to put in the validation ??

Correct

both PLACEDCOL and MASTERCOL return valid integers that complete the cell reference ??

Correct


Though even remooving them (ie using explicit values returns the error.

As I said, I tried going back to basics, using MS' VBA help example and it errored.

However, I managed to correct it:
Microsoft said:

With Range("e5").Validation
    .Add Type:=xlValidateWholeNumber, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="5", Formula2:="10"
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With

But I got 1004 on the .Add line
I tried:

Range("e5").Select
With Selection.Validation
    .Add Type:=xlValidateWholeNumber, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="5", Formula2:="10"
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With

And it works! Appauling that the MS example does not work but there you go.

I tried your example and it errored, same place. I checked mastercol and placedcol and both are correct and relating to the correct column.

I am hoping not to have to activate the other sheet because it should all be happening withotu switching sheets.

This is driving me nuts!

Taking MS' example again (which is a simplified start to things) I decided I'd apply the validation to e5 on a different worksheet.

As soon as I introduced the sheets() or worksheets() statement before the range I get the 1004 error on the select:
Private Sub CommandButton1_Click()
Sheets(1).Range("e5").Select

Help! Please!






Applications Support
UK

 

[MCubitt]

Some interesting reading at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexvba/html/odc_5709_chap05idx.asp

Key points...

To avoid errors, it is recommended that you don't use the Activate method to select a range of cells. If you get into the habit of using Activate instead of Select, you will get unexpected results when the top-left cell you activate is within the current selection.

You can use the Range property of the Application object to refer to a Range object on the active worksheet

(note "active worksheet")

If you want to work efficiently with more than one worksheet at the same time, it is important to be able to refer to ranges on worksheets without having to activate those worksheets. Switching between worksheets is slow, and code that does this is more complex than it need be. This also leads to code that is harder to read and debug.

If you want to refer to a range on a worksheet that is not active, simply use the Range property of the required Worksheet object:

Worksheets("Sheet1").Range("C10")

After further reading I then got a little confused




Applications Support
UK

 

[xlbo]

very very odd

I set up a workbook with 2 sheets
1 sheet I renamed to "Users to add to core"
On this sheet I set up dummy data validation in colB (2)

I then made the other sheet active
I entered the text _COLL11 in cell A39 on this sheet (now the active sheet). I also set up a named range of _COLL11 and entered some values in the cells it related to (on the active sheet)

I then ran the code I posted with "Users to add to core" NOT active and it worked perfectly - the validation was changed from a comma seperated list to the values held in the named range _COLL11

If your setup is the same as that then I see no reason why the code should fail....

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[MCubitt]

I created a blank worksheet, followed your steps and encountered the 1004 error on the .Add line.

This is crazy.

I am running Excel 2000.





Applications Support
UK

 

[xlbo]

running xl2002 but the VBA changes between the 2 are mimimal and I would not expect them to cause something like this. Can you send me the test spreadsheet you set up where it failed ??
Geoff dot Barraclough AT PunchTaverns DOT_com

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[MCubitt]

Thanks Geoff, files sent by e mail.



Applications Support
UK

 

[PHV]

I am running Excel 2000
All the office2k components should be patched with all the SRs and SPs.
I strongly suggest to go to the office update site....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Have replied via email - seems that th eissue was caused by the code being run from a commandbutton that had its "TakeFocusOnClick" property set to true rather than false - the issue was with the active object being the commandbutton rather than a sheet

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question