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”

 

[Shremlar]

THAT DIDNT POST RIGHT... my BAD! Lets try again.

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

Scenario… lets say I teach tap-dancing (why not) every Thursday to alot of different students (60) … but only a few students (min1, max 10) show upto each lesson… but it could be any of them on a given day. I want to have alog that will keep track of which students showed up on a given day and whichof the assigned tasks they completed that day. Since a max of 10 students showup I want improve the code by using 10 comboxes 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… andnext to each name I have 3 check boxes that denote the tasks (for example: arrivedon time, payed for days lesson, & performed satisfactorily) . I also have a button that transfers thestatus of the checkboxes to another sheet (Sheet2).

Sheet2 also has every student name listed in column B, but I have everycolumn to it’s right grouped into 3-column groups (one for each task) . ROW1 ofthe first column in every group is set as the date of every Thursday for theforeseeable 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 currentsetup an requires 180 checkboxes on Sheet1


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

 

[My Aswer Is This]

I would never use a whole lot of Check Boxes.
Why not have all the Students names in Column B
And in column C have a Data Validation List where you can choose:
arrivedon time, payed for days lesson, & performed satisfactorily
Something like this.
Then you choose which of the 3 choices you want and this will be entered into column 3
Then instead of having buttons to click on
You can just double click on a cell to perform the task you want.
If your interested in this approach let me know.

Creating large numbers of Checkboxes and Comboboxes and Buttons is not the best or easiest way to do things.

 

[Shremlar]

I might be interested... but i have no idea what a Data Validation List even is..

 

[My Aswer Is This]

Do this:

Click Column B

This now selects all cells in Column(B)


On Your Menu Bar choose
Data
Then choose Data Validation
Then Choose Allow
Then choose List
Then in Source enter

Cat,Dog,Mouse

Now click Ok

Now the user when he clicks on any cell in Column B he will see a little Drop down arrow and he must Choose Cat Dog or Mouse


Then that value is entered into the cell

 

[Shremlar]

I might be interested... but i have no idea what a Data Validation List even is..

so i looked into Data Validation List... and I'm confused why that would be easier than a check box... maybe its just my situation but i need to be able to quickly enter this data... as it becomes true.

a simple true/false checkbox seemed to be the best way to do this. having three drop-down lists next to each name / combobox seems complicated

 

[Shremlar]

Do this:

Click Column B

This now selects all cells in Column(B)


On Your Menu Bar choose
Data
Then choose Data Validation
Then Choose Allow
Then choose List
Then in Source enter

Cat,Dog,Mouse

Now click Ok

Now the user when he clicks on any cell in Column B he will see a little Drop down arrow and he must Choose Cat Dog or Mouse


Then that value is entered into the cell

AHHH.. the issue with this is that all three options could be true for each student ... which is why i went with checkboxes

 

[My Aswer Is This]

I just provide a suggestion.

If I understand what your attempting to do is have three check boxes on each row and then you will check each check box as to true or false

So if you have 10 Names in Column A you will need 150 checkboxes

Or do I not understand.

 

[Shremlar]

that is correct... any number of the checkboxes for a given row could be true or false... so even if i switched to Data Validation Lists i would still need 3 for each row

 

[Shremlar]

that is correct... any number of the checkboxes for a given row could be true or false... so even if i switched to Data Validation Lists i would still need 3 for each row

sorry... not correct.. 30 check boxes... if there are 10 names there would be 30 checkboxes/lists