Set up input message for validation

[Bubo]

Hi,

I have a module wich sets the validation for a range. The input message for the validation is stored in a cell. The message is more than 256 characters containing linefeed characters (ALT+ENTER), too.

The issue is that seting up the validation in such a way results in an error. Basically, it seems, that Excel cannot cope with such input messages.

Had anyone the same experience, is it a workaround?

Bubo

 

[tusharm]

What's the error?

 

[Bubo]

This is the way is set up the validation:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Operator:=xlBetween, Formula1:="=$HA$1:$HM$1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Select from list!"
.ErrorTitle = "Invalid entry type"
.InputMessage = Range("HM2").Text
.ErrorMessage = "Select from list!"
.ShowInput = True
.ShowError = True
End With



The error message is: "Application-defined or Object-defined error"

 

[Bubo]

Could anyone help me on that?

 

[Beate Schmitz]

Hello,

replace your code by this:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >With</span> Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Operator:=xlBetween, Formula1:=<span style="color:#800000"; >"=$HA$1:$HM$1"</span>
    .IgnoreBlank = <span style="color:#000080"; >True</span>
    .InCellDropdown = <span style="color:#000080"; >True</span>
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = <span style="color:#000080"; >False</span>
    .ShowError = <span style="color:#000080"; >False</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >With</span>

</span></nobr></div>

Because of the length of your string in the warning cell (HM2), I would recomment you to use a msg-box instead of the validation input-msg.

Your cell with the dropdown would be A1.

Then copy following code into the codewindow of the sheet in question:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Private</span> <span style="color:#000080"; >Sub</span> Worksheet_Change(<span style="color:#000080"; >ByVal</span> Target <span style="color:#000080"; >As</span> Range)
<span style="color:#000080"; >If</span> Target.Address = <span style="color:#800000"; >"$A$1"</span> <span style="color:#000080"; >Then</span>
    <span style="color:#000080"; >If</span> [COUNTIF(HA1:HM1,A1)] = 0 <span style="color:#000080"; >Then</span>
        Application.EnableEvents = <span style="color:#000080"; >False</span>
        MsgBox [HM2]
        Range(<span style="color:#800000"; >"$A$1"</span>).ClearContents
    <span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
Application.EnableEvents = <span style="color:#000080"; >True</span>
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Sub</span>
</span></nobr></div>

 

[Bubo]

Thank you for your reply, you answer is highly appreciated.

Your solution works, but do not solve my problem.

I set up the validation for cells dynamically changing. It means that depeing how the user uses the excel worksheet the validation will appear in different cells. There is not one but more cells having set up in that way.

I already use "Worksheet_Change" function for other purposes. Of course it is possibe to implement your proposal to the code, but the module more complex. Moreover, I assume that the users must close the msgbox before selecting from the list. This is not very customer friendly.

What I am looking for is to set up the validation with the long message from the procedure. I tried it from the menu and it is possible to type in the whole message there. I know there must be a solution to do the same within the vba module.