Excel VBA; UserForm, Run one piece of code on different TextBox values

Question

UPD:

Some information regarding my real case. Here's the UserForm I'm using:

In my Userforms, Duration/CED textboxes are named as Dur1-Dur6, and S/C Frequency textboxes are named as sc1-sc6.

The main purpose of the macro is to copy a table template and paste it alongside the main table with required formulas, based on S/C Frequency, like this. Table

The full code I have:

Private Sub OkButton_Click()

TheStart:
Dim FirstRow2 As Integer: FirstRow2 = 18 'set the number value of first row with formulas
Dim LastRow2 As Integer: LastRow2 = Range("L1000").End(xlUp).Row
Dim AQCol As Integer: AQCol = 11 'set the number value of AQ column in the Main Table (to calculate relative reference for formulas)

If Supplier_Data.SuppName = "" Then
MsgBox "Please enter supplier's name"
Exit Sub
End If

Dim LastCol1 As Integer: LastCol1 = Range("IV18").End(xlToLeft).Column 

If Supplier_Data.Dur1 = "" Or Supplier_Data.sc1 = "" Then
MsgBox "Please enter at least one duration and s/c frequency"
Exit Sub

'copy TEST table
ElseIf Supplier_Data.Dur1 = "TEST" Then
Sheet5.Range("A7:C10").Copy
Sheet6.Cells(15, LastCol1 + 1).PasteSpecial (xlPasteAll)
Sheet6.Cells(15, LastCol1 + 1).Value = Supplier_Data.SuppName.Value & " " & Supplier_Data.Dur1.Value & " " & "offer"
Sheet6.Cells(17, LastCol1 + 1).Value = Supplier_Data.sc1.Value

Else
Sheet5.Range("A2:C5").Copy
Sheet6.Cells(15, LastCol1 + 1).PasteSpecial (xlPasteAll)
Sheet6.Cells(15, LastCol1 + 1).Value = Supplier_Data.SuppName.Value & " " & Supplier_Data.Dur1.Value & " " & "offer"
Sheet6.Cells(17, LastCol1 + 1).Value = Supplier_Data.sc1.Value

End If

'Calculate AS for each line
For i = FirstRow2 To LastRow2 - 1

If Supplier_Data.sc1.Value = "ppd" Then
ASFormula = "= (r[0]c[-2] * 365/100) + (r[0]c[" & AQCol - (LastCol1 + 3) & "] * r[0]c[-1])/100"
ElseIf Supplier_Data.sc1.Value = "PD" Then
ASFormula = "= (r[0]c[-2] * 365) + (r[0]c[" & AQCol - (LastCol1 + 3) & "] * r[0]c[-1])/100"
ElseIf Supplier_Data.sc1.Value = "PM" Then
ASFormula = "= (r[0]c[-2] * 12) + (r[0]c[" & AQCol - (LastCol1 + 3) & "] * r[0]c[-1])/100"
ElseIf Supplier_Data.sc1.Value = "PQ" Then
ASFormula = "= (r[0]c[-2] * 4) + (r[0]c[" & AQCol - (LastCol1 + 3) & "] * r[0]c[-1])/100"
End If

Sheet6.Cells(i, LastCol1 + 3).FormulaR1C1 = ASFormula
Sheet6.Range(Cells(FirstRow2, LastCol1 + 1), Cells(FirstRow2, LastCol1 + 3)).Copy
Sheet6.Range(Cells(i, LastCol1 + 1), Cells(i, LastCol1 + 3)).PasteSpecial (xlPasteFormats)

Next i

'Total Estimated AS
Sheet6.Cells(LastRow2, LastCol1 + 3).FormulaR1C1 = "=SUM(r" & FirstRow2 & "c" & LastCol1 + 3 & ":r" & LastRow2 - 1 & "c" & LastCol1 + 3 & " )"
Sheet6.Range(Cells(LastRow2, LastCol1 + 1), Cells(LastRow2, LastCol1 + 3)).Borders.LineStyle = xlContinuous

Sheet6.Range(Cells(LastRow2, LastCol1 + 1), Cells(LastRow2, LastCol1 + 3)).Font.Bold = True


Supplier_Data.Hide

End Sub

So, in order not to have the same piece of code for all Durations, I am looking for a way to run code, starting from Dim LastCol1 As Integer: LastCol1 = Range("IV18").End(xlToLeft).Column (so that Macro Generated Table 2 will be near Macro Generated Table 1, not overwriting it), for each Duration/CED textbox filled.

If anyone could suggest the solution, I'd really appreciate it!

Dharman · Accepted Answer · 2023-01-13 21:15:58Z

0

How about this?

Private Sub UserForm_Initialize()
Dim c As Control
Dim cnt As Integer: cnt = 1

For Each c In userform1.Controls
    If TypeName(c) = "TextBox" Then
        Cells(cnt, 1).Value = c.Value * 2 * 3
        cnt = cnt + 1
    End If
Next c
End Sub

The code I provided above is inside the userform_initialize event, but You should be able to take it out of the event and have it run just fine (you may have to modify certain parts to fit your situation though, ex. if you want the values to go to a different destination); basically what it will do is loop through all of the controls in the userform, and if the control is a text box it will put that value in the corresponding cell (which is what your question was originally asking to do).

edited Jan 13, 2023 at 21:15

Dharman♦

34k27 gold badges106 silver badges158 bronze badges

answered Jan 18, 2015 at 3:13

John Smith

7,4477 gold badges52 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA; UserForm, Run one piece of code on different TextBox values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related