MsgBox for Date/Year input

[hallingh]

I want to put an icon in a cell that will cause a message box to pop up. I want the Message box to contain two drop down menus, one containing the twelve months and the other containing the years 2009-2020. The user should be able to select the month and year and then click OK.

This is only the first part of my problem, but I want to iron this out before I continue. Any help would be greatly appreciated, as my knowledge of message boxes is limited. Thanks for the help!

Hank

 

[hallingh]

I changed one of teh header cells to text and typed in "Nov-10" for its value. Then I entered Nov and 2010 into the userform combo boxes and it still didnt populate the cell. Would I need to have all of them in text format for some reason for it to work?

 

[jmthompson]

Would you show me the code you are using to populate your comboboxes?

 

[hallingh]

I'm not using any code to populate the combo boxes. I just have dynamic ranges on a sheet in my workbook and entered the name into the row source property of the combo boxes

 

[jmthompson]

Okay, in your Userform code, add this, changing the sheet name and range as needed

Private Sub UserForm_Initialize()
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Me.MonthBox.RowSource = Sheets("Sheet1").Range("A1:A" & lastRow).Address
Me.YearBox.RowSource = Sheets("Sheet1").Range("B1:B" & lastRow).Address
End Sub

 

[hallingh]

Uhhhh. Now I'm just confused. The Sheet "UserForm Ranges" is completely empty except for the months and years in columns a and b, respectively. When I put that code in my userform, the combo boexes populated themselves with data from I dont even know where. I have no clue what the issue could be.

 

[jmthompson]

Well that is interesting. Do you have more than one workbook open?

 

[hallingh]

Ah, I see where they are coming from. So it is looking at the correct sheet when it is checking to see how many rows there are. But, when it is populating the combobox, it is going that many rows down on my main sheet instead of in my "UserForm Ranges" Sheet. I'm going to play with the code for a minute to try to figure out why it's happening.

 

[jmthompson]

oops. Should be...

Private Sub UserForm_Initialize()

Dim lastRow As Long
lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Me.MonthBox.RowSource = Sheets("Sheet1").Range("A1:A" & lastRow).Address
Me.YearBox.RowSource = Sheets("Sheet1").Range("B1:B" & lastRow).Address
End Sub

</pre>

 

[hallingh]

I can't see anything different between the two codes? I copy and pasted and changed the sheet name again but I have the same problem. I can't see anything wrong with the code or how it could possibly be populating the combo boxes with values from the main sheet.

 

[jmthompson]

D'oh. There is a further complication if your range is not in the same sheet as your user form. Not a big deal, I just have to find it. Stand by