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]

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.

I wouldn't need 300 checkboxes… only 30. 10 comboboxes, each one has a student selected… 3checkboxes next to each one. That’s why I want to use a combobox

 

[My Aswer Is This]

That's OK if you want to use Combo box and 30 Checkboxes that's your decision.

I would not be able to help you with this any more.

So I'm sure someone else here at Mr. Excel will be able to help you.
Take care

 

[My Aswer Is This]

Well I never like to give up.
Ok Why do you need a Combobox for every student?
A combobox is designed to have a whole lot of names in it and then you choose the name you want.
So if we have as your first post said 60 Student names in Sheet(1) column B why can we not load all those names into just one Combobox on Sheet(1)
And then you choose the name you want.
Then we just have Three check boxes on Sheet(1)





And depending which one is chosen the script enters the x in the proper column on sheet 2 next to the name chosen from the Combobox

I did not exactly follow the part about looking for Date are you saying Row(1) of sheet(2) has dates

So if today was 2/6/2018

And Sheet(2) Range("X1") had todays date the x would go into what column ?

 

[Akuini]

Hi, Shremlar
I think I can help you with the combobox part. I created a combobox that has 2 special behavior:
1. The combobox can appear and hide automatically when you select a cell in a certain range.
2. You can type a keyword in the combobox and the results will be narrowed down as you type.

So you only need 1 combobox for all cells that need to be filled.
Here's a screenshot:

Let me know if you're interested in this method.

 

[Shremlar]

Hi, Shremlar
I think I can help you with the combobox part. ... Let me know if you're interested in this method.

Sorry for the late reply... i am interested that would definitely help. Although the primary reason i posted this was because I can’t figure out how to get an X to populate on Sheet2 ina cell with the current week’s column and the chosen names row.

 

[cnestg8r]

I think the advice you are getting is the best direction. However, if you want to use checkboxes, you might consider using user forms. You could have all the check boxes and combo boxes you wish. The database records would only carry the results of the user form inputs.

 

[Akuini]

I can’t figure out how to get an X to populate on Sheet2 ina cell with the current week’s column and the chosen names row.

Well, I think I can help you with that part too.
Can you post your data layout (in both sheets) as table? I need to know the data layout.
OR
You can upload your sample workbook (without classified data) somewhere (maybe to dropbox.com or google drive) then post the link here.

 

[Shremlar]

I created a miniature version in excel… didn’t recreate the code, justmade it look like it would and saved it as an image. Can be found on Googledrive here:

https://drive.google.com/file/d/1Rwmbl4RmhCCApJQejtRHFu2INGw1wTbm/view?usp=sharing

 

[Shremlar]

My current code is set up as it is below... and it relies on the list of names on Sheet2 to be perfectly mirrored on Sheet1... and 3 checkboxes for each name.

Private Sub Button_SAVE_Click()


If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox1.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(2, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox2.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(2, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox3.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(2, 2).Value = "X"
End If



If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox4.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(3, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox5.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(3, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox6.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(3, 2).Value = "X"
End If


If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox7.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(4, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox8.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(4, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox9.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(4, 2).Value = "X"
End If



If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox10.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(5, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox11.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(5, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox12.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(5, 2).Value = "X"
End If



If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox13.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(6, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox14.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(6, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox15.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(6, 2).Value = "X"
End If


If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox16.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(7, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox17.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(7, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox18.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(7, 2).Value = "X"
End If




If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox19.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(8, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox20.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(8, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox21.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(8, 2).Value = "X"
End If



If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox22.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(9, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox23.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(9, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox24.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(9, 2).Value = "X"
End If




If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox25.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(10, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox26.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(10, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox27.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(10, 2).Value = "X"
End If



If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox28.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(11, 0).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox29.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(11, 1).Value = "X"
End If
If Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Value = Date And CheckBox30.Value = True Then
    Worksheets("Sheet2").Range("C1:ZZ1").Find(Date).Offset(11, 2).Value = "X"
End If

End Sub

 

[Shremlar]

...you might consider using user forms. ...

I have considered userforms as an option… but I’m not sure how to work withthem yet… and I want to get an idea of how the mechanics will work… this isbrand new and will likely be growing and changing in the future, so I wouldlike to get a basic mechanic working before I start “polishing” it.