Auto filling a cell in a table which has a list using data validation

[rjn008]

Good Afternoon,

Is it possible to auto populate a cell in a table where by the cell adjacent left has a format on it. The only issue I think I have is I use a list on the cell under data validation, but the list does contain the value I wish to have auto populated?

This is the mini sheet code and image of the style of sheet I am using;

The New LMS ^N4.xlsm
	I	J	K	L	M	N	O
5	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7
6	Arsenal	Man City	Chelsea	x	x	x	x
7	Man Utd	Newcastle Utd	x	x	x	x	x
8	Liverpool	Fulham	Arsenal	x	x	x	x
9	Arsenal	Man City	Chelsea	x	x	x	x
10	Arsenal	Man City	Chelsea	x	x	x	x
11	Arsenal	Man City	Brentford	Aston Villa	Liverpool	AFC Bournemouth	West Ham Utd
12	Arsenal	Man City	Chelsea	x	x	x	x
13	Newcastle Utd	Liverpool	Chelsea	x	x	x	x
14	Man Utd	Man City	Brentford	Brighton & HA	x	x	x
15	Newcastle Utd	Tottenham	Chelsea	x	x	x	x
16	Arsenal	Chelsea	Fulham	x	x	x	x
17	Arsenal	Man City	Chelsea	x	x	x	x
18	Newcastle Utd	Tottenham	Brentford	Brighton & HA	x	x	x
19	Newcastle Utd	Liverpool	Arsenal	x	x	x	x
20	Arsenal	Tottenham	Brentford	AFC Bournemouth	x	x	x
21	Liverpool	Man City	Arsenal	x	x	x	x
22	Arsenal	Man City	Chelsea	x	x	x	x
23	Liverpool	Tottenham	Chelsea	x	x	x	x
24	Liverpool	Man City	Chelsea	x	x	x	x
25	Liverpool	Man City	Arsenal	x	x	x	x
26	Newcastle Utd	Liverpool	Aston Villa	Brighton & HA	x	x	x
27	Arsenal	Tottenham	Chelsea	x	x	x	x
28	Man Utd	Liverpool	Chelsea	x	x	x	x
29	Arsenal	Tottenham	Brentford	Brighton & HA	x	x	x
30	Newcastle Utd	Tottenham	Aston Villa	Man Utd	Liverpool	Arsenal	West Ham Utd
31	Man Utd	Fulham	Notts Forest	x	x	x	x
32	Newcastle Utd	Tottenham	Man City	Aston Villa	Man Utd	x	x
33	Liverpool	Man City	Aston Villa	Brighton & HA	x	x	x
34	Arsenal	Tottenham	Aston Villa	Man City	Newcastle Utd	x	x
35	Newcastle Utd	Liverpool	Arsenal	x	x	x	x
36	Newcastle Utd	Man City	Chelsea	x	x	x	x
37	Arsenal	Man City	Brentford	Brighton & HA	x	x	x
38	Newcastle Utd	Man City	Aston Villa	Man Utd	Tottenham	Arsenal	Chelsea
39	Newcastle Utd	Liverpool	Aston Villa	Man City	Tottenham	Chelsea	Arsenal
40	Liverpool	Man City	Fulham	x	x	x	x
41	Man Utd	Fulham	Chelsea	x	x	x	x
42	Liverpool	Tottenham	Brentford	Brighton & HA	x	x	x
43	Arsenal	Chelsea	Aston Villa	Man Utd	Liverpool	Man City	x
44	Arsenal	Man City	Chelsea	x	x	x	x
45	Newcastle Utd	Tottenham	Chelsea	x	x	x	x
46	Newcastle Utd	Liverpool	Chelsea	x	x	x	x
47	Newcastle Utd	Tottenham	Brentford	Aston Villa	Liverpool	Arsenal	AFC Bournemouth
48	Arsenal	Newcastle Utd	x	x	x	x	x
49	Newcastle Utd	Man City	Brentford	Crystal Palace	x	x	x
50	Arsenal	Tottenham	Chelsea	x	x	x	x
51	Newcastle Utd	Tottenham	Brentford	Brighton & HA	x	x	x
52	Tottenham	x	x	x	x	x	x
53	Arsenal	Man City	Aston Villa	Brighton & HA	x	x	x
54	Newcastle Utd	Fulham	Chelsea	x	x	x	x
55	Newcastle Utd	Fulham	Brentford	Brighton & HA	x	x	x
56	Newcastle Utd	Tottenham	Aston Villa	Man City	Liverpool	AFC Bournemouth	Chelsea
LMS #4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H3:XFD3,1:2,A3:E3,4:1048576	Expression	=ROW(A1)=CurrentRow	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I6:O56	List	=$H$139:$H$159

I am looking to auto populate the 'x' in any cell that has a strikethrough on the adjacent left cell? Obviously as the weeks move on I would need this to happen in any cell adjacent to a strikethrough no matter what column it was in?

 

[Micron]

