VBA Calendar

[Porschemaniac]

Something that I am currently working on requires a date to be input. Ideally, I will then be using this date as a variable, for use in some other VBA macros.

Having read many, many online tips (and after consulting my Excel VBA for Dummies), I have managed to get a calendar working.

I have inserted a User Form, with a Calendar control. I have successfully managed to call this from a worksheet, using a command button and have inserted the neccessary code into both the worksheet and User Form (code to call in worksheet - UserForm.1 Show) and I have also inserted code directly into the User Form to control two embedded control buttons (OK and Cancel).

All of the examples that I have found read the date from the User Form directly into a cell, using Active.Cell = UserForm1.Calendar1.Value. However, I need to take the UserForm1.Calendar1.Value and read it into a variable called Lrd (Last Review Date).

I have added the line Lrd = UserForm1.Calendar1.Value into the worksheet, where I need to use the date variable (after the line UserForm1.Show) However, when the User Form activates, I am able to select dates, usng the calendar, however, the date does not seem to populate into the Lrd variable.

In fact, what actually happens is that Lrd populates as the date last set when editing the Calendar User Form in the VB Editor.

I know the value is in there somewhere, I just need to figure out how to get it into my Lrd Variable.

As always, any help will be greatly appreciated.

Thanks,

Ian.

 

[SkipVought]

hi,

 with YourSheetObject.YourRangeObject
   .value = UserForm1.Calendar1.Value 
   .numberformat = "yyyy/mm/dd"  'or whatever format you want
 end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Thanks once again Skip.

Guess this code goes into my Worksheet code?

Because I'm trying to get the date value into the 'Lrd' variable, do I need the YourRangeObject?

Best regards,

Ian.

 

[SkipVought]

So why do you need in your variabel?

Please post ALL your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Hello Skip,

This is a further development of the training course tracking spreadsheet that you have given me some guidance for already.

Basically, I am building a spreadsheet to be used as a tool in tracking training and development activities undertaken within a specific department. As part of this, I want to generate a report telling the user, "Since your last review, you have completed X training courses. Currently, Y training courses are marked incomplete"....etc.

I appreciate that I could probably do this, using standard spreadsheet functions, but it appeared to me that buidling each of the key statistics within VBA would provide me a bit more flexibility in building a 'virtual' report.

The end user will click a command button, called "Update Stats". This will allow them to select the last review date (the last time they reviewed their development plan with thier manager) and will then check indicators and dates on another sheet "Providers Mapping" to see which courses have been marked complete since the last review date, how many of the courses they are marked exempt from completing, etc.

So far, I have a version of this working, using an Input Box to accept the last review date, however, one of the end users asked if they could use a pop-up calendar to input the date. I guess this makes sense as it reduces the chance of entering an invalid date.

Currently, I have the following code in a worksheet named "Stats" - this is where the user will click the "Update Stats" Command Button and generate their report.

Code -

Private Sub CommandButton1_Click()

Dim Cell As Range
Dim CurDate As Date
Dim Lrd As Date
Dim ctr As Integer
Dim ctra As Integer
Dim ctrb As Integer
Dim ctrc As Integer
Dim CellDate As Date
Dim Cellct As Integer
Dim CompStat As String

CurDate = Date

UserForm1.Show

Lrd = UserForm1.Calendar1.Value

For Each Cell In Worksheets("Providers Mapping").Range("I3:I394")
Cellct = Cellct + 1
CellDate = Cell.Value
'Check the completion indicator in the Providers Mapping sheet - y = yes n = no e = exempt
CompStat = Cell.Offset(0, -1)
'Compare completion dates and completion indicators from Providers Mapping Sheet
If CellDate >= Lrd And CompStat = "y" Then
ctr = ctr + 1
End If
If CellDate < Lrd And CompStat = "y" Then
ctra = ctra + 1
End If
If CompStat = "e" Then
ctrb = ctrb + 1
End If
If CompStat = "n" Then
ctrc = ctrc + 1
End If
Next Cell

Worksheets("Stats").Range("B6") = "You have marked " & ctr & " training activities complete since your last review."

Worksheets("Stats").Range("B8") = "In the period before your last review, you had marked " & ctra & " training activities complete."

Worksheets("Stats").Range("B10") = "Including all possible training courses for your department, there are " & Cellct & " courses currently available."

Worksheets("Stats").Range("B12") = "You are currently exempt from completing " & ctrb & " Courses."

Worksheets("Stats").Range("B14") = "Currently, " & ctrc & " courses are marked as incomplete."

Worksheets("Stats").Range("B16") = "This report was generated on: " & CurDate

End Sub



I Have added the UserForm as User Form 1 and, in the code for the User Form, I have the following:



Option Explicit
Sub Initialise_User_form()

'Set default value to 30 days before today

UserForm1.Calendar1.Value = Date - 30

End Sub

Private Sub CommandButton1_Click() 'OK

Dim Lrd As Date

'Place the date from the calendar into the Lrd variable
Lrd = UserForm1.Calendar1.Value


'Dismiss the userform
Unload Me

End Sub

Private Sub CommandButton2_Click() 'Cancel

'Dismiss the userform
Unload Me

My belief was that the UserForm1.Calendar1.Value could be used to update the Lrd variable and then, in turn, drive the other comparrions within the "Stats" sheet.

Regards,

Ian.

 

[SkipVought]

I am totally confused. You have TWO CommandButton1_Click procedures, one actually shows the userform, that supposedly he commandbutton is on.

???

Your variable only had 'life' while that procedure is running. When the procedue ends, the variables disapear.

Check out Understanding Scope and Visibility.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Thanks Skip.

Fixed it.

Regards.

 

[SkipVought]

Would you mind explaining how you fixed it, for the benefit of anyone who might browse this thread?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

The clue was in the fact that the variable only has life whilst the procedure is running.

Previously, the code to build the report was resdient in the worksheet that the "update Stats" button lives in.

Of course, as soon as you click that button, you invoke the User Form, so, by moving the code to be resident within the User Form, the variable is 'live' until the form is dismissed.

 

[SkipVought]

Thanks!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}