refering to CheckBox's in a command button

[Brian934]

Hey everyone,

I am having a bit of a problem with a userform. The goal is to allow the user to select which items he/she wants to divide the total contents of a cell to using checkboxes. i.e if the user selects two of the four checkboxes divide the cell by two and paste the values to the two appropriate cells. If the user selects three checkboxes, divide by three and paste to the three appropriate cells, etc. I have tried do this several ways so far and nothing is working. Here was my last fatal attempt:

Private Sub CommandButton1_Click()

Sheets("Instructions&quot.Activate

Set cell1 = Worksheets("Instructions&quot.Range("A39&quot'if _
'one check box is chosen use this cell
Set cell2 = Worksheets("Instructions&quot.Range("B39&quot'this _
'cell is just A39 divided by two
Set cell3 = Worksheets("Instructions&quot.Range("C39&quot'by 3
Set cell4 = Worksheets("Instructions&quot.Range("D39&quot'by 4

Set P = Worksheets("Labor 1&quot.Range("G15&quot'cells represented
'by the checkboxes that if checked paste value here
Set PP = Worksheets("Labor 1&quot.Range("G16&quot
Set PPP = Worksheets("Labor 1&quot.Range("G17&quot
Set PPPP = Worksheets("Labor 1&quot.Range("G18&quot

'if the first two checkboxes are clicked
If CheckBox1 = True + CheckBox2 = True _
+ CheckBox3 = False + CheckBox4 _
= False Then
cell2.Copy
P.PasteSpecial (xlPasteValues)
PP.PasteSpecial (xlPasteValues)
End If

End Sub
This is all suppose to be executed when you click OK
I know I have the wrong syntax somewhere, I just cannot see it right now. At first I was doing all of this with code, but thought it might be easier if I did the simple math right on the worksheet in hidden rows.
Thanks in advance for any help,
Brian

 

[Zathras]

I'm having a very hard time trying to figure out just what you are wanting to do. I have no idea of how your user interface involving 4 check boxes is supposed to be interpreted.

Based on your specs, I cobbled together a spreadsheet that looks like this:

Sheet: "Instructions"[blue]

A39: 120
B39: =A39/2
C39: =A39/3
D39: =A39/4

[/color]
Sheet: "Labor 1"[blue]

F15: 4
G15: =IF(F15=1,Instructions!A39,IF(F15=2,Instructions!B39,IF(F15=3,Instructions!C39,IF(F15=4,Instructions!D39,0))))
G16: =IF($F$15>1,G15,0)
G17: =IF($F$15>2,G16,0)
G18: =IF($F$15>3,G17,0)

[/color]
By varying the number in cell F15 from 1 to 4 I get the following display (all in "Labor 1" sheet):

   F15      G15     G16     G17     G18
  -----    -----   -----   -----   -----
    1       120      0       0       0
    2        60     60       0       0
    3        40     40      40       0
    4        30     30      30      30

Is that what you expect?

BTW, it is rather unexpected to see so many "Set" operations in macro code as you presented it. But if you really need them, then you should be sure to set each of them to "Nothing" at the end to free up the memory you assigned.

 

[Brian934]

Thanks Zathras,

That almost has it, but I need to be able to differentiate between which cost code (which are the G15-G18 and the checkboxes) is checked on the userform, not just how many are checked. The user could check just G17 and G18, for example, and I would need to have 60 (using your numbers) in each cell. I apologize for the code earlier, I was very frusterated because this is one of the last steps in an application I am doing, and this should be much easier than it is being.

 

[Zathras]

So if I understand correctly, what you have is one input field ("A39&quot, and four output fields ("G15" thru "G18&quot that are controlled by four check boxes.

Unless you have more on your form than you have indicated, I would consider just puting the check boxes directly on the sheet.

Is this the effect you're after? -- assume Cell A39 on sheet "Instructions" contains the value 120, then the "Labor 1" sheet could look like this:

         F          G
14   Cost Codes   Basis
15    [ ] xyz       0
16    [x] abc      60
17    [x] def      60
18    [ ] ghi       0

Or like this (according to what's checked)

         F          G
14   Cost Codes   Basis
15    [x] xyz      40
16    [ ] abc       0
17    [x] def      40
18    [x] ghi      40

Would that do what you need?

 

[Brian934]

That is exactly what I need to do. Sorry, I wasn't clearer to begin with.

Brian

 

[Zathras]

Ok, here goes. (You may already know how to do most of this, but for the sake of anyone else reading this thread I want to be complete.)

1. Start a worksheet with two sheets: "Instructions" and "Labor 1"
2. Select sheet "Instructions" and enter 120 in cell A39.
3. Select sheet "Labor 1" and select cell F15
4. From the menu: View/Toolbars/Control ToolBox
5. Be in "Design Mode" (Toggle the button that looks like a draftsman's triangle, ruler and pencil.)
6. Click the checkbox icon.
7. Click near the upper-left hand corner of cell F15.
8. Repeat for cells F16 thru F18.
9. Select the first check box (F15).
10. Click the "Properties" tool in the toolbar (finger pointing to a sheet.)
11. Set the Caption to a description of the first cost element.
12. Set the LinkedCell to F15.
13. Repeat for the other three checkboxes, linking to F16, F17 and F18 respectively.
14. Enter this formula into cell G15:[blue]

   =IF(G16,Instructions!$A$39/($G$16+$G$17+$G$18+$G$19),0)

[/color]
15. Copy the formula from G15 to G16 thru G18.
16. Exit "Design Mode"
17. Close the toolbar and properties inspector (optional).
18. Click each checkbox once or twice. (Since the initial values of cells F15 thru F18 were blank, the check box shows grey. Clicking sets the linked cell to either TRUE or FALSE.)

Note that $G$16+$G$17+$G$18+$G$19 produces the desired result while SUM($G$16,$G$17,$G$18,$G$19) doesn't. It has to do with the way Excel treats raw boolean values.

 

[Zathras]

I should have added somewhere around step 3 to select rows 15 thru 18 and set the row height to 18.75 or so, in order to make room for the check boxes.

 

[Brian934]

Works Great, Thanks Zathras

Just to clarifiy for others, the formula should have read:
=IF(F15,Instructions!$A$39/($F$15+$F$16+$F$17+$F$18),0)

Thanks again Zathras, I was getting too caught up on using VBA to solve every problem.

 

[Zathras]

Thanks for the correction. Somewhere along the line my brain jumped the track from using columns G and H to using columns F and G.

Glad to help.