Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Userform question. | Excel Discussion (Misc queries) | |||
userform question | Excel Programming | |||
userform question | Excel Programming | |||
Userform Question - is this possible? | Excel Programming | |||
userform question | Excel Programming |