Check box output script

Grinch356

New Member
Joined
Jan 6, 2015
Messages
31
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​
 
Hi

Here at work the firewall prevents me from downloading it. I will get the workbook at home.
 
Upvote 0
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.
 
Upvote 0
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

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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top