Solved Validation problem

[Guus2005]

Windows 8, Office 2013, 32 bits

My code gives an error on the ".Add" line. Can't figure out why.
The error is 1004. Which doesn't give me any information. Unfortunately.
Anyone?

Public Sub GegevensValidatieAantalUrenMaand(ByVal rngTarget As Range)
'
' GegevensValidatieAantalUrenMaand Macro
'
    With rngTarget.Validation
        .Delete
        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="99999999,99"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Aantal onjuist"
        .InputMessage = ""
        .ErrorMessage = "Geef een correcte waarde voor het aantal uren in met 2 decimalen achter de komma (bijvoorbeeld: 48,50)"
        .ShowInput = True
        .ShowError = True
    End With
   
End Sub

 

[Isaac]

What's your Selection at the time of running this? Can't be multiple.

 

[The_Doc_Man]

I hate Excel error 1004 because it means that the error was signaled from Excel but whatever trapped that error wasn't the routine that triggered it. Error 1004 is merely "Unspecified application error." I've gotten it many times in so many different contexts that I can't begin to count them.

It might be a pain in the toches but try adding an On Error trap local to that routine that will allow you to LOCALLY trap the error and see its number in the context of the call. On the other hand, are you attempting to update something in the range? Because you defined that routine with a BYVAL argument and that means your changes shouldn't be preserved anyway. If something you are doing needs to look up the context of the argument, you are working on an isolated copy and therefore I don't know what the context would look like during the call.

 

[isladogs]

And add the err.Description to the error handling routine

 

[Isaac]

Doing either of those things is not going to give you more information about the error. The error is application- or object-defined error. It will occur when doing a multiple selection on that action. It is local.

 

[isladogs]

I agree that err 1004 is one of the many generic application- or object-defined errors.
However, in the context of the code line where it happens, a more specific description is often provided

 

[Isaac]

I agree that err 1004 is one of the many generic application- or object-defined errors.
However, in the context of the code line where it happens, a more specific description is often provided

Interesting. In all of my error handlers with a msgbox on err.description, I've never seen anything other than application- or object-defined error given, when that arises.

 

[The_Doc_Man]

@Isaac, I have found that 1004 pops up in a thousand different places and I have SOMETIMES gotten more detailed error data by putting in something to trap the error code closer to the point of the error. You have to understand error handling flow, which I'm going to assume is very similar to Access error handling. Yes it is possible that even with local error handling there are some things you will never see better than error 1004. Which is, among other things, why I decided to abandon Excel's "Smart Art" feature because in VBA, it is mostly DUMB art. To say that it is difficult to use with VBA somehow doesn't cover it accurately. I pride myself in my percussive adjustment abilities ("bang on it to make it fit") but the combination of VBA and Smart Art in an Excel Application Object controlled from MS Access was like trying to demolish a granite cliff face with a rubber mallet. I finally used the mallet to knock some sense into my head and used a radically different approach that avoided the abomination that is automated Smart Art. Probably accounts for the lumps that show up in more recent pictures. (Sorry, getting chatty....)

First and foremost, the error that Guus reported WAS NOT HANDLED by the routine where it occurred. That routine has no error trapping set up so can't handle doodlum-squat of an error. To get the display HE got that reported the error on a specific line, he had to have gone through formal error handling, with stack unwinding, in order to run into the Office "Last Chance Handler" that is in every Office program that supports VBA usage. From his description, it was Excel's Last Chance handler that caught the error. By that time, there WAS no better context for the error so he got the catch-all error message of 1004.

 

[Isaac]

Well, I was able to reproduce his error exactly - and on the same line - by selecting multiple cells prior to running it.

Guess we'll have to wait until he weighs in here..

 

[isladogs]

So did you try adding error handling as suggested and, if so, was a more specific error description given in your test?

 

[Isaac]

I did, it makes no difference

 

[Guus2005]

I have a single cell selected and in the Worksheet_SelectionChange event i have a call to this function.
Now with Error handling in the function (i had error trapping higher in code but tracking back i got me to this point.)

Public Sub GegevensValidatieAantalUrenMaand(ByVal rngTarget As Range)
'
' GegevensValidatieAantalUrenMaand Macro
' Uitvoeren op ieder maand tabblad
'
    On Error GoTo Err_GegevensValidatieAantalUrenMaand

    With rngTarget.Validation
        .Delete
        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="99999999,99"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Aantal onjuist"
        .InputMessage = ""
        .ErrorMessage = "Geef een correcte waarde voor het aantal uren in met 2 decimalen achter de komma (bijvoorbeeld: 48,50)"
        .ShowInput = True
        .ShowError = True
    End With

Exit_GegevensValidatieAantalUrenMaand:
    On Error GoTo 0
    Exit Sub

Err_GegevensValidatieAantalUrenMaand:
    MsgBox "Error:" & Err & " :" & Err.Description & vbCrLf & "in GegevensValidatieAantalUrenMaand, modAuto", vbCritical

    Resume Exit_GegevensValidatieAantalUrenMaand
Resume 'For debugging purposes
'Todo:[+] Errorhandler GegevensValidatieAantalUrenMaand in modAuto Controleren op fouten
End Sub

It still gives me a 1004 error code with no additional information.
Even when i select more than one cell this code should work fine.

It delete any validation currently in the cell and adds a new one.

It is a protected sheet in a protected workbook., but before this code was reached all protection was removed from both the sheet and the workbook.

 

[Guus2005]

This code looks similar but this one does work without fail:

Public Sub AutoPullDown_SoortOpvang(ByVal rngTarget As Range)
    '
    ' AutoPullDownSoortOpvang Macro
    ' Deze macro voorziet een bepaalde cel automatisch van een pulldown menu
    '
    With rngTarget.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=SoortOpvang"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Selecteer Opvang soort"
        .ErrorTitle = MsgboxCaption & " verkeerde keuze."
        .InputMessage = "Klik aan en maak een keuze"
        .ErrorMessage = "Dit is geen geldige opvang soort."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Validation with a pulldown list. The named range is "SoortOpvang". Obviously.

 

[isladogs]

As I haven't tried the code, I can't offer any other suggestions but the first post in the error 1004 thread listed below included this comment:

An Excel is created and sent for each teacher. The number of columns in the Excel is variable. This works for the first record, but for the second record I get the error message 1004: Method Range of object _Global failed.

In that case, a specific error message was supplied for err 1004. Whether that is any help to you is, of course, another matter

 

[Darrell]

I'm wondering if this comma is your problem.

"...Formula2:="99999999,99"

I ran this code but replaced the comma with a decimal point and it ran fine.
Then I changed my regional settings so that the decimal symbol was a comma and the code would only work if I had a decimal point in this Formula2 setting. even though it would display as a comma in the Validation settings.

 

[Isaac]

And for my part, I have to admit I've now been able to reproduce the OP's error seemingly arbitrarily, even sometimes when only one cell is selected; other times it works fine. The macro recorder produces relatively identical code, too ... Stumped

Oh ... Of course I also replaced the comma with a decimal point, but assumed the comma was appropriate for OP region, but who knows