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]

You sir (or Ma'am,) are awesome. Thanks a ton for the help. I'm going to give this all a shot now. I will let you know if I have any issues. Again, thanks you thank you thank you.

Hank

 

[hallingh]

Alright, I have two questions. First, how do you call the event that triggers the user form when the cel is double clicked? I think that will be a better option for this sheet.

My second question is a bit more complicated. I see how to set a cell equal to the combo box entry. However, what I need is a bit more tricky. The purpose of this userform is obviously to allow the user to enter a month and year. The sheet is deisgned to forecast a budget. The user first enters a number (which is the total budget for a given month) into a cell. He/she then uses the userform to select which month and year that number applies to. After the user clicks my "submit" command button on my userform, I want it to automatically populate the correct cell.

There is a row that I'm going to need to search inorder to do this. It is the header of my table of budget forecasting, and contains values in this format:

Nov-09 Dec-09 Jan-10 Feb-10 etc.

These dates will be changing month to month, so I somehow need my userform to recognize the users selection from the combo box and put it into the same format as above. This would allow me to do a simple search of that row in order to retrieve the correct column for which to put the user's number for that monthly budget.

If I am not being clear as to what I need to accomplish, please let me know. I know this is a pretty tricky problem, but I am struggling with a solution and would greatly appreciate some help. Thanks for all the help thus far!

Hank

 

[jmthompson]

This sounds a bit similar to something I've created in the past.

What are the names of your Month and Year comboboxes?

Please give an example of the month and year values as collected by your user form.

 

[hallingh]

My combo boxes are titled MonthBox and YearBox. Really original, I know. Right now I just have the userform setting two seperate cells equal to the combo boxes. This is obviously not a solution to the problem, I just wanted to get the userform working. So, cell A1 on a random sheet says whatever month they select which I have in the format Jan, Feb, Mar, Apr, May, etc. I did this because this is the format they are on the worksheet. The years I have in their full form (2011, 2012, etc.) because I thought only having the last two digits was a bit off. Thanks for the reply.

Hank

 

[hallingh]

Also, the code to make the userform pop up when the cell is clicked is not working. I could just use the command button, but I think my boss will like the former method more. I have tried putting it into module1, the code of the actual worksheet, and the "ThisWorkbook" tab in the project window. Am I doing something wrong here? I put the code in exactly as you had it except replaced UserForm1 with my Userform name, "EnterDate"

 

[jmthompson]

Few more questions-
What row are your headers in?
What is the first column of your month-yr columns?

 

[hallingh]

My headers are in row 12 and the first column containing a Month-Yr is column B

 

[jmthompson]

And where is the user entering the amount to be budgeted, is it in a cell on the same row where the amount should be recorded?

 

[hallingh]

No, it is in a cell directly next to where he/she will click to activate the userform. Probably E6.

 

[Ruddles]

Alright, I have two questions. First, how do you call the event that triggers the user form when the cel is double clicked?

Option Explicit
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
  If Target.Address = "$A$1" Then 
    Cancel = True
    UserForm1.Show
  End If
 
End Sub