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]

Could you upload your workbook (without sensitive data) somewhere (maybe via dropbox.com or google drive)?
Then put the link here.
It's easier to find a solution when a sample workbook is provided.

 

[My Aswer Is This]

So tell me.

What are the Possible choices you want entered in Column B

What are the Possible choices you want entered in Column C
What are the Possible choices you want entered in Column D​

 

[Shremlar]

Could you upload your workbook

unfortunately i cannot... its on a standalone system that I cannot connect anysort of recordable media to.

 

[Shremlar]

What are the Possible choices

Column B would be the comboboxes/names (Column A is for something else)..

Column C would be Arrived on time
Column D would be Paid for Lesson
Column E would be Satisfactory Performance

 

[My Aswer Is This]

I never open links.

 

[My Aswer Is This]

I do not want a combobox in column B

What would be in the combobox?

Are these student names?

If so where does the combobox get these names from?

Where is the list of all student names?

 

[Shremlar]

I do not want a combobox in column B

What would be in the combobox?

Are these student names?

If so where does the combobox get these names from?

Where is the list of all student names?

Yes... as i said in the original post, Sheet2 has a list of all the student names... which i can easily use to fill the comboboxes with

 

[My Aswer Is This]

So why can we not put a Data Validation list in Column A
Have the user select the name from the Data validation list

You would not have to enter all these names into the list like I earlier explained.

Tell Me exactly where on sheet(2) this list of names are.

Say some like this Sheet(2). Range("A1:A45")

 

[My Aswer Is This]

See if you will just keep following my logic.
I can exclude you needing 100 comboboxes and 300 check boxes

So in columns C D and E

Can the user enter a value into all three columns or is he only allowed to enter one.

So can he choose Cake for column C and Pie for column D and Candy for column E

Or must he choose only One?

Now you understand these are just examples.

 

[Shremlar]

So why can we not put a Data Validation list in Column A ... Tell Me exactly where on sheet(2) this list of names are

I suppose i could use Data Validation list for the names... in Column B... not A, Column A is already being used for something else... however with 60 names, in a combobox i will get a scroll bar that makes going through the names to find the right one a lot easier...

Specifically the location of the names is: Worksheets(“Sheet2”).Range(“B3:B62”)