Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Userforms - especially check boxes
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 checkboxs corresponding cell instead of in the second checkboxs cell. A solution to this I thought was: With each checkboxs 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 labels caption to the scroll bar's value Private Sub HSBrating_Change() LBLrating.Caption = HSBrating.Value A label called ratings 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 doesnt display which value the scrollbar is at whilst youre 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 dont want. I dont 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. Im especially interested in learning about that checkboxes problem. Thanks Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA Userforms - especially check boxes
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 "Phil1982" wrote: 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 checkboxs corresponding cell instead of in the second checkboxs cell. A solution to this I thought was: With each checkboxs 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 labels caption to the scroll bar's value Private Sub HSBrating_Change() LBLrating.Caption = HSBrating.Value A label called ratings 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 doesnt display which value the scrollbar is at whilst youre 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 dont want. I dont 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. Im especially interested in learning about that checkboxes problem. Thanks Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and move check box (check boxes) with new cell link? | Excel Worksheet Functions | |||
Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
check box in userforms | Excel Programming | |||
tick boxes on userforms | Excel Programming | |||
Text Boxes on Userforms | Excel Programming |