UserForm Question Need Help!
 

Hello,

PLEASE HELP! Excel 2003

After I created my userform I found some code but I can't get it to
work the way I need it to!

My user form is for one worksheet it has 3 text boxes, a OK button, and
a close button. I need the code to insert data into cells a6, b6, c6 if
there is no data already there. If data is in the cells I need it to
find the first empty cell within the range and insert data from the
userform textboxes.

The Code:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim rng2 As Range
Set rng2 = Range("A6:A20")
On Error Resume Next
Set rng = rng2.Columns(1).SpecialCells(xlConstants)
On Error GoTo 0
If Not rng Is Nothing Then
'Insert data in blank cell
Range(A6).Value = TextBox1.Value
Range(B6).Value = TextBox2.Value
Range(C6).Value = TextBox3.Value
Else

'Find empty cell within range
Set r = Worksheets("TEST - user forms").Range("A6:A20")
' change name of worksheet and range to your needs
found = False
For Each C In r
If IsEmpty(C) Then
r.Worksheet.Activate
C.Select
found = True
Exit For
End If
Next
If Not found Then MsgBox "No empty cell found within the range"

'Insert data in blank cell
ActiveCell.Offset(1, 0).Value = TextBox1.Value
ActiveCell.Offset(1, 1).Value = TextBox2.Value
ActiveCell.Offset(1, 2).Value = TextBox3.Value

End If

'Closes Userform
Unload UserForm1

End Sub

Thank you for your help,
jfcby

UserForm Question Need Help!
 

This is how I'd do it:

Private Sub CommandButton1_Click()
Dim CellsUsed As Integer
With Range("A6:A20")
CellsUsed = Application.CountA(.Cells)
If CellsUsed = .Cells.Count Then
MsgBox "Range full"
Else
With .Cells(1).Offset(CellsUsed)
.Value = TextBox1.Value
.Offset(0, 1).Value = TextBox2.Value
.Offset(0, 2).Value = TextBox3.Value
End With
End If
End With
End Sub

--
Jim
"jfcby" wrote in message
ups.com...
Hello,

PLEASE HELP! Excel 2003

After I created my userform I found some code but I can't get it to
work the way I need it to!

My user form is for one worksheet it has 3 text boxes, a OK button, and
a close button. I need the code to insert data into cells a6, b6, c6 if
there is no data already there. If data is in the cells I need it to
find the first empty cell within the range and insert data from the
userform textboxes.

The Code:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim rng2 As Range
Set rng2 = Range("A6:A20")
On Error Resume Next
Set rng = rng2.Columns(1).SpecialCells(xlConstants)
On Error GoTo 0
If Not rng Is Nothing Then
'Insert data in blank cell
Range(A6).Value = TextBox1.Value
Range(B6).Value = TextBox2.Value
Range(C6).Value = TextBox3.Value
Else

'Find empty cell within range
Set r = Worksheets("TEST - user forms").Range("A6:A20")
' change name of worksheet and range to your needs
found = False
For Each C In r
If IsEmpty(C) Then
r.Worksheet.Activate
C.Select
found = True
Exit For
End If
Next
If Not found Then MsgBox "No empty cell found within the range"

'Insert data in blank cell
ActiveCell.Offset(1, 0).Value = TextBox1.Value
ActiveCell.Offset(1, 1).Value = TextBox2.Value
ActiveCell.Offset(1, 2).Value = TextBox3.Value

End If

'Closes Userform
Unload UserForm1

End Sub

Thank you for your help,
jfcby

UserForm Question Need Help!
 

Hello Jim,

Thank you for yor help the code works great!

jfcby


Jim Rech wrote:
This is how I'd do it:

Private Sub CommandButton1_Click()
Dim CellsUsed As Integer
With Range("A6:A20")
CellsUsed = Application.CountA(.Cells)
If CellsUsed = .Cells.Count Then
MsgBox "Range full"
Else
With .Cells(1).Offset(CellsUsed)
.Value = TextBox1.Value
.Offset(0, 1).Value = TextBox2.Value
.Offset(0, 2).Value = TextBox3.Value
End With
End If
End With
End Sub

--
Jim
"jfcby" wrote in message
ups.com...
Hello,

PLEASE HELP! Excel 2003

After I created my userform I found some code but I can't get it to
work the way I need it to!

My user form is for one worksheet it has 3 text boxes, a OK button, and
a close button. I need the code to insert data into cells a6, b6, c6 if
there is no data already there. If data is in the cells I need it to
find the first empty cell within the range and insert data from the
userform textboxes.

The Code:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim rng2 As Range
Set rng2 = Range("A6:A20")
On Error Resume Next
Set rng = rng2.Columns(1).SpecialCells(xlConstants)
On Error GoTo 0
If Not rng Is Nothing Then
'Insert data in blank cell
Range(A6).Value = TextBox1.Value
Range(B6).Value = TextBox2.Value
Range(C6).Value = TextBox3.Value
Else

'Find empty cell within range
Set r = Worksheets("TEST - user forms").Range("A6:A20")
' change name of worksheet and range to your needs
found = False
For Each C In r
If IsEmpty(C) Then
r.Worksheet.Activate
C.Select
found = True
Exit For
End If
Next
If Not found Then MsgBox "No empty cell found within the range"

'Insert data in blank cell
ActiveCell.Offset(1, 0).Value = TextBox1.Value
ActiveCell.Offset(1, 1).Value = TextBox2.Value
ActiveCell.Offset(1, 2).Value = TextBox3.Value

End If

'Closes Userform
Unload UserForm1

End Sub

Thank you for your help,
jfcby