Solved

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

Posted on 2008-10-24
8
1,338 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
macro for same location path 2 19
Excel for Mac - How make those Tabs larger? 2 31
Update As Well As Add 6 38
Excel 2010 - Comparison of texts in a cell 14 36
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now