New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

fill combobox Descending Order

Macros, VBA, Excel Automation, etc.

fill combobox Descending Order

Postby Tony Sondergeld » Fri Apr 08, 2016 8:46 pm

I have some code does work and on a selection with combo-box 1 will place the range into Combo box 2 in the reverse order (Largest to smallest) However this is on a user form which is displayed vbModeless once it finishes the sub sit there and does nothing which stop user from entry to the worksheet and or code running other that selecting combo box 1. The green run button isn't coming back up on visual basic either and it say it RUNNING. You can pause it or stop the sub running with visual Basic . I've try put a IF statement option in a couple different place within the code but either it stop in early and doesn't finish the sort or doesn't do anything. Is there a way of getting it to finish the sort and return the worksheet to user control
Code: Select all
Private Sub ComboBox1_Change()
Dim vItems      As Variant
Dim vTemp       As Variant
Dim i           As Long
Dim j           As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
With ComboBox2
.Clear
.List = ws.Range("Product1").Value
End With

vItems = Me.ComboBox2.List
   
    For i = LBound(vItems) To UBound(vItems) - 1
        For j = i + 1 To UBound(vItems)
            If vItems(i, 0) < vItems(j, 0) Then
                vTemp = vItems(i, 0)
                vItems(i, 0) = vItems(j, 0)
                vItems(j, 0) = vTemp
                End If
        Next j
    Next i
    Me.ComboBox2.List = vItems
End Sub


I also Try this which is almost the same code both do the same thing.
Code: Select all
Dim Ray, i As Integer, j As Integer, temp As String
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

With ComboBox2
.Clear
.List = ws.Range("Product1").Value
End With
With ComboBox2
Ray = Application.Transpose(.List)
    For i = 1 To UBound(Ray) - 1
        For j = i To UBound(Ray)
       
            If Ray(j) > Ray(i) Then
                temp = Ray(i)
                Ray(i) = Ray(j)
                Ray(j) = temp
            End If
        Next j
    Next i
.List = Ray
End With
  • 0

Tony Sondergeld
Rookie
 
Posts: 1
Joined: Apr 8, 2016
Reputation: 0
Excel Version: 2007, 2010, 360

Re: fill combobox Descending Order

Postby Sisyphus » Sun Apr 10, 2016 3:12 am

Just as a quick guess, if you will pardon me, I would think that the modeless form can't be altered after control has been returned to the worksheet. Perhaps the reason for partial execution lies in there not being any control over the precise moment when control changes hands. It might change in the middle of execution. I wouldn't try to argue (with either Excel or VBA) over this point.
Therefore I suggest to do something to the target form to make it modal, finish my changes, and put it back up where it was. If you use Application.ScreenUpdating = False / True the user wouldn't see any of that. I think you can't change the Mode property at runtime. So, the way to go would be to unload the form and create a new instance of it which incorporates the changed combobox. You can load the combobox before the Show command.
As an alternative, check if you can just hide the form, make the changes, and show it again. Perhaps, since Mode is a property of the Show method, that doesn't count as changing the Mode at runtime.
Hope this helps.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 64 guests