Solved

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

Posted on 2008-10-24
8
1,345 Views
Last Modified: 2012-05-05
Need to use my array as a list box on a pop up message box. Then store my selection to a vaiable.
Thanks.

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
    Else
   Text = Cells(X, Y)
   X = X + 1
   ReDim Preserve ToAddresses(LBound(ToAddresses) To UBound(ToAddresses) + 1)
   ToAddresses(UBound(ToAddresses)) = Text
  End If
Loop
   
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

0
Comment
Question by:kasimir2008
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:Cory Vandenberg
ID: 22798106
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.
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22798151
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.
0
 

Author Comment

by:kasimir2008
ID: 22798200
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.


0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:kasimir2008
ID: 22798299
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.
Thanks.
0
 
LVL 18

Expert Comment

by:Cory Vandenberg
ID: 22798328
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.
0
 
LVL 18

Accepted Solution

by:
Cory Vandenberg earned 500 total points
ID: 22798522
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.

WC
ToAddresses.xls
0
 

Author Comment

by:kasimir2008
ID: 22798686
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.
Thanks
0
 

Author Closing Comment

by:kasimir2008
ID: 31509730
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.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question