STILL NEED HELP! Userform Error

[ROBINSYN]

Cannot get this to work. I have a button on Sheet 1 with the macro Userform1.Show

Enter data that should transfet to Sheet "Vendors"

Once user clicks Add Vendor, the Sheet is suppose to unhide, add the vendor info,sort then hide and user will return to Sheet1.

Also Entry should start in Cell C4. Is there a way Userform can serach for next empty cell so vendors I already have are not overridden?

Can you help see what i did wrong?



Private Sub CommandButton1_Click()

CurrentsheetName = ActiveSheet.Name
Application.ScreenUpdating = False
Sheets("Sheet1").Select 'THIS IS MY PROBLEM ERROR
Range(Cells(65000, 10), Cells(65000, 10)).End(xlUp).Select
ActiveCell.Offset(1, 0).Select

ActiveCell.Value = UserForm1.TextBox1.Value
ActiveCell.Value = UserForm1.TextBox2.Value
ActiveCell.Value = UserForm1.TextBox3.Value
ActiveCell.Value = UserForm1.TextBox4.Value
ActiveCell.Value = UserForm1.TextBox5.Value
ActiveCell.Value = UserForm1.TextBox6.Value

ActiveCell.Offset(0, 1).Value = UserForm1.TextBox1.Value
ActiveCell.Offset(0, 2).Value = UserForm1.TextBox2.Value
ActiveCell.Offset(0, 3).Value = UserForm1.TextBox3.Value
ActiveCell.Offset(0, 4).Value = UserForm1.TextBox4.Value
ActiveCell.Offset(0, 5).Value = UserForm1.TextBox5.Value
ActiveCell.Offset(0, 6).Value = UserForm1.TextBox6.Value

UserForm1.TextBox1.Value = "C4"
UserForm1.TextBox2.Value = "D4"
UserForm1.TextBox3.Value = "E4"
UserForm1.TextBox4.Value = "F4"
UserForm1.TextBox5.Value = "L4"
UserForm1.TextBox6.Value = "M4"
Unload UserForm1
Application.ScreenUpdating = True

Worksheets("Vendors").Select
Range("C4:M65536").Select
Selection.sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C2").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Purchase Order").Select



End Sub
This message was edited by ROBINSYN on 2002-10-07 07:13

 

[stevebausch]

As far as "next empty cell" is concerned, the classic code for that involves:


Range(Cells(65000,10),Cells(65000,10)).End(xlup).[Row|Select]

Take your pick, either .Row, or .Select will help you find the "next empty cell".

Personally, I find this simple technique to be unreliable; I have a loop that Trim()s any potential 'end' cell, and continues on until the cell is truly occupied with visible entry.


At this moment, I can't find the snippet to offer you. Sorry.

Found it. Here it is:

Range("A160").Select

ActiveCell.End(xlUp).Select

Do While Len(Trim(ActiveCell.Value)) = 0

ActiveCell.End(xlUp).Select


Loop
This message was edited by SteveBausch on 2002-10-07 01:19

 

[ROBINSYN]

Almost had it but throws info into wrong cells. Starts a K instead of C.