Solved: Calendar in Userform

[gsalois]

Hi all,

I'm modifying John Walkenbach's excellent chart in a userform code. I'm trying to use a button to select a radio button AND a date from the calendar object. When both of these selections are made, I have the appropriate chart showing up fine in the (individual) userforms. I'm also trying to populate a textbox on the form with the balance from the spreadsheet that matches the radio button selection and the date from the calendar form.

So, if I select the BOF button and then select August 10, 2010 from the calendar, I am trying to get the "Balance" from the spreadsheet that matches and populate the text box with that number.

I'm attaching the wkbook and hopefully someone can point out a solution! I am ready for a real "D'oh" moment...

Thanks!!

Gene

[Bob Phillips]

[vba]

Private Sub Calendar1_Click()
Dim sh As Worksheet
Dim SelectedDate As String
Dim DateRow As Long

Select Case True

Case Me.obABC: Set sh = Worksheets("chtABC")
Case Me.obBOF: Set sh = Worksheets("chtBOF")
Case Me.obDEG: Set sh = Worksheets("chtDEG")
Case Me.obXYZ: Set sh = Worksheets("chtXYZ")
End Select
SelectedDate = Format(Calendar1.Value, "mmm yyyy")
On Error Resume Next
DateRow = Application.Match(SelectedDate, sh.Columns(1), 0)
On Error GoTo 0
If DateRow > 0 Then

txtBalance.Text = sh.Cells(DateRow, "B").Value
End If
End Sub
[/vba]

[gsalois]

Man - that is sweet!! Thank you very much!! I'm three days into reading, trying things out, and generally just making a mess with VBA for Excel so your response is very much appreciated!

Cheers,

Gene

[Bob Phillips]

To be honest, as you are only interested in the month, it should trap the calendar month change as well and do the biz then.

[gsalois]

Thanks, James - interestingly enough everything works except for the BOF button - I've been playing with it, rewriting it and still nada - the other three buttons return the correct value in the text box but the BOF button returns nothing. Would you mind taking one more look?

I'm starting small and eventually hope to expand this to take on month and year and then eventually a daily balance. Small steps...

Thanks!!

[Bob Phillips]

The BOF button works fine for me.

[gsalois]

Strange - it does produce the chart in the userform but the textbox stays empty. I tried creating a new sheet, chart, etc. and added the button to the userform by copying the existing BOF button. The other buttons enter the correct value, but the BOF and the new LLL button simply stay with the last value entered.

At the risk of being a pest - I'm uploading the file - did I gaff something in the "translation"?

Thanks!

Gene

[gsalois]

file...

[Bob Phillips]

Yes, you did. The dates on chtBOF and chtLLL are not in the format of the others, they are mmm not mmm yyyy, so the selected date never matches.

[aysam]

so good

[gsalois]

Got it! That was driving me bats... okay - that got fixed but in the meanwhile now all of a sudden, the button on sheet1 blows up and points to form1 (I'm assuming the module got messed but it's such a little code I can't see what's going wrong).

thanks - you're kind to help!

Cheers, Gene

ps - attached the file again.

[gsalois]

Never mind, James - I went back to a previous version of the file and edited that one with your previously posted code. It seems to work fine now. Once again - thanks - I appreciate your patience. Don't know what I did to blow up the module, but the previous file now with your code seems to work fine!

Cheers!