Solved: Combo Box inside pop-up Message Box ?

[bdsii]

hello all.....I can't find this specifically anywhere.

I would like to create a macro that when run would cause a pop-up message box (or could use a Userform I suppose??) that would allow the user to select from about 20 different options. The option they select would be assigned to a variable that I would use later on.

The options inside the combo box would be actually be specific sheet names from the workbook. I can manually enter them to be used or if the code can automatically can provide the sheet names to be selected that would be good too. However there may be certain sheets I do not want to be selected so I am not sure if that is the best option.

I want to use the combo box to allow the user to select the sheet and store the sheet name in a variable that will be used later in a macro.

Is it possible to have a combo box in a Message Box or would I need to use a UserForm? If I need to put code in something other than the VBA editor I would need guidance on specifically where that would be. I do not want the combo box to run upon opening or anything like that just when called by the macro.

Any ideas on this ?

Thanks for the help!

[lucas]

You will have to use a userform. Would a listbox work as well?

[lucas]

You can exclude sheets in the initialize statement for a listbox or combobox like this:

For a listbox:
[vba]Private Sub UserForm_Initialize()
Dim wk As Worksheet
For Each wk In Worksheets
If wk.Name <> "Sheet1" And wk.Name <> "Sheet2" And wk.Name <> "Sheet4" Then
ListBox1.AddItem wk.Name
End If
Next wk
End Sub[/vba]

It will add all sheet names to the listbox except those you exclude.

A simple example is attached. The variable assigned to the sheet is in the commandbutton1 click event and it is shtname

[mikerickson]

Are you on a Mac?

If so, this might work for you
[VBA]Sub test()
Dim oneSheet As Worksheet
Dim sheetNames As String
Dim userChoice As String

For Each oneSheet In ThisWorkbook.Sheets
sheetNames = sheetNames & ", " & Chr(34) & oneSheet.Name & Chr(34)
Next oneSheet
sheetNames = "{" & Mid(sheetNames, 3) & "}"

userChoice = MacScript("choose from list (reverse of " & sheetNames & ")")

If userChoice = "false" Then
MsgBox "cancel pressed"
Else
MsgBox "You choose the sheet name: " & userChoice
End If
End Sub[/VBA]

[bdsii]

Thanks all for the replies. I am running it on a PC.

I do not think I can use a simple listbox without using VBA because the number of items to be displayed for selection is more than 8 which is the limit without VBA (I think).

lucas - I appreciate the sample file and will be working through it today. One question I have is the code below a leftover from previous code ? I could not follow where it was being used.
[VBA]
a = ListBox1.ListCount
[/VBA]

Also, a question about the For Next loop below. Does that populate the listbox on the userform or does it loop through the sheets to find the sheet to move to ? I am not clear on it right now.

[VBA]
For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
shtname = ListBox1.List(X)
' ActiveWorkbook.Sheets(shtname).Delete
' ListBox1.RemoveItem (X)
ActiveWorkbook.Sheets(shtname).Select

End If
Next

[/VBA]

If I wanted to manually populate the options to select in the listbox on the userform, would I replace the loop with some type of manual entry code ?


I do appreciate the help !


thanks!

[Aflatoon]

I do not think I can use a simple listbox without using VBA because the number of items to be displayed for selection is more than 8 which is the limit without VBA (I think).

That is not true, but you certainly cannot use a userform or MsgBox statement without VBA!

[lucas]

bdsii, you are correct. I apologize for the confusion. The line:
[VBA]a = ListBox1.ListCount[/VBA]
and at the top of the buttonclick module this line that defines it:
[VBA]Dim a As Integer[/VBA]

can safely be removed from the code.


You asked about this code:
[VBA]For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
shtname = ListBox1.List(X)
' ActiveWorkbook.Sheets(shtname).Delete
' ListBox1.RemoveItem (X)
ActiveWorkbook.Sheets(shtname).Select

End If
Next
[/VBA]

It is the code that navigates to the sheet based on your selection.

The listbox is populated in the initialize statement which is this:

[VBA]Private Sub UserForm_Initialize()
Dim wk As Worksheet
For Each wk In Worksheets
If wk.Name <> "Sheet1" And wk.Name <> "Sheet2" And wk.Name <> "Sheet4" Then
ListBox1.AddItem wk.Name
End If
Next wk
End Sub[/VBA]

It is the code where you decide which sheets to exclude from the listbox.

Hope this helps and if you have more questions, don't hesitate to ask.

[bdsii]

Thanks Lucas! I reviewed the code and it is helpful in understanding how this all inter-relates and works. I learn better having a working example which this provides. :-)

So, basically, the worksheet is activated by the worksheet that is selected in the listbox and the code references not the actual name of the worksheet but the number of item in the listbox. Is this correct ?

If that is the case, would it be possible to instead of using the listbox number that is selected to use a CASE statement to activate the worksheet and then perform additional steps?

thanks so much !

[lucas]

Actually it is operating off of the name of the sheet. the X is just a variable that denotes the string selection.

What additional steps do you wish to implement?

[bdsii]

I want to use the listbox to select the correct sheet and then use the userform to allow the user to input more data that will be entered into cells on that specific sheet which was selected in the listbox.

I just thought a CASE statement would allow me to specify if the user selected sheet100 then X,Y,Z wouldhappen. If the user selected Sheet200, the A,B,C would happen. I have used Case statements before and they are simple enough for me to grasp and utilize :-)

I am gonna have to do more digging, I am not sure how the X denotes the string selection. That is just me from being unfamilar with this and trying to learn. You have helped so much providing a working example I can dissect and use to learn.

thanks!

[lucas]

Like this:

[VBA]Option Explicit
Private Sub CommandButton1_Click()
Dim X As Integer
Dim shtname As String
On Error Resume Next
Application.DisplayAlerts = False
For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
shtname = ListBox1.List(X)
' ActiveWorkbook.Sheets(shtname).Delete
' ListBox1.RemoveItem (X)
ActiveWorkbook.Sheets(shtname).Select

End If
Next
Select Case shtname
Case "Sheet5", "Sheet3"
MsgBox "You selected Either sheet 3 or sheet 5"
Case "Sheet7", "Sheet8"
MsgBox "You selected Either sheet 7 or sheet 8"
End Select
Application.DisplayAlerts = True
End Sub[/VBA]

[lucas]

I misunderstood so I thought that if you selected one of several sheets that an action would occur. You can simply remove the ones you don't need and add more actions.

Hope this helps.

[MZLZL]

Good Job

[bdsii]

Great, Lucas ! That should do it. I appreciate it! Others probably will benefit from this in the future as well. I will mark it solved :-)

Buford