Check box output script

[Grinch356]

Hi,

Not sure if this can be done in excel or if I need to create some sort of special form but....

I am looking to create an output script based on ticking chackboxes in excel.

The user would manually enter into a cell A3 an item code - lets call it 11111

They then use a button, probably a checkbox to select multiple areas that they want in the output - there would be about 8 to choose from, lets say in this case the user ticks three of them, those being 'North', 'South' and 'East'.

Finally they selct the weeknumbers they want to apply, again probably from checkboxes - in this case they select weeks '5' '6' and '7'.

This would then produce an output on the sheet in columns A, B and C that looks like this (probably by pushing a button)

11111 North 5
11111 South 5
11111 East 5
11111 North 6
11111 South 6
11111 East 6
11111 North 7
11111 South 7
11111 East 7

Any help, guidance would be appreciated because I am a bit lost on this one, not sure if I need to use VBA to get it to work, here is a link to what I think the sheet might look like and the checkboxes:
https://onedrive.live.com/redir?resid=3AE8882B3E11F58!130&authkey=!AC6kgNcOYpKHuhI&ithint=file,xlsx

thanks for looking​

 

[Worf]

Hi

I'm going offline now but will return tomorrow with a solution...

 

[Grinch356]

Hi,

Thanks, thats great, I am still stuck on this one.

 

[Worf]

Hi

Tell me if you have any trouble understanding this example:

Private Sub CommandButton1_Click()
Dim group1, i%, d%, group2, nl%, dest As Range
group1 = Array("void"): group2 = Array("void")
d = 0
For i = 0 To 3                                                  ' group 1
    If Me.OLEObjects("CheckBox" & (i + 1)).Object.Value Then
        If i > 0 And UBound(group1) < d Then ReDim Preserve group1(d)
        group1(d) = Me.OLEObjects("CheckBox" & (i + 1)).Object.Caption
        d = d + 1
    End If
Next
d = 0
For i = 4 To 11                                                 ' group 2
    If Me.OLEObjects("CheckBox" & (i + 1)).Object.Value Then
        If i > 0 And UBound(group2) < d Then ReDim Preserve group2(d)
        group2(d) = Me.OLEObjects("CheckBox" & (i + 1)).Object.Caption
        d = d + 1
    End If
Next
nl = (UBound(group1) + 1) * (UBound(group2) + 1)
Range("a20").Resize(nl).Value = Range("a14")                    ' fill column A
Set dest = Range("b20").Resize(UBound(group1) + 1, 1)
dest.Value = Application.Transpose(group1)
For i = 1 To UBound(group2)                                     ' fill column B
    dest.Offset((UBound(group1) + 1) * i).Value = dest.Value
Next
For i = 1 To UBound(group2) + 1                                 ' fill column C
    Range("c20").Offset((i - 1) * (UBound(group1) + 1)).Resize(UBound(group1) + 1).Value _
    = group2(i - 1)
Next
End Sub

 

[Grinch356]

Hi,

Thanks for this - I am having some trouble getting it to work - could you possibly share the working model you have screenshotted here and I can figure it out. I am having some trouble with the 'ME' keyword and grouping the checkboxes.

Thanks for your efforts on this, it is much appreciated.

 

[Worf]

Hi

See if the workbook helps. You may use the debugger and step through the code.

https://dl.dropboxusercontent.com/u/52116836/Checkboxes.xlsm

 

[Grinch356]

Hi,

That is a lot clearer to me now, thank you - just one question - how did you define the groups 1 and 2, I cant see how to do it?

 

[Worf]

They are virtual groups, I’m referring to the individual controls by index and name on the code.

Group1 – CheckBox1 to CheckBox4
Group2 – CheckBox5 to CheckBox12

We could place each group inside a separate frame, but it’s not necessary.

 

[Grinch356]

Yes, I can see that now - clever stuff!! I like it.

Thanks for your help, think I am am all sorted now, really appreciate it.

 

[Grinch356]

Yep, fully tested and I understand it enough to mess around with it, great stuff, thanks so much.