VBA - Adding dynamic named range to combobox error and Manipulating ranges without interacting with the sheet

[Feersum Endjinn]

Dear all,

first post here, but I must say this has been extemely useful in helping me with any problems I've had.

I've been teaching myself VBA for the past couple of months and have become more and more sophisticated with the solutions I use.

One particular problem I have is this:

I had previously been using data validation in a cell to present a dynamic named range. This list needed to be in alphabetical order so I had incorporated code into a macro to move the list of projects into a new column (hidden) and then sort this list alphabetically.

What I dislike is that when the user runs the macro, they see work being performed, the cells being selected etc etc, albeit very quickly! I find this looks a bit clumsy..

I've since chosen to use VBA to define the range and then add the list to a combobox in a userform. Before it's added to the combobox, the range is sorted. However, naturally this still sorts the project lists on the worksheet. The list in their original position have to stay in their order, so I would still need to add code to move this list to a free column..

Basically what I'm asking is, is it possible to manipulate ranges 'within the code' without interacting with the spreadsheet. I have a feeling that I would have to use arrays, which I'm not yet familiar with in Excel, however I have read that sorting arrays isn't possible in excel?

Secondly, I'm never able to add a dynamic named range to a userform. It simply gives me an error on userform startup. In the past I've had to manually add each project from the sheet for as many rows as their are projects..

Here is the code from the userform..

Private Sub UserForm_Initialize()
' Executed before the form is shown
Dim Project As Range

ActiveWorkbook.Names.Add Name:="List", RefersTo:="=OFFSET(CostMacro!$A$2,0,0,(COUNTA(CostMacro!$A:$A)),1)"
Range("List").Sort key1:=Range("List").Cells(1), _
Order1:=xlAscending, Header:=xlGuess, Ordercustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

With Me.ProjectListBox

For Each Project In List

ProjectListBox.AddItem Project.Value

Next Project
End With

ProjectListBox.ListIndex = 0
CurrentTopic = 0

End Sub

Thanks,

 

[Feersum Endjinn]

Ok, I've fixed the problem with the adding the named range:

Private Sub Userform_Initialize()

' Executed before the form is shown
Dim Project As Range

ActiveWorkbook.Names.Add Name:="List", RefersTo:="=OFFSET(CostMacro!$C$2,0,0,(COUNTA(CostMacro!$C:$C)-1),1)"

Range("List").Sort key1:=Range("List").Cells(1), _
Order1:=xlAscending, Header:=xlGuess, Ordercustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

With Me.ProjectListBox

For Each Project In Range("List")

ProjectListBox.AddItem Project.Value

Next Project
End With

ProjectListBox.ListIndex = 0
CurrentTopic = 0

End Sub

Comments on the other issue would be appreciated!

 

[RoryA]

You can sort arrays in VBA, it's just not a built-in function of the arrays, so you have to code an algorithm to do it (of which there are many). If you want to use a worksheet for the manipulation there is no need to select anything and you can turn off screen updating so the user won't see anything happen.

 

[Feersum Endjinn]

Thanks rorya,

Application.ScreenUpdating = False

For anything reading this thread for their own info and having to then search for how to turn off screen updating...