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

Here at work the firewall prevents me from downloading it. I will get the workbook at home.

 

[Worf]

I got the workbook and successfully tested it.
What exactly happens when other people use it? Error message? What code line?
Get data is command button one for me.
We may try variations such as linking the code to a shape instead of an ActiveX control to see the outcome.

 

[Worf]

To test using a shape, follow this procedure:

- Paste the code below on a module
- Add a rectangular shape to the query sheet, resembling a button
- Right click the shape
- Choose the assign macro option
- Click the desired routine and press OK
- Click the shape to run the code

Sub Wood()
Dim group1, i%, d%, group2, nl%, dest As Range, j%, la%, ws As Worksheet
Set ws = Sheets("Query")
For j = 13 To ws.Range("d" & Rows.Count).End(xlUp).Row                 ' loop SKUs
    group1 = Array("void"): group2 = Array("void")
    d = 0
    For i = 0 To 7                                                  ' group 1
        If ws.OLEObjects("CheckBox" & (i + 1)).Object.Value Then
            If i > 0 And UBound(group1) < d Then ReDim Preserve group1(d)
            group1(d) = ws.OLEObjects("CheckBox" & (i + 1)).Object.Caption
            d = d + 1
        End If
    Next
    d = 0
    For i = 8 To 60                                                 ' group 2
        If ws.OLEObjects("CheckBox" & (i + 1)).Object.Value Then
            If i > 0 And UBound(group2) < d Then ReDim Preserve group2(d)
            group2(d) = ws.OLEObjects("CheckBox" & (i + 1)).Object.Caption
            d = d + 1
        End If
    Next
    nl = (UBound(group1) + 1) * (UBound(group2) + 1)
    la = ws.Range("a" & Rows.Count).End(xlUp).Row + 1
    ws.Cells(la, 1).Resize(nl) = ws.Cells(j, 4)                     ' fill column A
    Set dest = ws.Cells(la, 2).Resize(UBound(group1) + 1, 1)
    dest = Application.Transpose(group1)
    For i = 1 To UBound(group2)                                     ' fill column B
        dest.Offset((UBound(group1) + 1) * i) = dest.Value
    Next
    For i = 1 To UBound(group2) + 1                                 ' fill column C
        ws.Cells(la, 3).Offset((i - 1) * (UBound(group1) + 1)).Resize _
        (UBound(group1) + 1) = group2(i - 1)
    Next
Next
End Sub

 

[Grinch356]

Hi,

This works brilliantly - all users can now use this. Thank you so much for your help again.