Populate cells from UserForm data. Depending on the `sub`, the destination cells change

Question

Lets say I have samenstelling1, samenstelling2 and samenstelling3.

In each of those sub modules I call my userform samenstelling and the user fills in the 5 fields.

How do I get the data that was filled on the userform to populate certain cells that differ for each module?

when the user presses OK after filling in their data, I am able to show a msgbox in the sub, so I know the sub is linked with the userform. I'm stuck on how I can paste that data to certain cells.

for samenstelling1 the cells are "Q500:O500", for samenstelling2 the cells are "Q501:O501" etc.

The code for btnok

Public Sub btnok_Click()
Dim letter As String
Dim tekeningnr As String
Dim omschrijving As String
Dim posnummer As String
Dim revletter As String

tekeningnr = txttekeningnummer.Value
omschrijving = txtomschrijving.Value

revletter = cmbrevisieletter.Value
posnummer = cmbposnummer.Value
letter = UCase(cmbletter.Value)

Unload Me

End Sub

The code for samenstelling1

Sub samenstelling1()
Sheets("Artikelen_aanmaken").Activate

Dim letter As String
Dim tekeningnr As String
Dim omschrijving As String
Dim posnummer As String
Dim revletter As String

Samenstelling.UserForm_Initialize
Samenstelling.Show

'this part is not working because it doesn't receive that data from the userform, the cells stay empty

    Range("q500") = cmbletter.Value
    Range("N500") = txttekeningnummer.Value
    Range("P500") = cmbrevisieletter.Value
    Range("R500") = txtomschrijving.Value
    Range("O500") = cmbposnummer.Value

           Select Case posnummer
            Case Is = 1

I've also tried it with the code below but I think it's just defining the data I put in those cells as the string then

        Range("q500") = letter
        Range("N500") = tekeningnr
        Range("P500") = revletter.Value
        Range("R500") = omschrijving.Value
        Range("O500") = posnummer.Value

