Excel VBA Userforms - especially check boxes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

Relevant Pages

  • RE: Excel VBA Userforms - especially check boxes
    ... Private Sub CommandButton1_Click ... It would be hard to say anything about the scrollbar - if the event doesn't ... If I tick a checkbox I want Excel to put TRUE in the ... corresponding cell. ...
    (microsoft.public.excel.programming)
  • Re: Dealing with Tables in VBA
    ... n-1 paragraphs in any cell in the second column of any table after the ... "No Filter Required" ... Anytime the checkbox next to "No FIlter Required" is checked, ... Find a occurance of a text string "Data Filter Requirements" ...
    (microsoft.public.word.vba.general)
  • Re: Dealing with Tables in VBA
    ... That is the end of cell marker and will be treated as a separate paragraph. ... However, if the checkbox that you are looking for is on the previous line, ... "No Filter Required" ... Find a occurance of a text string "Data Filter Requirements" ...
    (microsoft.public.word.vba.general)
  • Re: Dealing with Tables in VBA
    ... looks similar to an asterisk at the end of the cell, ... "No Filter Required" ... Anytime the checkbox next to "No FIlter Required" is checked, ... Find a occurance of a text string "Data Filter Requirements" ...
    (microsoft.public.word.vba.general)
  • Re: Problem vertically aligning multiple rows with tickboxes added using VB.
    ... Only thing I can think of is when creating the object hold the ALT key down, this will *snap* the object to the top left of the cell. ... Dim myCBX As CheckBox ... Dim myCell As Range ... That triggers my conditional formatting colour change depending on the TRUE or FALSE result from the checkbox. ...
    (microsoft.public.excel.newusers)