Assistance with inserting data validation comments

[ABabeNChrist]

Thanks Hans, works perfect

[ABabeNChrist]

Since some my dropdown list became very large I started using key words before comment followed by a semicolon for faster selection. But after selection I have to backspace the description verbiage and semicolon. Is it possible to add code that would remove the semicolon and verbiage before comment.

[HansV]

Which dropdown list?

[ABabeNChrist]

The Insert comment list (Post=112598)

[HansV]

That post is about the InsertCom userform. This userform contains a list box with comments. Is that what you meant by "dropdown list"?

[ABabeNChrist]

I'm sorry, yes that is correct

[HansV]

Change the code for CommandButton2 (the "Add to text box" button) to

Code: Select all

Private Sub CommandButton2_Click()
    Dim strText
    Dim lngPos As Long
    If Me.ListBox1.ListIndex <> -1 Then
        strText = Me.ListBox1.Value
        lngPos = InStr(strText, ";")
        strText = Mid(strText, lngPos + 1)
        If Me.chkBullet Then
            strText = "• " & strText
        End If
        If Me.TextBox1.Text <> "" Then
            strText = Me.TextBox1.Text & vbLf & strText
        End If
        Me.TextBox1.Text = strText
    End If
End Sub

[ABabeNChrist]

As always “Perfecto” works great
Thanks Hans

[ABabeNChrist]

I was wondering if any verbiage before ; could be displayed in bold within the listbox for quicker selection

[HansV]

No, a list box does not support formatting part of the text differently from the rest. But if you store the "prefix" and comment in different columns, you can display them in different columns of the list box.

[ABabeNChrist]

Thank you Hans

[ABabeNChrist]

No, a list box does not support formatting part of the text differently from the rest. But if you store the "prefix" and comment in different columns, you can display them in different columns of the list box.

Hi I was wondering if you could demonstrate a sample

[HansV]

I don't think the sample workbook that you attached in August of 2012 (!) contained keywords followed by a semi-colon, so it's hard to visualize what's needed.
Please post a fresh copy of the workbook.

[ABabeNChrist]

I have attached a sample workbook.
This is how I would like for it to appear within the list box, or something similar.

Damaged Wood Fencing; Loose, damaged, and unstable fencing were present at time of the inspection, recommend repair and/or replace as necessary.

Gate Repair; Gate does not operate properly. Recommend adjustments and/or repairs as necessary to insure proper usage.

Retaining Wall; A section(s) of the retaining block wall is unstable and poses a hazard. A qualified contractor should evaluate and make repairs or replace sections as necessary.

The reason for a change is to find the comment or comments that best suits the concern quicker.

Book1.xlsm

[HansV]

See the modified version. It doesn't use color (that is not supported for a list box) but it displays the prefix in a separate column, making it easily distinguishable from the rest.

Book1.xlsm

[ABabeNChrist]

Thanks Hans, I was hoping for more of a separation (not space wise)
I’ll play around with it and see how I looks

[ABabeNChrist]

I have another thought; I use verbiage that has 3 levels of concern. Each level of concern are determined by the color of the font, red font is the highest level of concern such as a safety issue, a green font represents a maintenance issue, blue font represents additional information and of course black font is used for general information.

Here is what I was wondering, using my current approach to insert my selected comments as in the attached workbook in my Post=162375. Can the comments font colors be changed as necessary using like say a checkbox that would change font color as it is being inserted to the worksheet and could multiple comments be inserted at one time using a verity of font color comments

[HansV]

It would become very difficult to maintain.

[ABabeNChrist]

I believe I found a solution or workaround that will accomplish what I’m after.
Instead of using a single target location to insert multiple comments in multiple colors I can use multiple target locations with fonts color preset. Sample attached

Sample.xlsm

[HansV]

If that works for you, that is great!