Using the output of a custom calendar form

Question

I am trying to use this userform to get a date but I do not know how to set it to display today as the default (always 18/08/2012) and I do not know how to actually get it to pass the selected date to the current sub.

I couldn't find the blog that was mentioned online.

Sub Macro1()
    UserForm1.TextBox1.Text = Date
    UserForm1.Show
    ActiveCell.Value = UserForm1.TextBox1.Value
End Sub

I'm trying to use the above code to test it.

Wrote a solution for you. You can have a go at it or you can simply check the customize stuff posted by Trevor in the same link you got. Haven't tested it so it's up to you. — L42, Feb 25 '15 at 05:11

L42 · Accepted Answer · 2015-02-25T04:59:04.667

In your GenerateCal Sub add this two(2) lines at the bottom.
These lines of code will initialize the values of the said controls.

CommandButton45.Caption = Format(.Range("A1").Value, "mmm - yyyy")
TextBox1.Text = DateSerial(Val(Format(CommandButton45.Caption, "YYYY")), _
    Val(Format(CommandButton45.Caption, "MM")), 1)

Then change this line:

.Range("a1").Value = Application.Text(dt, "mmmm yyyy")

to this line:

.Range("a1").Value = Application.Text(StartDay, "mmmm yyyy")

dt is of type string so it will generate a different result.

I also believe that being explicit is always better so I suggest you add this declarations on top of GenerateCal Sub.

Dim StartDay As Date, FinalDay As Date
Dim DayOfWeek As Integer, CurYear As Integer, CurMonth As Integer, x As Integer
Dim Cell As Range
Dim RowCell As Long, ColCell As Long

The next problem is the deletion of the temporary sheet generated by the UserForm_Initialize Sub. It seems you cannot delete a non-visible sheet (I'm using XL2013, I'm not sure if it is true to other versions). So you need to change it a bit like:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'    On Error Resume Next
    Application.DisplayAlerts = False
    With ws
        .Visible = xlSheetVisible
        .Delete
    End With
    Application.DisplayAlerts = True
'    On Error GoTo 0
End Sub

Notice that I removed the OERN and OEG0 because I don't think there is a need for that.

Finally, to clean it all up, you'll need to clear the manually entered caption for CommandButton45 and TextBox1 in the properties window. Once you've incorporated all the changes correctly, you should get the correct date in your Userform1.

Additional:

You are accessing the value of TextBox1 correctly. But I'd suggest to edit this code if you want to pass is somewhere.

'~~> Ok Button
Private Sub CommandButton53_Click()
    MsgBox TextBox1.Text
End Sub

Sample:

Private Sub CommandButton53_Click()
    ActiveCell.Value = TextBox1.Value
End Sub

Using the output of a custom calendar form

1 Answers1

Linked