Solved How to replace data validation input message with a textbox

February 7, 2018 at 07:44:31
Specs: Windows 10
Input message data validation is limited to 255 characters and 9 lines. How would like to replace it with a textbox. Would it be possible?
Here you go my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim arr, cellVal As Variant
    Set rng = Range("A1:A10")
    arr = rng.Value
    If Not Intersect(Target, rng) Is Nothing Then

    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            cellVal = arr(i, j)
            Select Case cellVal
              Case Is = "A"
                  rng(i, j).Validation.InputMessage = "Presentation and history:" & vbTab & vbCrLf & _
                "One eye or both eyes" & vbTab & vbCrLf & _
                "Gritty sensation/itch versus pain" & vbTab & vbCrLf & _
                "Photophobia" & vbTab & vbCrLf & _
                "Visual change" & vbTab & vbCrLf & _
                "Discharge present" & vbTab & vbCrLf & _
                "Injury" & vbTab & vbCrLf & _
                "Foreign body" & vbTab & vbCrLf & _
                "History of allergy or hay fever" & vbTab
              Case Is = "B"
                  rng(i, j).Validation.InputMessage = TextBox1.Text
              Case Is = "C"
                  rng(i, j).Validation.InputMessage = "Carrot"
              Case Else
                  rng(i, j).Validation.InputMessage = "Something   else"
            End Select
        Next j
    Next i
    End If
End Sub

Case "A" shows the limit of the data validation message. I would like to replace it with TextBox1 as shown in case "B". Please let me know if it is possible.
Regards
Tommaso


See More: How to replace data validation input message with a textbox

Reply ↓  Report •

✔ Best Answer
February 8, 2018 at 13:57:33
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyTB As Shape
    ' hide all boxes
    ActiveSheet.Shapes("TextBox 1").Visible = msoFalse
    ActiveSheet.Shapes("TextBox 2").Visible = msoFalse
    ActiveSheet.Shapes("TextBox 3").Visible = msoFalse

    ' working on B1:B10 in order not to disturb data validation in A1:A10
    If Not Intersect(Target, [B1:B10]) Is Nothing Then

        ' assign correct TextBox to MyTB
        Select Case Target.Value
            Case "A", "a"
                Set MyTB = ActiveSheet.Shapes("TextBox 1")
            Case "B", "b"
                Set MyTB = ActiveSheet.Shapes("TextBox 2")
            Case Else
                Set MyTB = ActiveSheet.Shapes("TextBox 3")
        End Select

        ' position MyTB one cell right/down from Cursor (Target) and make visible
        MyTB.Left = Target(1, 2).Left
        MyTB.Top = Target(2, 2).Top
        MyTB.Visible = msoTrue

    End If
End Sub



#1
February 7, 2018 at 18:43:30
I'm confused. Are you trying to display a MsgBox or are you trying to take the text of a Text Box and use it as the Validation input message?

Are you expecting to be able to get more characters and/or more lines in the Validation input message simply by using the text from a Text Box, which I assume is longer than 255 characters/9 lines?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
February 7, 2018 at 23:10:32
A text box has more characters than an input data validation message. So I would like to mimic the validation behavior by using maybe a text box rather than a confined or restricted input message.
Thanks for looking into this.

Reply ↓  Report •

#3
February 8, 2018 at 09:42:28
I'm still confused. Keep in mind that we can't see your workbook from where we are sitting nor do we know anything about your work process.

Here is what I think is going on based on the code that you posted. Feel free to correct me if I'm missing something:

If any cell in A1:A10 is changed, the code is going to loop through the range and attempt to set the Data Validation Input Message based on the contents of each cell (A or B or C or anything else)

That indicates to me that each cell already has Data Validation applied to it, otherwise the attempt to set the Validation.InputMessage is going to fail.

OK, if my assumption is correct, then I'm not sure why you are trying to set the InputMessage after the cell has already been changed. Is it being done so that the next time a user selects the cell, the new InputMessage will appear?

If that's the case, then I'm confused as to when you expect the TextBox1.Text to appear. In your current code, you are setting an InputMessage which won't get displayed until the cell is selected some time in the future. Your request seems to be that you are trying to cram in more text than is allowed by setting the InputMessage with the text from some other source.

rng(i, j).Validation.InputMessage = TextBox1.Text

We all know that that is not going to happen if TextBox1.Text is too long.

So, before I can offer any suggestions, I guess I need to know a little more about your process and what this InputMessage is being used for, when it is supposed to appear, etc.

I also have another, separate question:

Your range (rng) is 1 column wide, yet you are using a 2 dimension array in your loop.

      For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count

What's the reason for doing that?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
February 8, 2018 at 13:57:33
✔ Best Answer
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyTB As Shape
    ' hide all boxes
    ActiveSheet.Shapes("TextBox 1").Visible = msoFalse
    ActiveSheet.Shapes("TextBox 2").Visible = msoFalse
    ActiveSheet.Shapes("TextBox 3").Visible = msoFalse

    ' working on B1:B10 in order not to disturb data validation in A1:A10
    If Not Intersect(Target, [B1:B10]) Is Nothing Then

        ' assign correct TextBox to MyTB
        Select Case Target.Value
            Case "A", "a"
                Set MyTB = ActiveSheet.Shapes("TextBox 1")
            Case "B", "b"
                Set MyTB = ActiveSheet.Shapes("TextBox 2")
            Case Else
                Set MyTB = ActiveSheet.Shapes("TextBox 3")
        End Select

        ' position MyTB one cell right/down from Cursor (Target) and make visible
        MyTB.Left = Target(1, 2).Left
        MyTB.Top = Target(2, 2).Top
        MyTB.Visible = msoTrue

    End If
End Sub


Reply ↓  Report •

#5
February 8, 2018 at 13:59:51
Thanks for trying to help me. This code that I have posted for the benefit of other users works brilliantly and answer to my question.
Thanks again for trying to help.
Kind Regards
Tommaso

Reply ↓  Report •

#6
February 8, 2018 at 15:09:18
I was going to suggest a SelectionChange macro (even had one written) to show a text box when the appropriate cell was selected, but your requirements still weren't clear, at least not to me.

You originally said:

I would like to replace it with TextBox1 as shown in case "B".

What was shown in Case B was this:

rng(i, j).Validation.InputMessage = TextBox1.Text

In other words, you didn't really want what was shown in Case B because you were no longer trying set a Validation.InputMessage. That is what caused the confusion.

In any case, I'm glad you found a solution that works for you. I have marked the thread as solved.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question