Too stupid to combine VBA codes

[Logan602041]

Gentlemen, I'm new to Excel, VBA codes and far too stupid to get my head around the task I have set myself and have come here to ask for your help as I have already thrown way too much time at this problem to no avail.

Problem: I am trying to make a 'risk assessment spreadsheet' and I need two different types of drop-down menu on the same sheet, I need to identify an illness from a drop down menu and as I make selections have them appear in cells to the right of my drop down menu. For example: Stupid, Lazy, VBA unteachable, Block-headed, Bumbling idiot.

I've managed this by copying the VBA code below from this link: Excel Data Validation - Select Multiple Items
This works fine with the selected "illness" spread in individual cells to the right. Great (code below)

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
Dim iCol As Integer

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
If Target.Column = 3 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Validation.Value = True Then
iCol = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
Cells(Target.Row, iCol).Value = Target.Value
Else
MsgBox "Invalid entry"
Target.Activate
End If
End If
End If

exitHandler:
Application.EnableEvents = True

End Sub


But, I want the second type of multiple selection drop-down (Illness solutions) to simply list my selected multiple "solutions" in a single cell separated by commas. For example: Underneath "VBA Unteachable" I'd like to have options for say.... "Jump off a bridge, Beat head against wall, Go to a forum for help, Give up" all displayed in that one cell.

Whilst I have found the code to do this (see below) I cannot find a way to combine them both and then limit this second type of multiple drop-down selection to a only bunch of different cells on this same sheet, I'd be most grateful if anyone has a solution, thanks!

Code:

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
If Target.Count > 1 Then GoTo exitHandler

lType = Target.Validation.Type
If lType = 3 Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
On Error Resume Next
Ar = Split(oldVal, ", ")
strVal = ""
For i = LBound(Ar) To UBound(Ar)
Debug.Print strVal
Debug.Print CStr(Ar(i))
If newVal = CStr(Ar(i)) Then
'do not include this item
strVal = strVal
lCount = 1
Else
strVal = strVal & CStr(Ar(i)) & ", "
End If
Next i
If lCount > 0 Then
Target.Value = Left(strVal, Len(strVal) - 2)
Else
Target.Value = strVal & newVal
End If
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

 

[My Aswer Is This]

Lets start from the beginning. And not concern our self with all this code.

So if I understand it you want to enter a value in Column 1
And if the value entered is:

Sports then enter Football in column B same row and enter Baseball in column C and Soccer in column D

That is a example.

Am I on the right path?


So how are we to know what values go in what column.

 

[Logan602041]

Thanks for responding, to answer your question - "So how are we to know what values go in what column?" - Because we select them from a pre-populated drop-down menu.

~Firstly I want to select more than one entry from this drop down menu and have the results display in their own columns to the right of the drop-down.

To use your example, the drop-down could contain sports titles, "Football, Rugby, Snooker". these then appear in separate columns to the right and the first code I pasted does this nicely.

Now for the cell below each of these newly selected titles I want to be able to have a single cell populated by a drop-down menu that selects (lets say ball types) so the drop-down would contain "round balls, oval balls, leather balls, small balls, shiny balls, hard balls soft balls".

For football I would want to select 3 entries "round balls, leather balls, soft balls" and have them display in a single cell separated by a comma.

Basic Excel I understand won't let me do this which is why I have sought out these codes.

 

[My Aswer Is This]

In your last post you gave me no specifics. I believe you have some code you want to use and your wanting help modifying the existing code to work for you.

I'm not real good at trying to modify existing code to do what users wants.

So I will have to say:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Akuini]

Hi, Logan602041. Welcome to the Forum.
Can you upload your sample workbook (without sensitive data) to a free site such as dropbox.com & then put the link here.
It can help us better understand what you want.
Question:
1.The first data validation, is it only in one cell?
2.The second data validation, is it in each column?

 

[Logan602041]

Hi, Logan602041. Welcome to the Forum.
Can you upload your sample workbook (without sensitive data) to a free site such as dropbox.com & then put the link here.
It can help us better understand what you want.
Question:
1.The first data validation, is it only in one cell?
2.The second data validation, is it in each column?

Hello Akuini, you are most kind thank you for your welcome and offering to help.

I have made a sample workbook as requested and the link is here: Risk Assessment sample.xlsm

I don't know how to explain it further than this, I am trying to combine two VBA codes which I have found to work independently of one another, I lack the skill though to make both work where I need them on the same sheet as the example shows.

thanks again for any help anyone can provide with this.

 

[Akuini]

Ok, try this:
Say you want to fill in cell G7:
select G7 then select B7 then pick the items from data validation > the items will be inserted to G7 separated by a comma+space.

Risk Assessment sample 1.xlsm

Shared with Dropbox

www.dropbox.com

you may want to adjust the range this part:

'range where second data validation type are located If Not Intersect(Target, Range("B7:B13")) Is Nothing Then

and this:
'range to fill in by second type data validation If Not Intersect(Target, Range("C7:J13")) Is Nothing Then xcell = Target.Address End If

 

[Akuini]

I think the code can be simplified, like this:

Risk Assessment sample 2.xlsm

Shared with Dropbox

www.dropbox.com

And you may want to adjust the range in these parts:

Private Const xcol As Long = 10 'last column of the table (col J)

'range where first data validation type is located If Target.Address(0, 0) = "B6" And Target.Value <> "" Then

'range where second data validation type are located ElseIf Not Intersect(Target, Range("B7:B13")) Is Nothing Then

'range to fill in by second type data validation If Not Intersect(Target, Range("C7:J13")) Is Nothing Then xcell = Target.Address End If

 

[Logan602041]

I think the code can be simplified, like this:

Risk Assessment sample 2.xlsm

Shared with Dropbox

www.dropbox.com

And you may want to adjust the range in these parts:

Private Const xcol As Long = 10 'last column of the table (col J)

'range where first data validation type is located If Target.Address(0, 0) = "B6" And Target.Value <> "" Then

'range where second data validation type are located ElseIf Not Intersect(Target, Range("B7:B13")) Is Nothing Then

'range to fill in by second type data validation If Not Intersect(Target, Range("C7:J13")) Is Nothing Then xcell = Target.Address End If

Thank you for your help, I'm grateful and this kind of works....... I can't spend too much time on it tonight as I have a sickly four year old who keeps waking up but....

Your solution does let me populate the cells in columns C..H below row 6 if I select from the Dropdown menu in the yellow marked area (B6..B13) and if that's the only way then I'll settle for it with gratitude.

What I was aiming for though was simply having a separate (multi-selection) drop-down menu in each of the Cells in columns C..H below row 6, just like the example in Sheet 3.

I only intended to use the B6 drop-down menu to generate the required titles for the columns to the right (C6..H6)

Is there a chance of that do you suppose or am I wishing on a star?

 

[Akuini]

Ah, sorry, I misundertood what you want.
Try this:

Risk Assessment sample 3.xlsm

Shared with Dropbox

www.dropbox.com

just make sure you already have the data validation in place