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]

Where in my workbook code do I put that? I tried to put it in the module but it isn't currently working. Thanks for the code. Also, do you have any idea how I can accomplish what I'm trying to with this? It seems Jmthompson gave up on me

 

[jmthompson]

jmthompson had to pick her kid up from daycare

What will determine in which row your budget amount will be placed?

And your code needs to be placed on the worksheet, right-click on the tab name, click View Code and past your code in there.

 

[hallingh]

Ahhh perfect, that code works great now. The row my budget amount will be placed in will be determined by which one seems the most intuitive for the user to enter the figure into, which in my opinion is cell E6. Thanks for returning to the fight for me!

Hank

 

[jmthompson]

What row do you want the macro to place the amount into? i know the column will be determined by the userform selections...

 

[hallingh]

The figure will be placed into row 13, obviously at whatever column is correct based on the header.

 

[jmthompson]

Okay, I don't have time to set up a userform to match yours, so this is untested. Please save your workbook before running the macro.

In VB, go to your userform, view code and add this code:

Private Sub OK_Click()
   
Dim myNum As Long
myNum = Range("E6").Value
Dim LCol As Long
Dim rsp As String
Dim y As Long
Const lngHeaderRow = 1
On Error GoTo EndSub
    LCol = Cells(lngHeaderRow, 2).End(xlToRight).Column
    rsp = Me.MonthBox.Value & "-" & Application.Right(Me.YearBox.Value, 2)
    For y = Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
        If Cells(lngHeaderRow, y).Text = rsp Then
        Cells(13, y).Value = myNum
        End If
    Next y

EndSub:
Me.MonthBox.Value = ""
Me.YearBox.Value = ""
End Sub

 

[hallingh]

Wow, thank you. The code looks great. Should I put that in the code for my "oksubmit" commandbutton? Obviously I want the user to click that commandbutton in order to run the code.

 

[jmthompson]

Yes, this will be the code for OK/Submit

 

[hallingh]

OK, I put that code into my Submit Command button, and when I activate the userform and click the submit button it clears the comboboxes on the userform but doesn't update the correct cell. Any idea what the issue might be? I'm trying to figure out the issue but a lot of the code is a little over my head. I can figure out roughly what each step is supposed to accomplish, but have no ide ahow to go about troubleshooting. Thanks again for the code.

Hank

 

[hallingh]

Should this:

Const lngHeaderRow = 1

be this? :

Const lngHeaderRow = 12

I could be way off on what this is doing, but the Header row constant should be 12 maybe? Just thinking of possible problems.