Combobox lists from public range variable

[Prevost]

Hi Everybody. I am trying to call a public range to use as the data source for my combobox drop down list, however I continue to receive errors. I found some code that worked by declaring the range variable within the UserFrom_Initilialize() but I can't seem to transfer it over to what I would like. Also, what is going on with the Dim d(): d = myrange? Thanks!

This Works, but is not how I want my code set up.

Private Sub UserForm_Initialize()
    Dim myrange As Range
    Set myrange = Sheet75.Range("A14:A16")
    Dim d(): d = myrange            'what is this line?
    ComboBox1.List = d
End Sub

This doesn't work, but is more like what I want, where I have declared myrange as a public variable in a module (and it is Sheet75.Range("A14:A16"))

Private Sub UserForm_Initialize()
    ComboBox1.List = myrange
End Sub

 

[Zack Barresse]

Hi there,

The "Dim d()..." line is dimensioning "d" as a Variant array, which ends up being a two-dimension array, housing the values of the specified range. So d(1, 1) houses the value of A14, d(2, 1) houses the value of A15, etc.

We don't know what you've declared "myrange" as. Not sure why you're declaring it as a public variable anyway. You should stay away from public variables as much as you can. It's a known issue they can go awry when having too many of them. Not to say they're not handy, but too many can be a bad thing.

To answer your question specifically, your second code snippet doesn't work, I'm assuming, because the myrange variable isn't set. Here are two examples which should work for you...

Private Sub UserForm_Initialize()
    Set myrange = ActiveSheet.Range("A1:A20")
    ComboBox1.List = myrange.Value
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = ActiveSheet.Range("A1:A20").Value
End Sub

You must set a List property to a Range objects Value property, which can be indexed in an array.

 

[MickG]

You first code Creates an array "d" of the range values.
Which ever way you do it , You still need to set the "MyRange" range
What your other code needs is the (.value) property i.e "Myrange.value"
This worked for me:-
Nb:- This also set the first value in the Combobox

Private Sub UserForm_Initialize()
Set MyR = Sheets("Index").Range("a1:a10")
With Me.ComboBox1
    .List = MyR.Value
    .ListIndex = 0
End With
End Sub

 

[Prevost]

Thanks for the responses! I have set my ranges as a public variable so that I can call them from my userform and my module without having to declare them more than once (and I declared myrange in my module). Should the userform theoretically be able to pull the public myrange variable into the combobox without having to set it within the userform? That is what's happening with my code....So I have to redimension my range into an array, I am assuming that that is the case for any range when you want to manipulate it?

Thanks!

Here's code that works

Private Sub UserForm_Initialize()
    Dim myrange As Range
    Set myrange = Sheet75.Range("A14:A77")
    Dim d(): d = myrange
    OnespeedRPMBox.List = d
End Sub

Here's code that doesn't work (when I set myrange as a public variable it's a no go!)

Private Sub UserForm_Initialize()
    Dim d(): d = myrange
    OnespeedRPMBox.List = d
End Sub

 

[Norie]

Where are you declaring, and setting the value of, myrange?

By the way, are you sure it needs to be public?

You mention code in a module, what does that code do and does it need to be in a separate module?

 

[Zack Barresse]

Yes, you still have to set the variable, even if it's declared. You've given it scope but haven't defined it, so the Range object of the variable is equal to Nothing until set. So your second code example is doing the same thing - the "myrange" variable isn't being set. Either set the variable, or point to the actual range. Look at my examples. You don't have to set your range to an array. The range object just needs to be set, which is a separate process than declaring it.

 

[Prevost]

Thank you! I had declared my array, I just had declared it in the wrong place so when I initiated my userform, it could not see the value of my range. I will also look into passing values to a sub (which I had not heard of before) but these replies made start searching as to why you may not want to use global variables. Thanks again for all the posts.

 

[Zack Barresse]

The problem with multiple public/global variables is they tend to lose scope. This generally doesn't happen unless you have an exorbitant number of them. I'm not sure I see a reason to keep this particular variable as public though. I don't see it being passed from anywhere else, and if it's not being defined or created through another process or class, there's really no need for it.

HTH