Making a Custom Dialog Box Work (some code included)

[Bishop]

When I open a spreadsheet I immediately want a dialog box to appear asking
the user for the following information:
Name
Center
Month
Week
Then I want to take that data and assign it to particular cells in the
sheet. I have created a dialog box (NotSoFast) that includes four comboboxes
(Name, Center, Month, Week) and a button (Panic Switch). I just don't know
how to put it altogether to make it work. Here is the following code I have
written so far:

Public NameSelect As String
Public CenterSelect As String
Public MonthSelect As String
Public WeekSelect As String

Sub ShowNotSoFast()
'I have nothing here so far. Not sure if I even need it
End Sub

Private Sub Name_Change()
With NotSoFast.Name
.AddItem "Jack"
.AddItem "John"
.AddItem "Sara"
.AddItem "Mike"
End With
End Sub

Private Sub Center_Change()
With NotSoFast.Center
.AddItem "NYC"
.AddItem "Las Vegas"
.AddItem "Canada"
.AddItem "Grace Land"
End With
End Sub

Private Sub Month_Change()
With NotSoFast.Month
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
End Sub

Private Sub Week_Change()
With NotSoFast.Week
.AddItem "Week 1"
.AddItem "Week 2"
.AddItem "Week 3"
.AddItem "Week 4"
.AddItem "Week 5"
End With
End Sub

Private Sub PanicSwitch_Click()
MonthSelect = Month.Value
WeekSelect = Week.Value
NameSelect = Name.Value
CenterSelect = Center.Value
Cells(1, 1) = MonthSelect
Cells(2, 1) = WeekSelect
Cells(1, 2) = NameSelect
Cells(2, 2) = CenterSelect
End Sub

How do I make my dialog box open when the spreadsheet is opened so the user
can select the appropriate info from the comboboxes and click "panic switch"
to make the info populate the designated cells?

 

[Patrick Molloy]

you can automatically run code when a workbook opens in two ways

1) Use the workbook's OPEN event
2) create a procedure (macro) called Autpen

they would both need this one line of code

notSoFast.Show

 

[Bishop]

Patrick

Thanks. That answered part of my question. The NotSoFast dialog box
appears when I open the workbook now. But none of my drop-down lists work.
Are they coded wrong? Do I need to put them somewhere specific?

 

[Patrick Molloy]

in the code behind the form, add a sub for popuating the list boxes. Call
this from the form's load event. don't know why you're using the change event
....thats the wrong place IMHO