Problems with autosort while userform is visible.
Posted by Beginner Bob on February 26, 2001 3:07 PM
Yet another problem...
I have a userform for entering data pertaining to vendors and jobs. Next to the vendor and job combo boxes I have buttons for adding new vendors/jobs. The vendors are in column A, and the jobs are in column B on the "LISTS" sheet, and are autosorted with the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS1 As Worksheet, LASTVEN As Range, BEG As Range, BGL As Range
Set WS1 = Sheets("LISTS")
For DOA = 1 To 2
On Error Resume Next
Set LASTVEN = WS1.Cells(65536, DOA).End(xlUp)
Set BEG = WS1.Cells(1, DOA)
Selection.Sort Key1:=WS1.Cells(1, DOA), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Set BGL = WS1.Cells(65536, DOA).End(xlUp)
If Not IsEmpty(Cells(2, DOA)) Then
If DOA = 1 Then WS1.Range("A2", BGL).Name = "VNLIST"
If DOA = 2 Then WS1.Range("B2", BGL).Name = "JLIST"
The problem is that when the vendor/job is entered into the inputbox as called by the button, the "LISTS" sheet does not sort. It works fine for entering the vendors/jobs right on the sheet, but not through the userform/inputbox. Can't figure this one out. I appreciate the help. Thanks.