Excel VBA Userforms - especially check boxes

[Phil1982]

Hi

I am trying to create a userform containing 3 check boxes.

Imagine this scenario:
*Pretend at the end of a school year I wanted to create a database regarding
9 pupils’ homework completion.
*There have been 3 homework assignments.
*I want the database to be 4 columns by 10 rows – A row for each pupil and a
column for each subject.
*For each pupil I want to use 3 checkboxes to input whether or not he/she
handed his/her assignments in on time. If I leave a checkbox blank, I want
Excel to put FALSE in the cell corresponding to that pupil and that
assignment. If I tick a checkbox I want Excel to put TRUE in the
corresponding cell.

I realise that there may be simpler or more efficient ways of doing this
(including manually) than using checkboxes….I have just tried to think of a
simple example.


I have tried to create such a form but encountered the following problems:
*When I want to leave a checkbox blank to produce FALSE and I tick the
following checkbox – Excel puts TRUE in that first checkbox’s corresponding
cell instead of in the second checkbox’s cell.

A solution to this I thought was:
With each checkbox’s code, add code at the beginning that selects the
appropriate cell in the active row.
I have tried guessing code to do that but none of the following have worked.
For example if I want data about the 3rd assignment to appear in the 4th
column
ActiveRow.Cells(4).Select
ActiveRow.Column4.Select
Range(,4).Select
Range($4).Select




Another situation
I want to use a scroll-bar. I want to be able to see the value of the
scrollbar as I drag it.

I thought the solution was to add a label and set that label’s caption to
the scroll bar's value

Private Sub HSBrating_Change()
LBLrating.Caption = HSBrating.Value ‘A label called “ratingâ€â€™s
caption = scroll bar value
ActiveCell.Value = HSBrating.Value
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

2 problems I have faced with this are
*It doesn’t display which value the scrollbar is at whilst you’re dragging it
*As a result you have to let go of the mouse to see what value it is at, and
when you do that it inserts that value onto the spreadsheet – which I don’t
want. I don’t want it to insert a value onto the spreadsheet and move the
cursor until I have selected the value I want.


Could someone please help me with solutions to these. I’m especially
interested in learning about that checkboxes problem.




Thanks

Phil

 

[Tom Ogilvy]

Better not to depend on what is selected to determine what to do. Rather be
specific.

Private Sub commandButton2_click()
cells(activecell.row,2).Value = Checkbox1.Value
cells(activecell.row,3).value = Checkbox2.value
cells(activecell.row,4).value = checkbox3.value
end Sub

if you must use the activecell and it will be on the student's name:

Private Sub CommandButton1_Click()
ActiveCell.Offset(0, 1).Value = CheckBox1.Value
ActiveCell.Offset(0, 2).Value = CheckBox2.Value
ActiveCell.Offset(0, 3).Value = checkbox3.Value
End Sub

It would be hard to say anything about the scrollbar - if the event doesn't
fire while you are moving (and I don't believe it does), then you can not
utilize an event to update the label. As to having other actions occur (that
you don't want to occur) based on moving the scrollbar, you could move that
code to a control that requires a more specific action.

--
Regards,
Tom Ogilvy