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

 

[AlphaFrog]

Your custom message box is called a UserForm. Here's a starter tutorial or do a web search for Excel UserForm tutorials and you'll find a lot more.
http://www.mrexcel.com/Excel_VBA_Userforms_training.html


This link is for a UserForm Date Picker. It's much more than what you asked for, but it's a good example of what can be done.
http://www.fontstuff.com/vba/vbatut07.htm
Note: You can download its code at the bottom of the link.

If you do a web search for Excel Date Picker, you'll find a few others as well.

 

[Ruddles]

{{Edited because it repeated some of AlphaFrog's post.}}

Triggering a userform by "putting an icon in a cell" is another question. What do you mean by "an icon"?

 

[hallingh]

Awesome, thanks for the info. What I want to do with this is have the user select a month and year, and then create a string that will be in this format - "Jan - 09" or "Feb - 11." I want to do that because I need it to put a number from a certain cell into a different cell in hte correct column. The columns have headers that are in this format. I would assume that the msgbox I first came up with would be better than the calendar for this, but if you think otherwise please let me know. Again, thanks a ton for the info.

Hank

 

[hallingh]

By an icon, I just wanted to put something into the cell that lets the user know if they click the cell the message box will pop up. I've seen some examples of workbooks that have cells that contain little calendar icons which, when the cell is clicked, activate a pop up calendar.

 

[hallingh]

OK. I created my userform, called EnterDate, with two combo boxes and a command button. I would like the user to select the month from the first combo box and the year from the second. I have defined two ranges, month and year, on a worksheet titled UserFormRanges. I have a couple questions. First, how do I activate the userform when a cell is activated? And second, how do I get the drop down menus to actually display the data contained in my named ranges? I'm not sure where to enter the code. Any help would be greatly appreciated. Thanks!

Hank

 

[Ruddles]

First, how do I activate the userform when a cell is activated?

Keeping it simple, stick this in your worksheet's code module:-

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If Target.Address = "$A$1" Then UserForm1.Show
 
End Sub

That will trigger when cell A1 is selected. (There's also an event which triggers when a cell is double-clicked if you prefer that.)

 

[Ruddles]

And second, how do I get the drop down menus to actually display the data contained in my named ranges?

You've used comboboxes rather than a date picker, yes?

Select the combobox and from the menu bar go View > Properties Window, then set the RowSource to the name of your range.

More info follows...

 

[Ruddles]

To retrieve the value selected from the combobox, you refer to it in VBA as UserForm1.ComboBox1.Value. You can copy the value automatically into your worksheet by right-clicking the combobox, selecting View Code and entering the following:-

Option Explicit
 
Private Sub ComboBox1_Change()
 
  Range("A1") = UserForm1.ComboBox1.Value
 
End Sub

Whenever the combobox changes, the event is triggered and the value is moved to cell A1 (or wherever you want it to go).

 

[Ruddles]

An alternative is to create a macro which just does this:-

Option Explicit
 
Public Sub ShowMyForm
 
    UserForm1.Show
 
End Sub

Then go Developer > Insert, select the first icon (command button) from the Form Controls section, and drag-release it on your worksheet. Set the Assign Macro to ShowMyForm. Set the caption to something suitable. Now you can display the form by clicking the button.