MsgBox for Date/Year input

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
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.
 
Last edited:

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
{{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

Well-known Member
Joined
Sep 11, 2010
Messages
769
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

Well-known Member
Joined
Sep 11, 2010
Messages
769

ADVERTISEMENT

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

Well-known Member
Joined
Sep 11, 2010
Messages
769
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786

ADVERTISEMENT

First, how do I activate the userform when a cell is activated?
Keeping it simple, stick this in your worksheet's code module:-
Code:
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
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:-
Code:
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

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
An alternative is to create a macro which just does this:-
Code:
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.
 

Forum statistics

Threads
1,141,734
Messages
5,708,171
Members
421,550
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top