Solved

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

Posted on 2008-10-24
8
1,333 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:WarCrimes
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
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:
WarCrimes earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 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

9 Experts available now in Live!

Get 1:1 Help Now