Userform and Textboxes to update fields on form

[Noir]

I have a sheet with 55 columns of employee data with column headers such as Last Name, First Name, MI, Phone#, Office#,etc. My job is to update this document daily. Even with freezing panes and splitting the screen, it is awkward to update all 55 fields. Not only do i have modifications to existing data but, new employee adds as well. I basically am scrolling my mouse to death.

What i did was, create 2 Userforms with 55 Textboxes on each. Userform1 is used to view and edit "existing" data. I do this using code the following code;

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Range("a24").Select
Do Until ActiveCell.Text = ComboBox1.Text Or ActiveCell.Value = vbNullString
ActiveCell.Offset(1, 0).Select
Loop
TextBox1.Value = ActiveCell.Offset(0, 1).Value
TextBox2.Value = ActiveCell.Offset(0, 2).Value
TextBox3.Value = ActiveCell.Offset(0, 3).Value
TextBox4.Value = ActiveCell.Offset(0, 4).Value
TextBox5.Value = ActiveCell.Offset(0, 5).Value
TextBox6.Value = ActiveCell.Offset(0, 6).Value
TextBox7.Value = ActiveCell.Offset(0, 7).Value
TextBox8.Value = ActiveCell.Offset(0, 8).Value
TextBox9.Value = ActiveCell.Offset(0, 9).Value
TextBox10.Value = ActiveCell.Offset(0, 10).Value
TextBox11.Value = ActiveCell.Offset(0, 11).Value
TextBox12.Value = ActiveCell.Offset(0, 12).Value
TextBox13.Value = ActiveCell.Offset(0, 13).Value
TextBox14.Value = ActiveCell.Offset(0, 14).Value
TextBox15.Value = ActiveCell.Offset(0, 15).Value
TextBox16.Value = ActiveCell.Offset(0, 16).Value
TextBox17.Value = ActiveCell.Offset(0, 17).Value
TextBox18.Value = ActiveCell.Offset(0, 18).Value
TextBox19.Value = ActiveCell.Offset(0, 19).Value
TextBox20.Value = ActiveCell.Offset(0, 20).Value
TextBox21.Value = ActiveCell.Offset(0, 21).Value
TextBox22.Value = ActiveCell.Offset(0, 22).Value
TextBox23.Value = ActiveCell.Offset(0, 23).Value
TextBox24.Value = ActiveCell.Offset(0, 24).Value
TextBox25.Value = ActiveCell.Offset(0, 25).Value
TextBox26.Value = ActiveCell.Offset(0, 26).Value
TextBox27.Value = ActiveCell.Offset(0, 27).Value
TextBox28.Value = ActiveCell.Offset(0, 28).Value
TextBox29.Value = ActiveCell.Offset(0, 29).Value
TextBox30.Value = ActiveCell.Offset(0, 30).Value
TextBox31.Value = ActiveCell.Offset(0, 31).Value
TextBox32.Value = ActiveCell.Offset(0, 32).Value
TextBox33.Value = ActiveCell.Offset(0, 33).Value
TextBox34.Value = ActiveCell.Offset(0, 34).Value
TextBox35.Value = ActiveCell.Offset(0, 35).Value
TextBox36.Value = ActiveCell.Offset(0, 36).Value
TextBox37.Value = ActiveCell.Offset(0, 37).Value
TextBox38.Value = ActiveCell.Offset(0, 38).Value
TextBox39.Value = ActiveCell.Offset(0, 39).Value
TextBox40.Value = ActiveCell.Offset(0, 40).Value
TextBox41.Value = ActiveCell.Offset(0, 41).Value
TextBox42.Value = ActiveCell.Offset(0, 42).Value
TextBox43.Value = ActiveCell.Offset(0, 43).Value
TextBox44.Value = ActiveCell.Offset(0, 44).Value
TextBox45.Value = ActiveCell.Offset(0, 45).Value
TextBox46.Value = ActiveCell.Offset(0, 46).Value
TextBox47.Value = ActiveCell.Offset(0, 47).Value
TextBox48.Value = ActiveCell.Offset(0, 48).Value
TextBox49.Value = ActiveCell.Offset(0, 49).Value
TextBox50.Value = ActiveCell.Offset(0, 50).Value
TextBox51.Value = ActiveCell.Offset(0, 51).Value
TextBox52.Value = ActiveCell.Offset(0, 52).Value
TextBox53.Value = ActiveCell.Offset(0, 53).Value
TextBox54.Value = ActiveCell.Offset(0, 54).Value
TextBox55.Value = ActiveCell.Offset(0, 55).Value
Application.ScreenUpdating = True
End Sub




**and this for the Textboxes**



Private Sub TextBox1_Change()
ActiveCell.Offset(0, 1).Value = TextBox1.Value
End Sub

The Userform1's process works perfectlly.

Now Userform2, i would like to use for adding "new" employee data. Here is where i need your help.

I also placed 55 textboxes on Userform2. What i want is a way for the textbox code to find the next available cell in "ColumnA". Once that cell has been established, all 55 "new data entries" can be made along that same row (ie:if there is existing data in row 30 from A30:BD30, the textbox code looks for and finds that cell A31 is empty. Therefore, Textbox1's entry will go into A31. Textbox2 into B31, Textbox3 intoC31, etc. *Important* i want the code to look for an empty cell in columnA only. If A31 is empty, make your new entry in that same row. While any other column may have an empty cell within the 55 columns, columnA is never empty beause it is the "Last Name" column. (unless of course the entire row is empty)

Thx,
Noir

 

[DRJ]

If you are looking for the next blank row try this

LastRow = Range("A65536").end(xlup).row + 1

This will be the next available blank row

The range of this cell would then be range("A" & LastRow)

 

[DRJ]

Also we can clean up this code a bit

For x = 1 to 55
controls("TextBox" & x).value = activecell.offset(0,x).value
next x

Replace all your textbox.value = with that loop

 

[Noir]

Thanks DRJ!!

Noir

 

[Noir]

DRJ,
Your revised code for the 55 textboxes worked great, it really consolidated the functions nicely.

Unfortunately, i cannot get the Last Row code to work properly. It seems to overwrite the data on the last row, never moving on to the next available row.

Please advise.

Thx,
Noir

 

[JamieP]

This has worked for me

Range("B65536").End(xlUp).Offset(1, 0)

 

[Noir]

Thanks for the suggestion JamieP but, it does the same thing. When i open the userform and use the textbox to enter data, it initially finds the next available row (eg:183). However, when i try to enter a new entry in a new row(after closing then re-openeing my userform) the textbox writes the data in the same row again (183).

I know it is probably something very simple but, i can't seem to figure this one out.

Noir

 

[Noir]

Can anyone figure out what i am doing wrong???

Noir