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

 

[MCubitt]

Worked a treat - Thank You!



Applications Support
UK

 

[MCubitt]

I find the reference to inactive worksheets particularly awkward and inconsistent in VBA. My latest attempt is to freeze panes at D2 of the inactive worksheet using:

  With Worksheets("Users to add to core").Range("D2")
    .FreezePanes = True
  End With

I get runtime 438!

The same happens with

 Worksheets("Users to add to core").Range("D2").FreezePanes = True

Trying

Worksheets("Users to add to core").Range("D2").Select
FreezePanes = True

gives the favourite 1004 error!

Should have stuck with Lotus 1-2-3 ;-)




Applications Support
UK

 

[xlbo]

That's because the FREEZEPANES method applies to the WINDOW object not the RANGE object or the SHEET object

You have to select a cell and then use the WINDOWS property eg:

Range("B2").Select
    ActiveWindow.FreezePanes = True

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]

Geoff,

The closest I had was
Worksheets("Users to add to core").Range("D2").Select
FreezePanes = True

The freeze is to be applied to an inactive window, which I do not want activated. Is this out of the scope of the command?




Applications Support
UK

 

[xlbo]

Ok - bit of research and it seems possible

You can only freeze panes using the windows object but each workbook has an associated windows object so:

For Each win In Application.Windows
MsgBox win.Parent.Name
Next

should give you an idea of how to determine the window index to use eg

For Each win In Application.Windows
    if win.Parent.Name = TestForWorkbookName.xls then
       myIdx = win.index
    end if
Next

windows(myIdx).freezepanes

Obviously, the cell to freeze panes on should be active in that workbook at the time...

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]

Apparently the freezepanes command is the only one which HAS to be active. Therefore it's "tough" for me! Not the end of the world but an annoyance!




Applications Support
UK