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]

[/FONT]
Private Sub UserForm_Initialize()

Dim lastRow As Long
lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Me.MonthBox.RowSource = Sheets("Sheet1").Range("A1:A" & lastRow).Address(External:=True)
Me.YearBox.RowSource = Sheets("Sheet1").Range("B1:B" & lastRow).Address(External:=True)

</pre>

 

[hallingh]

Now when I double click the cell to show the userform it gives me a "subscript out of range" error. Is this because it's associating it with a different sheet and the code to show the userform is on my main sheet???

 

[jmthompson]

Is this the code you are using?

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

What is the name of the worksheet this code is in?

And what row of code is highlighted when you debug the error?

 

[hallingh]

Yes, that is my code. This line is highlighted:

EnterDate.Show

 

[jmthompson]

I've made some tweaks to the Ok Click macro, but I am not having any trouble calling the userform. Since I don't know the name of your main sheet, you will have to replace all of the Sheet2's in this code

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

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

 

[hallingh]

Still the same problem. I didn't have any issues with that code before I made the changes to the RowSource in the userform code. Do you have any idea what could be causing this? It's weird that the problem came out of nowhere.

 

[hallingh]

Is there any way to call a dynamic range in vba? Would it help if I coded it using the named range instead of referencing the range from the sheet?

 

[hallingh]

Success! I just took out the code that changed the Row Source, and used the updated OkClick code and it works perfectly. Thank you SO much for your help. You are an absolute life saver!

Hank

 

[jmthompson]

happy to help