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)
Range(BEG, LASTVEN).Select
Selection.Sort Key1:=WS1.Cells(1, DOA), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
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"
End If
Next
End Sub
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.
Posted by David Hawley on February 26, 2001 4:57 PM
Hi Bob
The reason is most likely due to the Control (CommandButton ?) on the form that is triggering the Sort.They by default retain Focus after being clicked. There a few ways to deal with this.
1. Set the TakeFocusOnClick to False. In the Properties Window.
2. Use:
Private Sub CommandButton1_Click()
CommandButton1.TakeFocusOnClick = False
'Rest of your code
End Sub
3. Use
Private Sub CommandButton1_Click()
Range("A1").Select
End Sub
Regarding you code if the Sort is being done on the sheet that is housing the code then you could shorten it to:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LASTVEN As Range, BEG As Range, BGL As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For DOA = 1 To 2
On Error Resume Next
Set LASTVEN = Cells(65536, DOA).End(xlUp)
Set BEG = Cells(1, DOA)
Range(BEG, LASTVEN).Sort Key1:=Cells(1, DOA), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Set BGL = Cells(65536, DOA).End(xlUp)
If Not IsEmpty(Cells(2, DOA)) Then
If DOA = 1 Then Range("A2", BGL).Name = "VNLIST"
If DOA = 2 Then Range("B2", BGL).Name = "JLIST"
End If
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Notice the use of:
Application.ScreenUpdating Application.EnableEvents
The ScreenUpdating: will speed things up.
The EnableEvents: will prevent your code running again when if a cell is changed during execution of your code.
Dave
OzGrid Business Applications
Posted by Beginner Bob on February 28, 2001 3:59 PM
That's perfect, thanks again Dave.