I suspect you could do this with VBA (a macro) but the sheet events are limited. Perhaps could use right click or double click on a cell but a button on the sheet would be better, I think. Or you call the code from the QAT or Macros option on the Code tab. My thinking is that the logic is like so:
- code loops through all cells in a range (don't know if that's every column you've shown or not)
- it determines if the cell font is strikethrough or not; e.g. ( .range("G4").characters.font.strikethrough ) for example
- - if not it moves on to the next cell
- - if true, it tests if next cell to right contains any value.
- - - if true then do nothing. Either there is already an x there, or you have 2 adjacent cells with strikethrough.
- - - if false, put an x in the cell to the right and each cell to the last column. Again, not sure where that will be in the future.

If there is a cell formula or CF formula that will do what you want there's no way I could figure that out.

 

[rjn008]

I suspect you could do this with VBA (a macro) but the sheet events are limited. Perhaps could use right click or double click on a cell but a button on the sheet would be better, I think. Or you call the code from the QAT or Macros option on the Code tab. My thinking is that the logic is like so:
- code loops through all cells in a range (don't know if that's every column you've shown or not)
- it determines if the cell font is strikethrough or not; e.g. ( .range("G4").characters.font.strikethrough ) for example
- - if not it moves on to the next cell
- - if true, it tests if next cell to right contains any value.
- - - if true then do nothing. Either there is already an x there, or you have 2 adjacent cells with strikethrough.
- - - if false, put an x in the cell to the right and each cell to the last column. Again, not sure where that will be in the future.

If there is a cell formula or CF formula that will do what you want there's no way I could figure that out.

Thanks @Micron, I aren't a VBA person, are you? Could you draft me the code up to test?

 

[Micron]

Thanks @Micron, I aren't a VBA person, are you?

I dabble in Excel VBA; I know Access VBA better.
I probably can come up with something in between doing other things on my plate, so just not right away. It would be nice to know
- where the right most column would be for inserting X (or will it be dynamic?)
- where the left most column is - can't tell if Week1 is col A or something else
- OK if I code it so that X is put in every cell to the right most column in your range (i.e. if there's anything else in there it will get over written)?

That's all I can think of that is unclear at this point. If you can answer those questions I'll take a stab at it. I'd prefer to not just do it and have you ask for a bunch of tweaks that I could have prevented.
EDIT - even better if you could upload a file copy to a file sharing service and post a link (but still clear up where the right most column will be). You can't upload files here.

 

[Micron]

I had some time (and a nice glass of Shiraz to keep me going). This works for me:

Private Sub CommandButton1_Click()
Dim Lrow As Long, Lcol As Long
Dim rng As Range, rng2 As Range, cel As Range
Dim row

On Error GoTo errHandler
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

With ActiveSheet
'find last row and column with data
    Lrow = .Cells.Find(what:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With
'create range to loop over based on last row & column with data
Set rng = Range(Cells(2, 1), Cells(Lrow, Lcol))
'loop over rows
For Each row In rng.rows
    'loop over cells in the current row
    For Each cel In row.Cells
        'if cel characters font setting is strikethrough:
        If Not cel Is Nothing And cel.Characters.Font.Strikethrough Then
            'create range from next column to right to last column (based on what Lcol is)
            Set rng2 = Range(Cells(cel.row, cel.Column + 1), Cells(cel.row, Lcol))
            'remove potential strikethrough setting from rng2 lest a cell have previously been strikethrough
            rng2.Characters.Font.Strikethrough = False
            'set rng2 cells to equal X
            rng2 = "X"
        End If
    Next
Next

exitHere:
'clean up
Set rng = Nothing
Set rng2 = Nothing
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Exit Sub 'exit if no error

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

I have done limited testing. I can see that if you delete a value that was strikethrough and don't remove that formatting, it will cause x's to be placed in that row. I think you can avoid that if you want to change a line to this:
If Not cel Is Nothing And cel.Characters.Font.Strikethrough And cel <> "" Then

HTH

EDIT - I know a lot of coders don't like looping over rows/columns but I don't see how to avoid that given that for any row, the starting point can be any column. I did manage to avoid looping over a range to set the X though. With your data sample, it is very fast for me, so unless you find it slow, I wouldn't worry about that.

 

[rjn008]

Thanks @Micron I will give it a try. For clarity the cell week 1 on my sheet is actually 'I5' so I trust I just change 'A1' to 'I5' in your coding?

I can use it on sample data myself before putting it into my actual working sheet.

Thanks again, I will be back later on today with the outcome.

One quick question, is this working code or does it need a button like I have on my 'Teams Count' above?

 

[rjn008]

@Micron I have created this with a button as I have read the code and realised it is a button click. However it isn't working in my sheet, I will upload a copy for you to the below link;

https://drive.google.com/file/d/18g6AQdT6qSoNtUCjQcBWXSzczraHB_7m/view?usp=drive_link

Let me know the link works and your findings, the code is in their on the below button;

 

[Micron]

As per item number 1) in my tips in my signature, what is "isn't working"?
The code should be on a sheet since the button is on a sheet. I didn't write it for use in a standard module so that may be the issue. Might not be the issue but that's what I suggest you try first.
Your link requires authorization, so I clicked the option to ask for it. Not sure what that all means.

EDIT - After a few sips of coffee, I can see that as it is, the code will have no idea what sheet the Range is on because it wasn't written to be used in a standard module.

 

[rjn008]

Sorry for being daft...

How would I assign your code to the button then, when it isn't from a module? I can add the code to the sheet code, but I can't get my head round how it would work.

I have given you permission to the file now, so hopefully you can update for me and I can understand what you have done.

 

[Micron]

How would I assign your code to the button then, when it isn't from a module? I can add the code to the sheet code, but I can't get my head round how it would work.

The code behind a sheet is in a sheet module. There are 3 types of modules in Excel (that I know of): class, standard, sheet and maybe Workbook (not sure of that). Look beside the code in the image you posted - you have 15 sheet modules; the last in the list is the workbook module. Double click the name of the sheet that this code is to be used on to open it's module and paste the code into there somewhere.

That link is for editing the data in a Google sheet. You'd need to upload a file as a file share to anyone with the link, unless you know how to give permissions for a file download to a particular person. I don't but I suppose it might depend on the file share application. Or maybe there is a way to get at the code behind a Google sheet but I've never used them.