Vba combobox / offset help please

[Shremlar]

I have a code that works… but it is really really cumbersome. I would like to improve it by using comboboxes.

Scenario… lets say I teach tap-dancing (why not) every Thursday to a lot of different students (60) … but only a few students (min1, max 10) show up to each lesson… but it could be any of them on a given day. I want to have a log that will keep track of which students showed up on a given day and which of the assigned tasks they completed that day. Since a max of 10 students show up I want improve the code by using 10 comboboxes to select the names to process, rather than writing code for every single name.

On Sheet1 I currently have every student name listed in column B… and next to each name I have 3 check boxes that denote the tasks (for example: arrived on time, paid for days lesson, & performed satisfactorily) . I also have a button that transfers the status of the checkboxes to another sheet (Sheet2).

Sheet2 also has every student name listed in column B, but I have every column to it’s right grouped into 3-column groups (one for each task) . ROW1 of the first column in every group is set as the date of every Thursday for the foreseeable future. And ROW2 of every column is set the task names.

The cumbersome, but working code looks like this:

Private Sub Button_SAVE_Click()

If Worksheets(“Sheet1”).Range(“C1:ZZ1”).Find(Date).Value = Date AndCheckbox1.Value = True then
Worksheets(“Sheet2”).Range(“C1:ZZ1”).Find(Date).Offset(2,0).Value = “X”
End If

If Worksheets(“Sheet1”).Range(“C1:ZZ1”).Find(Date).Value = Date AndCheckbox2.Value = True then
Worksheets(“Sheet2”).Range(“C1:ZZ1”).Find(Date).Offset(2,1).Value = “X”
End If

If Worksheets(“Sheet1”).Range(“C1:ZZ1”).Find(Date).Value = Date AndCheckbox2.Value = True then
Worksheets(“Sheet2”).Range(“C1:ZZ1”).Find(Date).Offset(2,2).Value = “X”
End If

‘ --- repeated for each student ---


End Sub



the above code works… but I have to repeat it 179 more times with my current setup an requires 180 checkboxes on Sheet1


I want to use comboboxes… but I cant figure out how to make it reference the value against the list of names & the date to get the location of the cell I want it to fill with “X”

 

[Akuini]

Ok, I think I understand what you want.
One thing:
Why not put the date by typing it in a cell, say in B1.
Using "Date" directly in the code has a disadvantage which is say you forget to run the code today then tomorrow the code won't work because it will be different date. If you type it in a cell then it still be the same.

 

[Shremlar]

The end goal is to have a place that anyone... (even if they have no idea how to work with excel) cansit down and enter what happened that day in an intuitive form, and it willrecord the results in a log that only I will really be using. I DO NOT wantanyone else messing with Sheet2, but I have to have other people entering dataon a given day. Currently everyone that collects the stats for the day is justwriting this info on a scrap piece of paper…. Needless to say there is a lot oferrors and inconsistencies in the information they are providing to me.

 

[Shremlar]

That's actually how i originally tried to do it... but then i would have to create code that would account for the difference in dates and find to most recent Thursday… and honestly the stats they are collecting come at them pretty fast and then it’s over… that’s why everyone is using scrap paper… it’s easier to scribble chicken scratch in a hurry vice trying to find a specific cell and enter the data every time. Trying to go back after the fact and figure out what the stats were, would be really difficult honestly, so on the random chance they miss a day… I can handle that myself on a case by case basis.

 

[Akuini]

Ok, I'll get back to you tomorrow when I have time

 

[Akuini]

Actually, I need your sample workbook (not just an image) so I don't have to recreate everything.
You can upload your sample workbook (without classified data) somewhere (maybe to dropbox.com or google drive) then post the link here.
And are you using a Form Control Checkbox not activex control? Any particular reason why?

 

[Shremlar]

I need your sample workbook

As requested:
https://drive.google.com/file/d/1qMS6GtL6IozJNUsWGFf6BDpKMaXamhGd/view?usp=sharing

 

[Akuini]

Ok, try this:

1. It’s easier to get the checkbox value if you link the checkbox to the cell where it resides. So I used “Sub toLink()” to do that.
2. Then to check if the checkbox is linked to the correct cell use “Sub checkLink” then see the result in the immediate window.
3. After the checkbox has linked to a cell then if checkbox value is true then the cell value is also true.
In “Private Sub Button_SAVE_Click” these part shows that the code only need to get the cell value to get the value of the checkbox.

If Not f Is Nothing Then
                    flag = False
                    If r.Offset(, 1) = True Then .Cells(f.Row, x) = "V": flag = True
                    If r.Offset(, 2) = True Then .Cells(f.Row, x + 1) = "V": flag = True
                    If r.Offset(, 3) = True Then .Cells(f.Row, x + 2) = "V": flag = True
                    
                    'if user forget to check anything on a name
                    If flag = False Then MsgBox "You haven't checked anything on " & f.Value
                    
                End If

4. Read the sheet INFO to understand how the "data entry by combo box" works.

Here's the workbook:
https://www.dropbox.com/s/ud8qua2vv3biw8d/a1086463-vba-combobox-offset-help-please 1.xlsm?dl=0