Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    9

    Thumbs down Range Validation

    Help! I'm under a deadline and am running out of time!

    I'm trying to validate date entry into some cells. I'm building worksheets from Access using Excel. I have a hidden range in my template named XlaList that has my list of values.

    I have the follwoing VBA code in a MS Access module:

    'Enter Validation of the list type
    With gobjExcel.Range(oWs.Cells(BegRow, 8), oWs.Cells(BegRow, 9)).Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=XlaList" 'Named range on hidden sheet
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With

    Except for the (ows.Cells(BegRow,8), ows.cells(BegRow,9) section, this was copied from a Microsoft example. Their example actually has .Range("e5"). I need to be able to use BegRow to set validation since it is going to cary from sheet to sheet.

    When I run the code, I receive the following error message:
    Runtime error 1004 - Application defined or object defined error.

    I can't figure out what is going on here!

    Please help!

    Thanks - Terrie

  2. #2
    Join Date
    Aug 2002
    Posts
    9

    Re: Range Validation

    By the way, the error is highlighted on the .Add Type section

    Terrie

    Originally posted by cpetdw
    Help! I'm under a deadline and am running out of time!

    I'm trying to validate date entry into some cells. I'm building worksheets from Access using Excel. I have a hidden range in my template named XlaList that has my list of values.

    I have the follwoing VBA code in a MS Access module:

    'Enter Validation of the list type
    With gobjExcel.Range(oWs.Cells(BegRow, 8), oWs.Cells(BegRow, 9)).Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=XlaList" 'Named range on hidden sheet
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With

    Except for the (ows.Cells(BegRow,8), ows.cells(BegRow,9) section, this was copied from a Microsoft example. Their example actually has .Range("e5"). I need to be able to use BegRow to set validation since it is going to cary from sheet to sheet.

    When I run the code, I receive the following error message:
    Runtime error 1004 - Application defined or object defined error.

    I can't figure out what is going on here!

    Please help!

    Thanks - Terrie

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    Well, I guess your deadline is passed for a long time now... but I found the solution. Here it is for those how may have the same problem in the future :

    Don't ask me why but this error append only when it is executed directly to the cell. Also it works in debug mode... (!!??)

    eg. : range("G44").validation ...won't work.

    To correct this, select the cell then apply the validation method on it

    eg.: range("S21").select
    selection.validation ... and so on.


    That's it !

  4. #4
    Join Date
    Jul 2003
    Posts
    2
    Ho, another thing :

    You can't give a formula like you would have in the validation menu. I didn't find a solution other than to transfert the excel "formula" in vba code (wich is longer).

    I was doing that to have different selection depending on the value of another cell.
    Last edited by TerroirMan; 07-24-03 at 11:00.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •