excelvbarangeuserform
Populate cells from UserForm data. Depending on the `sub`, the destination cells change
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
Solution
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
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port