Userform to add data to a specified range

[scwisely]

Hey All, Need help please - I have created a userform and need help with the VBA code to add the data to cells as described below:

textbox1 to cell AZ160 (and below)
textbox2 to cell BB160 (and below)

Also, I need a formula to auto-populate in BA160 (Formula = =$E$4&""&AZ160)

My sheet name is "Sheet13" ; however, that name changes based on cell reference to E4 -

I have the form completed just need the VBA code for the CommandButton1 to save the data into the row and subsequent rows

Thank You!
Scott

 

[My Aswer Is This]

I assume the sheet name will be in Range("E4") of the active sheet where this script is run from.
You said sheet name will be in Range("E4") but did not give the sheet name. So I assume its the active sheet.

Try this:

Private Sub CommandButton1_Click()
'Modified  6/22/2019  2:19:53 AM  EDT
On Error GoTo M
Dim SN As String
SN = Range("E4").Value
Dim LastrowAZ As Long
LastrowAZ = Sheets(SN).Cells(Rows.Count, "AZ").End(xlUp).Row + 1
If LastrowAZ < 160 Then LastrowAZ = 160
Dim LastrowBB As Long
LastrowBB = Sheets(SN).Cells(Rows.Count, "BB").End(xlUp).Row + 1
If LastrowBB < 160 Then LastrowBB = 160
Sheets(SN).Cells(LastrowAZ, "AZ").Value = TextBox1.Value
Sheets(SN).Cells(LastrowBB, "BB").Value = TextBox2.Value
With Sheets(SN)
.Range("BA160").Value = ActiveSheet.Range("E4").Value & .Range("AZ160").Value
End With
Exit Sub
M:
 MsgBox "The sheet name in Range(""E4"") of the active sheet does not exist"
End Sub

Private Sub CommandButton1_Click()
'Modified  6/22/2019  2:19:53 AM  EDT
Dim SN As String
SN = Range("E4").Value
Dim LastrowAZ As Long
LastrowAZ = Sheets(SN).Cells(Rows.Count, "AZ").End(xlUp).Row + 1
If LastrowAZ < 160 Then LastrowAZ = 160
Dim LastrowBB As Long
LastrowBB = Sheets(SN).Cells(Rows.Count, "BB").End(xlUp).Row + 1
If LastrowBB < 160 Then LastrowBB = 160
Sheets(SN).Cells(LastrowAZ, "AZ").Value = TextBox1.Value
Sheets(SN).Cells(LastrowBB, "BB").Value = TextBox2.Value
With Sheets(SN)
.Range("BA160").Value = ActiveSheet.Range("E4").Value & .Range("AZ160").Value
End With
End Sub

 

[scwisely]

This worked very well and THANK YOU! However, when a column is inserted or deleted within the sheet it does not save correctly and is out of alignment. Also, is there a way to have the form clear upon clicking the command button so we can enter another line of data?

Thank You so much!
Scott

I assume the sheet name will be in Range("E4") of the active sheet where this script is run from.
You said sheet name will be in Range("E4") but did not give the sheet name. So I assume its the active sheet.

Try this:

Private Sub CommandButton1_Click()
'Modified  6/22/2019  2:19:53 AM  EDT
On Error GoTo M
Dim SN As String
SN = Range("E4").Value
Dim LastrowAZ As Long
LastrowAZ = Sheets(SN).Cells(Rows.Count, "AZ").End(xlUp).Row + 1
If LastrowAZ < 160 Then LastrowAZ = 160
Dim LastrowBB As Long
LastrowBB = Sheets(SN).Cells(Rows.Count, "BB").End(xlUp).Row + 1
If LastrowBB < 160 Then LastrowBB = 160
Sheets(SN).Cells(LastrowAZ, "AZ").Value = TextBox1.Value
Sheets(SN).Cells(LastrowBB, "BB").Value = TextBox2.Value
With Sheets(SN)
.Range("BA160").Value = ActiveSheet.Range("E4").Value & .Range("AZ160").Value
End With
Exit Sub
M:
 MsgBox "The sheet name in Range(""E4"") of the active sheet does not exist"
End Sub

Private Sub CommandButton1_Click()
'Modified  6/22/2019  2:19:53 AM  EDT
Dim SN As String
SN = Range("E4").Value
Dim LastrowAZ As Long
LastrowAZ = Sheets(SN).Cells(Rows.Count, "AZ").End(xlUp).Row + 1
If LastrowAZ < 160 Then LastrowAZ = 160
Dim LastrowBB As Long
LastrowBB = Sheets(SN).Cells(Rows.Count, "BB").End(xlUp).Row + 1
If LastrowBB < 160 Then LastrowBB = 160
Sheets(SN).Cells(LastrowAZ, "AZ").Value = TextBox1.Value
Sheets(SN).Cells(LastrowBB, "BB").Value = TextBox2.Value
With Sheets(SN)
.Range("BA160").Value = ActiveSheet.Range("E4").Value & .Range("AZ160").Value
End With
End Sub

 

[My Aswer Is This]

If you go into your sheet and you did not say what sheet and start deleting rows and columns their is no way for the script to automatically run and reenter every thing on the sheet as far as I know.

 

[My Aswer Is This]

Your original post said:

textbox1 to cell AZ160 (and below)
textbox2 to cell BB160 (and below)

If you delete columns or rows or what ever.
The script will always put the Textbox values in Columns AZ and BB
​

 

[scwisely]

What if I used a named range, would that solve the issue? The challenge is that columns will be added or deleted, thereby shifting the column names. If a named range would work, what would the VBA code look like? Thank You so much for any input and your time!!!

 

[My Aswer Is This]

There are several questions I have.

To know what sheet we will be entering this data in we have to look in Range("E4") of some sheet.
You never mentioned that sheet name.

And are columns being deleted from that sheet?

And I assume one day you may be entering Alpha into Range("E4") so now we will be dealing with sheet named Alpha.

But the next time you may enter Bravo into Range("E4") so now we are dealing with sheet named Bravo.

So that would mean we may be dealing with a large number of different sheets and you may be deleting columns and maybe even rows from these sheet.

I'm not sure why Your using a UserForm to do all this.

And since you are why not enter the sheet name into a Userform Textbox or select the sheet name from a UserForm ListBox of sheet names.

It appears as if your using a UserForm just so you can enter two values into a sheet.

It might be easier to help if you would explain the ultimate Goal you want to achieve here.

If you had a named Range and deleted the column the named range refers to then we would still have the same problem.

Can you not put the column number into a TextBox on the userform and we tell the script to refer to that column.

 

[scwisely]

Thank you so very much for your time and patience with me - I am very new to VBA and excel. What we are trying to do is to be able to easily add new items to a list, if they don't exist in a data validation list. Once you select an item from the list, I use a VLookup to populate the cell in row 14 with a definition from column BB. I've included a screen shot as well. Ultimately, each sheet may have columns deleted or inserted based on the number of items needed. We may have multiple sheets with the name of the sheet based on cell E4 of each sheet - I am happy to send the file to you via email.

 

[scwisely]

https://app.box.com/s/0x31vzlld3w78ixac0cc5esu6gdxm8fl

 

[scwisely]

https://app.box.com/s/wu614w71fui37vkad05bgm4jg0z9z4i9