the sample of the select case I have for the amount of posnummers filled in (So this select case is in each samenstelling_ sub..

Select Case posnummer
            Case Is = 1
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k2"), Type:=xlFillSeries

            Case Is = 2
                Sheets("Artikelen_aanmaken").Activate
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k3"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c3"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i3"), Type:=xlFillDefault

            Case Is = 3
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k4"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c4"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i4"), Type:=xlFillDefault

            Case Is = 4
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k5"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c5"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i5"), Type:=xlFillDefault

            Case Is = 5
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k6"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c6"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i6"), Type:=xlFillDefault

            Case Is = 6
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k7"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c7"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i7"), Type:=xlFillDefault

            Case Is = 7
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k8"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c8"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i8"), Type:=xlFillDefault

            Case Is = 8
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k9"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c9"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i9"), Type:=xlFillDefault

            Case Is = 9
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k10"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c10"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i10"), Type:=xlFillDefault

            Case Is = 10
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k11"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c11"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i11"), Type:=xlFillDefault

            Case Is = 11
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k12"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c13"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i13"), Type:=xlFillDefault

            Case Is = 12
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k13"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c14"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i14"), Type:=xlFillDefault

            Case Is = 13
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k14"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c15"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i15"), Type:=xlFillDefault

            Case Is = 14
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k15"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c16"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i17"), Type:=xlFillDefault

            Case Is = 15
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k16"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c18"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i18"), Type:=xlFillDefault

            Case Is = 16
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k17"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c19"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i19"), Type:=xlFillDefault

            Case Is = 17
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k18"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c20"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i20"), Type:=xlFillDefault

            Case Is = 18
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k19"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c21"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i21"), Type:=xlFillDefault

            Case Is = 19
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k14"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c22"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i22"), Type:=xlFillDefault

            Case Is = 20
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k15"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c23"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i23"), Type:=xlFillDefault

            Case Is = 21
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k16"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c24"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i24"), Type:=xlFillDefault

            Case Is = 22
                Range("18:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k17"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c25"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i25"), Type:=xlFillDefault

            Case Is = 23
                Range("19:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k18"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c26"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i26"), Type:=xlFillDefault

            Case Is = 24
                Range("20:30").EntireRow.Hidden = True
                Range("A2:k2").Select
                Range("A2:k2").AutoFill Destination:=Range("A2:k19"), Type:=xlFillSeries

                    Sheets("Artikelen_in_stuklijsten").Activate
                    Range("18:30").EntireRow.Hidden = True
                    Range("A2:c2").Select
                    Range("A2:c2").AutoFill Destination:=Range("A2:c27"), Type:=xlFillSeries
                    Range("d2:i2").Select
                    Range("d2:i2").AutoFill Destination:=Range("d2:i27"), Type:=xlFillDefault

If the code is the same for each situation apart from the row position, you could convert it into a function, and call it from your userform with passing an integer indicating the row number. — Plutian, Nov 22 '19 at 08:28
Why did you create three of the same modules. You could think about creating just one using some `Select Case` or `If Then` statements. You can also leave out the `.Activate` and use explicit references. — JvdV, Nov 22 '19 at 08:36
@JvdV I've actually created 16 because the `posnummer` value determines which select case comes after that. Those ranges also differ (mainly in starting point) and I couldn't figure out a way to shorten that code so I've settled for trying to use a userform to get the inputs. Instead of activate I can use `With sheets("name") right? — Renate, Nov 22 '19 at 08:49
@Plutian I'm sorry, I'm very new and struggling to understand what that would look like. I'm going to look into functions but I thought calling the `userform initiate` would be similar so I went straight to that. Could you clarify what you mean by passing an integer to indicate the row number? — Renate, Nov 22 '19 at 08:51
So you have 16 modules, for 16 sheets. The only difference is that the input from the userForm needs to be 1 row down on each sheet down those 16 sheets? And you do need the same values from UserForm in all those sheets in one loop too? Or are you calling it seperately? — JvdV, Nov 22 '19 at 08:52
@JvdV No I have 16 modules for 1 sheet. So in the case that there are (e.g.) 14 `samenstellingen`, then the user will have the opportunity to define those 5 variables for each samenstelling. the rows that are filled just give the information that the sheet then uses to fill for the amount of `posnummers`. So `samenstelling1` will populate from row 2-(2+26), all the rows that aren't filled will be hidden for readability (the `select case` will autofill and hide the rows — Renate, Nov 22 '19 at 08:57
@JvdV I'm calling each `samenstelling x` seperately to let the user define that one `samenstelling` and the code will autogenerate the parts lists for that samenstelling — Renate, Nov 22 '19 at 08:59
In that case all you need to do is find the last used row from `Q500` down, and fill that. Or does every samenstelling has it's very own specific row in the sheet? — JvdV, Nov 22 '19 at 08:59
@JvdV I don't even need that because depending on the sub I'm running, the cells are defined. But the problem I'm having is that I don't know how to get the value entered in the form, into my sub so I can populate the cell. It's probably stupidly simple but I don't know how — Renate, Nov 22 '19 at 09:02

Plutian · Answer 1 · 2019-11-22T09:22:56.980

0

This is not complete code, but it should get you started.

Option Explicit

Public Sub btnok_Click()
Dim posnummer As Integer
Public Sub btnok_Click()
Dim letter As String
Dim tekeningnr As String
Dim omschrijving As String
Dim posnummer As String
Dim revletter As String

tekeningnr = txttekeningnummer.Value
omschrijving = txtomschrijving.Value

revletter = cmbrevisieletter.Value
posnummer = cmbposnummer.Value
letter = UCase(cmbletter.Value)

posnummer = "" 'Put the code which determines which row it should go on here.

Samenstelling (posnummer)

Unload Me

End Sub

Function Samenstelling(posnummer As Integer)

'Sheets("Artikelen_aanmaken").Activate 'No need to activate sheets, use the following instead, and call it before your range
Dim blad As Worksheet: blad = Sheets("Artikelen_aanmaken")

    blad.Range("Q" & 500 + posnummer) = cmbletter.Value 'the 500 + posnummer means it starts at row 500, plus a row higher for every position you define. Start at zero (or change this to 499 +)
    Range("N" & 500 + posnummer) = txttekeningnummer.Value
    Range("P" & 500 + posnummer) = cmbrevisieletter.Value
    Range("R" & 500 + posnummer) = txtomschrijving.Value
    Range("O" & 500 + posnummer) = cmbposnummer.Value

End Sub

edited Nov 22 '19 at 09:22

answered Nov 22 '19 at 08:46

Plutian

2,276
3
14
23

I think we are misunderstanding each other. The row number is set, for `samenstelling1` it's row 500, for `samenstelling2` its row 501 etc. What I'm struggling with is getting the data into the `samenstelling1` and pasting the values in the cell. As I said above, it's probably very simple but I can't get the values to show from the sub, I can show them from the `btnok_click`.... Do I need to call the `btnok_click`..maybe? – Renate Nov 22 '19 at 09:09
If your number is set than whichever option determines which `samenstelling` you use should go after the `posnummer` statement. If it's a selectable option on your userform for example you could do: `posnummer = right(Samenstelling.value,1)` which will trim the last character (the number) from your samenstelling value. Then you call the function and it will paste to row 500 + 1 higher for whichever `samenstelling` you currently use. Note the start value in the comment on my answer. – Plutian Nov 22 '19 at 09:26
First question the user get is "how many samenstellingen" after that, each one of those `samenstelling1`, `samenstelling2` is run untill it equals the amount they filled in the Q. If I understand you correcly, if I would put the userform in a function and I call that function, the values would be able to paste in the cell (now the cells just stay empty because the sub can't get to the values) – Renate Nov 22 '19 at 09:33
If that is the case and you need to run it multiple times, consider calling this function in a loop like `from 1 to posnummer`. If you cannot access your form values, this is easily solved by declaring them as `public` instead fo `dim`. This means they hold their value outside of the sub you call them from. Just make sure you set them to blank after use so you won't have issues with leftover data from last run. – Plutian Nov 22 '19 at 09:38
The posnummer has nothing to do with the output though, it just defines the select case that comes after. The issue was that my outputs were defines as `dim` inside of the `btnok_click` sub and they needed to be define as publics. Thank you for trying to help me though – Renate Nov 22 '19 at 09:39

JvdV · Accepted Answer · 2019-11-22T09:17:28.767

0

You are making it hard on yourself with that many different modules doing the exact same thing but just refer to another range of cells.

A Select Case or If Then statement would solve that issue. Even a .Find will get a row easy if you have identified the 16 ranges in your sheet somehow.

However, your current problem is to get the values from your UserForm. In that case you can use Public variables.

For example:

Public wrd As String

Sub Mod1()

UserForm1.Show
Debug.Print wrd

End Sub

Sub Mod2()

UserForm1.Show
Debug.Print wrd

End Sub

Sub Mod3()

UserForm1.Show
Debug.Print wrd

End Sub

Now if I would have a UserForm looking like:

The code I can put under the button would look like:

Private Sub CommandButton1_Click()

ts = Me.TextBox1.Value
Unload Me

End Sub

In your specific case it would then look like:

Public letter As String
Public tekeningnr As String
Public omschrijving As String
Public posnummer As String
Public revletter As String

Sub samenstelling1()
    Samenstelling.Show
    'Do more with the returned values from UserForm
End sub

Sub samenstelling2()
    Samenstelling.Show
    'Do more with the returned values from UserForm
End sub

Sub samenstelling3()
    Samenstelling.Show
    'Do more with the returned values from UserForm
End sub

And btnok_Click() would look like:

Private Sub btnok_Click()
    tekeningnr = txttekeningnummer.Value
    omschrijving = txtomschrijving.Value
    revletter = cmbrevisieletter.Value
    posnummer = cmbposnummer.Value
    letter = UCase(cmbletter.Value)
    Unload Me
End Sub

edited Nov 22 '19 at 09:17

answered Nov 22 '19 at 09:12

JvdV

70,606
8
39
70

I'd agree that I'm mkaing it difficult but that's because of the select case that comes after the userform and my inability to program those 16 ranges (Edited my Q to show you the select case, but that's my own problem). Form what you've shown in your comment, I believe it's the same setup except the `dim`'s are gone from the click and the `public`'s are added before the sub cases. now my issue starts from your lines `'Do more with the returned values from UserForm` because whenever I try that, the values are nowhere to be seen in that sub. If I msgbox(letter) it's just blank – Renate Nov 22 '19 at 09:28
Maybe start a new project to test what I have shown you :) and go from there. Somewhere along the line you are getting confused. The shown example works. @Renate – JvdV Nov 22 '19 at 09:33
OMG I'm so dumb, I did follow your code but forgot to comment out the `dim` in my sub I was testing. As soon as I removed it it worked. The issue was just to define the variables as `Public` Thank you again!! – Renate Nov 22 '19 at 09:38
@Renate, glad it's solved. Looking at your updated question, you definately might want to read [this](https://stackoverflow.com/q/10714251/9758194) post. It will help you out in future codings. Veel succes =) – JvdV Nov 22 '19 at 09:49
Ah yeah I see what you mean, I have tried it before but kept running into errors, I just did a small test and I see how I could implement it with resize. Fijne dag nog :) – Renate Nov 22 '19 at 10:11

Populate cells from UserForm data. Depending on the `sub`, the destination cells change

2 Answers2