• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1377
  • Last Modified:

use array in list box on Msg Box, store to variable

Need to use my array as a list box on a pop up message box. Then store my selection to a vaiable.

See at the end of the Code. Array is called ToAddresses.
Sub SendEmail()
Dim X As Integer
Dim Y As Integer
Dim Count5 As Integer
X = 2 'Row
Y = 7 'Column
Sheets("All ICG Cost Conditions").Select
 Dim ToAddresses As Variant
 ToAddresses = Array()
Do Until IsEmpty(Cells(X, Y))
  If Cells(X, Y) = Cells(X + 1, Y) Then
   X = X + 1
   Text = Cells(X, Y)
   X = X + 1
   ReDim Preserve ToAddresses(LBound(ToAddresses) To UBound(ToAddresses) + 1)
   ToAddresses(UBound(ToAddresses)) = Text
  End If
Text1 = Join(ToAddresses, "; ")
Count5 = UBound(ToAddresses) + 1
'MsgBox Text1 & " " & Count5, _
'vbInformation, "HELP by Ronny Lambotte"
'Text2 = ToAddresses(0)
'MsgBox Text2, _
'vbInformation, "HELP by Ronny Lambotte"
'See below....found this on Expert Exchange....don't know how to use 
'this. I want the array (ToAddresses) to be used in a list box on the 
'message box and be able to store my selection into a vaiable
Dim arr As Variant
Dim SF1 As String
ComboSF1.List = ToAddresses
 SF1 = ComboSF1.ListIndex + 1
 MsgBox SF1
End Sub

Open in new window

  • 4
  • 3
1 Solution
Cory VandenbergSenior Risk ManagerCommented:
You will have to create a form containing the listbox which can be populated with your array.  When the user selects an item you can hide the form and store the selected item in your variable.
To repeat what you are asking, just to make sure I understand it:

You want to display a MessageBox, but you want a ListBox to appear on the MessageBox.  The user will select something from the ListBox and then click OK from the MessageBox.

If this is what you want, you will have to create your own Message Box Form like WarCrimes suggests.
kasimir2008Author Commented:
to all:
Is it possible with vba code to create that messagebox and the listbox in it? If yes, what is the code?
Thanks. You both are correct, how would I do this.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

kasimir2008Author Commented:
I am ok with any similar to a list box. Can be a check box. The only thing is, only one option should be selected. This is why I was asking about the list box. Would like to stay with that if possible.
Just don't know the code using a message box with the array as the list. Then store the selection to a variable. There could be a button on the botom after the selection was made to close the box.
Cory VandenbergSenior Risk ManagerCommented:
Does it have to be a message box that pops up?  What about using a listbox embedded in your worksheet?

I have never used VBA to create a form, but it might be possible.  You can manipulate the form and items contained on it via VBA, but the issue with storing the value selected by the user in the listbox is how do you get it back to the original calling function?  The easiest way would be to store the value in a cell on the worksheet and then close the form.  I don't know if this would "interupt" the original sub running and then let it continue, but if you really need a pop-up that would be one way to do it.

Personally, I think an embedded listbox might be the way to go.
Cory VandenbergSenior Risk ManagerCommented:
I have done something similar before when dealing with selecting a Project Start Date and End Date.

If you have a range of values, you can set a cell to refer to range and make it essentially a listbox via the Validation option.

Select the cell you want to use as the "dropdown", go to Data->Validation in the menu.  In the Settings Tab, for Allow: select List
Then in the Source input box, put the range.  The easiest way of doing this is to have a named range, and even easier is to have a dynamic named range which will update this "dropdown" any time a value is added to your range.

I have attached an example where I have a list of Addresses.  If you go to Insert->Name->Define and look at the ToAddy named range, you'll see the formula to make column A a dynamic named range from A2 through A2000.  Basically, your named range ToAddy will start at A2 and go down to the row just before the first blank cell.  If your range goes past A2000, you'll have to update that to a larger number.

In cell C2, when you select it, you're presented with a dropdown of the values in your range.  To access this value via VBA is now pretty simple.  You could either just say Range("C2").Value or give this cell a name, and the do Range("SelectedToAddy").Value where "SelectedToAddy" is whatever name you gave the cell.

kasimir2008Author Commented:
this would be ok. I am really trying to do this on a message box. This was part of my design I wanted.
I have the array, just want to be able to use this it on a pop up, so the user can select. I guess I will use vba to create a list, then use the data selected to store.
kasimir2008Author Commented:
Was looking for list in Message box using VBA. Not sure if this was possible. Thanks for all your help. I will figure this out with using a list on the spreadsheet.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now