Assistance with inserting data validation comments
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Assistance with inserting data validation comments
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
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!
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!
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
Sometimes sacrifices are made in order for greatness.HansV wrote:Keep in mind that you're going to make it extremely easy for the user to create an absolute mess!
I’ll play around and see what I can or cannot do, I’m sure I’ll be back.
Thank you Hans
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
That works really well. I was trying but wasn’t even getting that close
Thank again Hans as always
Thank again Hans as always
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
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.
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.
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
No, a list box doesn't work that way.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
What about a checkbox that will give you an option to add the billet or no billet
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
I've lost track. Where would you like this check box, and what exactly should it do?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
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.
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
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?
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
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
I would say “affect comments that are added to the text box from that
moment on”
moment on”
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
For what it's worth, here is a modified version of the workbook...
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
Thank you Hans
It works pretty well.
It works pretty well.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
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.
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
I'd add a command button "Append to cell" - see the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
That works fantastic, thank you Hans and Happy New Year
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Assistance with inserting data validation comments
Everything has been working great, but is it possible to delete multiple selections.
-
- Administrator
- Posts: 78231
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Assistance with inserting data validation comments
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
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
Hans