STILL NEED HELP! Userform Error

ROBINSYN

Board Regular
Joined
Aug 19, 2002
Messages
188
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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

Board Regular
Joined
Aug 19, 2002
Messages
188
Almost had it but throws info into wrong cells. Starts a K instead of C.
 

Forum statistics

Threads
1,144,120
Messages
5,722,596
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top