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

Feersum Endjinn

New Member
Joined
Jun 18, 2012
Messages
3
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,
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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!
 
Upvote 0
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.
 
Upvote 0
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...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,211,710
Messages
6,103,451
Members
447,866
Latest member
bowers261

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
Back
Top