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]

You are welcome

 

[Grinch356]

You are welcome

Hi there,

Thanks so much for your help with this all that time ago. I am trying to work out how to update my version with a new functionality. Where I have created the box to enter a SKU I was trying to enable it to allow me to enter more than one SKU at once, and still perform the same functionality for each of the SKUs I paste in. Any ideas would be greatly appreciated as I have been using this for a long time now and just need to modify it. I have attached my current version if you can be of any help, thanks in advance. https://www.dropbox.com/s/bhm781bfuizwdby/CheckboxWriter.xlsm?dl=0

 

[Worf]

Hello again

You could use an ActiveX list box control with multiple selection enabled, as shown in the picture below.
Would the SKU list come from column A at the weekly dump sheet?

 

[Grinch356]

Hi, no, weekly dump is where I put the results from the query tab. I am looking for a solution that allows multiple SKUs to be input rather than selected. Essentially I am sent a list and I paste them in, any ideas?

Thank you for coming back to me by the way, much appreciated.

 

[Worf]

So you could simply paste the list at the query sheet. The code would then loop through this range, instead of just referring to cell C13.
Is the list big or can you type it? Is it easily pasted into Excel? Where does it come from?
Do you need code to eliminate duplicates and generate a column of unique values?

 

[Grinch356]

Hi, the bit I am having trouble with is how to adjust the code so it does not just reference C13 and instead will loop through the process for however SKUs i may enter in (we are talking less than 100). I cannot figure out how to adjust the coding to do this? I am guessing it is just a small tweak somewhere?

 

[Worf]

See if this does what you want; note that the SKUs must be placed at column D, starting at row 13.

Private Sub CommandButton1_Click()
Dim group1, i%, d%, group2, nl%, dest As Range, j%, la%
For j = 13 To 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 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 = 8 To 60                                                 ' 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)
    la = Range("a" & Rows.Count).End(xlUp).Row + 1
    Cells(la, 1).Resize(nl) = Cells(j, 4)                           ' fill column A
    Set dest = 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
        Cells(la, 3).Offset((i - 1) * (UBound(group1) + 1)).Resize _
        (UBound(group1) + 1) = group2(i - 1)
    Next
Next
End Sub

 

[Grinch356]

Hi, sorry for the late response - I have been away. This works perfectly and does exactly what I need, thank you so much for your help, much appreciated.

 

[Worf]

I'm glad it's working.

 

[Grinch356]

Hi, I am still very grateful for the support you gave me with this but after running without an issue for some time I have now discovered a problem. When someone else attempts to use this tool from a PC other than my own it does not work - do you know what could be causing this? It works fine for me but not for anyone else. I have noticed that when other people open it, when I click 'properties' on the Get Data button (which is where the troublesome code is attached to) everyone else sees it named as 'CommandButton2' but everyone else sees it named as 'CommandButton1' whcih is strange because we all enter from a shared file. I have attached a link and would be hugely grateful for any advice. Many thanks for all your help so far.
https://1drv.ms/x/s!Algf4bOCiK4DgQ08LpHUGjvdQk2T