VBA split cell content then add to check/tickbox

[george hart]

Hi all

In cell"P3" I have a list of names, for example Test1,test2,test3,test4,test5 etc. What I need is a tick box option in cell "AC3" that allows one to select from a list contained in "P3" and use tick/check box.

Reason is that P3 contains a list of names of those in receipt of an email. I then need something - tick/check box that one selects that determines whether they have received a response.

Column P Column AC - check/list
<TABLE style="WIDTH: 262pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=348 x:str><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 22.5pt" height=30><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 104pt; HEIGHT: 22.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=30 width=138>Standard issued to:</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: silver; WIDTH: 158pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=210>Acknowledgement received from Standard issued to:</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>Test,test2,test3,test4,test5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23> check/list box go here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>Test,test2,test3,test4,test5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23> check/list box go here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>Test,test2,test3,test4,test5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23> check/list box go here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>Test,test2,test3,test4,test5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23> check/list box go here</TD></TR></TBODY></TABLE>

Many thanks in advance

 

[DragonWood]

Do you want a check box for each name in the list from P3?

Or do you only want one check box that counts for the entire list?

 

[george hart]

I need a check box containing each name in the list from P3 so i can select a name/s that will in turn populate cell AC3 for example.

The idea being that I have a list of names I can select to determine those who have resonded to my email, from that determine those who haven't.

It may be that a check box not be the way forward and I need a list box??

Hope this makes sense

 

[DragonWood]

How do the names end up in P3?

Are they copied there from the e-mail to box?

Will they change, or are they going to always be the same?

 

[george hart]

The names are manually entered for reference to identify all those in receipt of the email - these won't change.

I need to somehow create a list/check box that one can select in order to tick to ackowledge the response, thus identifying those who haven't.

In short, list those in cell P3 for example, tick, and identify those with no tick...

I'm starting to confuse myself now!!

Many thanks for your patience

 

[DragonWood]

Sorry for the confusion, I just want to make sure I understand what you're trying to do.

Is there a reason for only using one cell?

Can you simply make a list of the names in Column P and then a check box for each in AC?

Or I think I just got it.

You want a drop menu in P3 that you can select the name from. Each name will then have a check box in Cell AC3. If you select someone the check box will either be checked or not depending on the status for that person. Correct?

 

[MickG]

Try this:-
Using this code, any selection in column "Ac" row 3 on will result in a validation list from the same row column "P".
When you select from the List Column "AE" same row , is added to, with your selection.
If you named column "AE" :-" Repiled To". you wouild have you list'
NB:- The code uses Column "HZ" as User Column.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 2 And Target.Column = 29 Then
        Target.Offset(, 2) = Target.Offset(, 2) & Target & ","
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ray
If Target.Row > 2 And Target.Column = 29 Then
    ray = Split(Target.Offset(, -13), ",")
    Range("hz1").Resize(UBound(ray) + 1) = Application.Transpose(ray)
    
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=" & Range("hz1").Resize(UBound(ray) + 1).Address & ""
End With
End If
End Sub

Mick

 

[george hart]

Many thanks

I think I'll manage to sort something from this

Thanks again

 

[george hart]

The code below works but when I start to hightlight certain cells etc I get errors because of SelectionChange I quess . Is there a way I can put this on a macro, so I can select to run when I choose?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 2 And Target.Column = 29 Then 'Column AC drop down box
Target.Offset(, 1) = Target.Offset(, 1) & Target & "," 'Column AE is the outcome of the dropdown box
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ray
If Target.Row > 2 And Target.Column = 29 Then
ray = Split(Target.Offset(, -13), ",") 'Column P
Range("BD4").Resize(UBound(ray) + 1) = Application.Transpose(ray)

With Selection.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=" & Range("BD4").Resize(UBound(ray) + 1).Address & ""
End With
End If
End Sub