Assistance with inserting data validation comments

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Assistance with inserting data validation comments

Post by ABabeNChrist »

I’m trying to improve on a current feature I use, thanks to the great assistance from Eileen’s Lounge.

I use data validation for my drop down selections. I use different VBA that allows me to Delete a comment from list, add a new comment to list and to insert multiple comments into select cell.

Something I have observed and would like to improve on if possible is to be able select multiple comments in different orders if needed and to be able to modify/adjust comment before inserting in cell location. I’m trying to be more flexible when inserting my comments. The comments used are more generic without locations or additional descriptions.

I have attached a sample workbook.
Book1.xlsm
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

You could place a text box on the userform, and an "Add" command button.
Clicking the Add button will add the currently selected list box item to the text box. The user can then edit the value of the text box, if desired.
Another command button will set the value of the selected cell to the text of the text box.

Keep in mind that you're going to make it extremely easy for the user to create an absolute mess! :grin:
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

HansV wrote:Keep in mind that you're going to make it extremely easy for the user to create an absolute mess! :grin:
Sometimes sacrifices are made in order for greatness. :grin:


I’ll play around and see what I can or cannot do, I’m sure I’ll be back.
Thank you Hans

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

See the attached version.
Book1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

That works really well. I was trying but wasn’t even getting that close

Thank again Hans as always :thankyou:

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

As I was populating my data validation list with large comments it was hard to identify different comments because they may start off similar and since it only shows a small portions it made it made it difficult to identify some.

I was wonder if the listbox can be populated similar to the way it appears in the textbox. Starting off with a billet and wrap text. This way I can see the complete comment and know where it starts and finishes.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

No, a list box doesn't work that way.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

What about a checkbox that will give you an option to add the billet or no billet

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

I've lost track. Where would you like this check box, and what exactly should it do?
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

As it is currently when a selection is made from the listbox over to the textbox a billet is added. I was hoping for an option that would allow user to choose to add a billet or not in front of each selection. So I thought that by adding a checkbox, that when checked (or True) would add a billet in front of each selection.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

Let's say the user has ticked the check box and added some comments to the check box.
Now the user clears the check box. Should the existing bullets be removed, or should it only affect comments that are added to the text box from that moment on?
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

I would say “affect comments that are added to the text box from that
moment on”

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

For what it's worth, here is a modified version of the workbook...
Book1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

Thank you Hans
It works pretty well. :cheers:

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

I have been using this feature for some time now and would like to improve on it if at all possible. Would it be possible to add a check box or similar, that when checked/selected it would add the additional selected comments to cell. The reason why I’m asking is there are times when I’ll bounce around to enter data into different cells and I come across another issue that I want to add. Currently it will remove the old and add the new.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

I'd add a command button "Append to cell" - see the attached version.
Book1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

That works fantastic, thank you Hans and Happy New Year :cheers:

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

Happy New Year to you too!
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Assistance with inserting data validation comments

Post by ABabeNChrist »

Everything has been working great, but is it possible to delete multiple selections.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Assistance with inserting data validation comments

Post by HansV »

In the Visual Basic Editor, open frmDelete.
Select the list box lboComments.
Change the MultiSelect property from 0 - fmMultiSelectSingle to 1 - fmMultiSelectMulti.
This makes it possible to select multiple comments in the list box. Click a comment to select it, click it again to deselect it.

Activate the code module of frmDelete.
Change the cmdDelete_Click event procedure to

Code: Select all

Private Sub cmdDelete_Click()
    Dim n As Long
    Dim i As Long
    Dim r As Long
    Dim c As Long
    For i = Me.lboComments.ListCount - 1 To 0 Step -1
        If Me.lboComments.Selected(i) Then
            n = n + 1
        End If
    Next i
    If n = 0 Then
        MsgBox "Please select one or more comments", vbInformation
        Exit Sub
    End If
    If MsgBox("You are about to delete one or more comments." & vbCrLf & vbCrLf & _
              "Are you sure?", vbYesNo + vbQuestion) = vbYes Then
        For i = Me.lboComments.ListCount - 1 To 0 Step -1
            If Me.lboComments.Selected(i) Then
                c = Me.cboCategories.ListIndex + 1
                r = i + 2
                Worksheets("Comments").Cells(r, c).Delete Shift:=xlShiftUp
            End If
        Next i
    End If
    cboCategories_Click
End Sub
Best wishes,
Hans