creating a simple userform

[CameronGates]

Remember I'm a newb so if I sound ignorant it's because I am, lol. Ok so I'm creating a simple user form that will be using a bunch of text boxes to input data into specified cells. So the code for each one of the text boxes look like this:

Private Sub TextBox1_Change()
Sheet3.Range("b1") = TextBox1.Value
End Sub

There are something like 60 text boxes all with the same code but specifying different cells and text boxes. here is the stupid question, how do I create the command click ok button to actually make it happen. Lol, sorry guys, i'm really new at this. Thanks for any help you guys can give.

 

[Logit]

.


To continue in the present method :

Sub EnterMyData()


        With ActiveSheet  'must be applied to the same sheet and that sheet must be visible
            .Range("B1").Value = TextBox1.Text
            .Range("D22").Value = TextBox2.Text
            
            '.etc
            '.etc
            '.etc
        
        End With
        
End Sub

If the data from the textboxes will all be applied to the same row ... consider this approach :

The following is pasted in the UserForm code window :

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub


Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value       'data entered into new empty row, column A
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value         'data entered into same empty row, column B
    
    'etc
    'etc
    
End Sub


Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/OU69OrQA9KAi3ahKzTut4ycz7r7XySTGgk8XCXeVi7U

 

[CameronGates]

Hey Logit whats up man? You are always coming to the rescue and I appreciate it greatly! Unfortunately this is to enter data all over a worksheet so it looks like I have a lot of typing to do. Do I need to add the lines Dim ws As Worksheet and Set ws = ActiveSheet?

 

[Logit]

.
This method should work without all the dim statements:

Sub EnterMyData()




        With ActiveSheet  'must be applied to the same sheet and that sheet must be visible
            .Range("B1").Value = TextBox1.Text
            .Range("D22").Value = TextBox2.Text
            
            '.etc
            '.etc
            '.etc
        
        End With
        
End Sub

If it doesn't, let me know and we'll dig deeper.

 

[CameronGates]

So do I need to make that a command button?

 

[Logit]

.
Yup ... create a command button and connect it to that macro.

 

[My Aswer Is This]

If I understand your request correctly.

You have a large number of textboxes on a Userform and you want a script that will loop through all the Textboxes on the Userform and send the values from the userform to the same sheet.

Is there some sort of trend to this?

Like Textbox1 value goes to Range("A1") and Textbox2 value goes to Range("A2")

If there is a trend or some logic we can reduce all the code you might need.

If there is not trend or logic can you make one?

 

[CameronGates]

If I understand your request correctly.

You have a large number of textboxes on a Userform and you want a script that will loop through all the Textboxes on the Userform and send the values from the userform to the same sheet.

Is there some sort of trend to this?

Like Textbox1 value goes to Range("A1") and Textbox2 value goes to Range("A2")

If there is a trend or some logic we can reduce all the code you might need.

If there is not trend or logic can you make one?

Well here are the ranges

.Range("b1").Value = TextBox1.Text
.Range("e1").Value = TextBox11.Text
.Range("h1").Value = TextBox12.Text
.Range("e6").Value = TextBox22.Text
.Range("e7").Value = TextBox23.Text
.Range("e8").Value = TextBox24.Text
.Range("e9").Value = TextBox25.Text
.Range("e10").Value = TextBox26.Text
.Range("e11").Value = TextBox27.Text
.Range("b13").Value = TextBox28.Text
.Range("d13").Value = TextBox29.Text
.Range("e13").Value = TextBox30.Text
.Range("h13").Value = TextBox31.Text
.Range("h15").Value = TextBox32.Text
.Range("e18").Value = TextBox22.Text
.Range("e19").Value = TextBox34.Text
.Range("e20").Value = TextBox35.Text
.Range("e21").Value = TextBox36.Text
.Range("e22").Value = TextBox37.Text
.Range("e23").Value = TextBox38.Text
.Range("b25").Value = TextBox39.Text
.Range("d25").Value = TextBox40.Text
.Range("e25").Value = TextBox41.Text
.Range("h25").Value = TextBox42.Text
.Range("e30").Value = TextBox44.Text
.Range("e31").Value = TextBox45.Text
.Range("e32").Value = TextBox46.Text
.Range("e33").Value = TextBox47.Text
.Range("e34").Value = TextBox48.Text
.Range("e35").Value = TextBox49.Text
.Range("b37").Value = TextBox50.Text
.Range("d37").Value = TextBox51.Text
.Range("e37").Value = TextBox52.Text
.Range("h37").Value = TextBox53.Text
.Range("h39").Value = TextBox54.Text
.Range("e42").Value = TextBox55.Text
.Range("e43").Value = TextBox56.Text
.Range("e44").Value = TextBox57.Text
.Range("e45").Value = TextBox58.Text
.Range("e46").Value = TextBox59.Text
.Range("d1").Value = TextBox6.Text
.Range("e47").Value = TextBox60.Text
.Range("g1").Value = TextBox61.Text
.Range("g13").Value = TextBox62.Text
.Range("g25").Value = TextBox63.Text
.Range("g37").Value = TextBox64.Text

It's working good right now though! Thank you! now for the next form on the next sheet, might be a bit easier? but I think it's going to need some data validation.

 

[My Aswer Is This]

Just a quick look at this shows no logic or trend. So I see not easier way to loop through these.

In your next project if you were to use some logic we could write a short little script that would loop through the Textboxes

Like Textbox 10 value goes to Range("B10") and Textbox 11 values goes to Range("B11")

Or Textbox 10 value goes to column 10 and so on.

Are you saying you plan to make a separate Userform for a different sheet?

You said:

for the next form on the next sheet

Does this mean you think you need a separate Userform for each sheet?
Why do you think you need that?


One Userform can do a lot of different things with no need for more then one Userform.​

 

[Fluff]

You could use an array & loop through that like

   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array(1, "B1", 11, "E1", 12, "H1", 22, "E6", 23, "E7", 24, "E8")
   For i = 0 To UBound(Ary) Step 2
      Range(Ary(i + 1)).Value = Me.Controls("TextBox" & Ary(i))
   Next i