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

 

[jmthompson]

If that is where your headers are, yes. Sorry if you told me that and I missed it

 

[hallingh]

MyNum is getting its value correctly, so (I think) the problem must be in this portion of the code:

 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

Any idea what might be the issue with that code? I made the change in value of the header row and still no luck. Thanks again for the code.

Hank

 

[jmthompson]

Are you getting an error?

I've had issues in the past with referring to comboboxes by their names, try replacing MonthBox and YearBox with ComboBox1 and ComboBox2

 

[hallingh]

I think it was ok with the names. I wasn't getting an error, and when I changed the names to ComboBox1 and ComboBox2 I got an object defined error. It seems like everything is working perfectly except for assigning the value to the correct cell. The combo boxes clear, MyNum is getting its value. I've been tinkering with the code pretty much blindly but to no avail.

 

[jmthompson]

Then rsp must not be forming correctly, let me play with it

 

[jmthompson]

try this

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 & "-" & 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]

Same result with that code. I'm not sure if this means anything/is helpful at all, but I put:

ActiveSheet.Range("A26").Value = Right(Me.YearBox.Value, 2)

at the end of my code to see if the Right(Me.YearBox.Value, 2) was the issue, and cell A26 remained blank. Should that statement set A26 equal to the last two digits of the year from my combo box?

 

[jmthompson]

Okay, the code works for me as

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 & "-" & 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

Big question- are your month-yr headers formatted as text?

 

[hallingh]

Ahhhhhhhh no! They are formatted as a Date. Is that an issue?

 

[jmthompson]

Yes, because your userform is collecting text, not numbers. I am guessing the actual values in the header are 1/01/2011, or the Excel equivilant. I would recommend changing the headers to tex values that match the current display, ie Jan-11. if you changed your userform to collect dates, it would be difficult to get the end user to pick the correct date to match the header.