UserForm textbox data to empty cells

**RLWells** · 05-28-2012, 09:28 PM

I am completely new to using VBA and need some help. I am attaching the workbook to make it easy to understand what I'm referencing. I created a userform with 2 text boxes and 3 buttons. I need the data in the boxes to populate columns B and C starting at row 6 and ending at 48. I have seen several similar questions but when I try and use the formulas provided for answers I get errors

After typing data in the 2 boxes the Ok button should input the data and close the userform. The next button should input the data and clear the userform. Any help would be appreciated!

**Mordred** · 05-28-2012, 09:59 PM

Hi RLWells, I don't know if the following workbook is quite what you're after but it may be a step in the right direction.

**RLWells** · 05-28-2012, 10:15 PM

That form works awesome until you get to the bottom. Instead of the end of form message it goes back up to the top and over writes B6 repeatedly. If there is no data in the text boxes and you hit next it clears every other row all the way down. Any help on fixing that? Thanks for all your help!

**royUK** · 05-29-2012, 02:42 AM

Try this code


Option Explicit
Dim eRng As Range
Dim eeRng As Range
Dim euRng As Range
Dim pwrWs As Worksheet
Private Sub cancelbutton_Click()
    Unload powercheck
End Sub

Private Sub CommandButton1_Click()
    Post
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
End Sub

Private Sub okbutton_Click()
    Post
    Unload Me
End Sub
Private Sub UserForm_Initialize()
    Set pwrWs = Worksheets("Power")
    Set eRng = pwrWs.Range("B6:C48")
End Sub
Sub Post()
    Set eeRng = pwrWs.Cells(49, 2).End(xlUp).Offset(1, 0)
    If eeRng.Row > 48 Then
        Select Case MsgBox("The table is full. Continue to next table?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Next table")
        Case vbYes
            Set eeRng = pwrWs.Cells(49, 6).End(xlUp).Offset(1, 0)
            If eeRng.Row > 48 Then
                MsgBox "Tables are full", vbCritical, "Quitting"
                GoTo exit_post
            End If
        Case vbNo
            GoTo exit_post
        End Select
    End If
    eeRng.Value = Me.TextBox1.Value
    eeRng.Offset(, 1).Value = Me.TextBox2.Value
exit_post:
    eeRng.Value = Me.TextBox1.Value
    Set eeRng = Nothing
End Sub