Specify return location of Calendar selection in VBA

[sueanne]

Hi,
I have the following code in my workbook, and when I select the date in the calendar in the Taxinvoice userform from command button 2 it is returning the value to both the textbox D8 on the Taxinvoice userform and also to cell A1 on the active worksheet.
Can anyone please explain how I modify this code to only make the value return to the D8 text box on the userform.
I have tried deleting the reference to A1 but then it doesn’t work at all.
Thanks!

[B]Userform1[/B]
Option Explicit
'UserForm with Frame1
Private WithEvents Calendar1 As cCalendar

Private Sub Calendar1_DblClick()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub

[B]Module 1[/B]
Sub lancia_cCalendar()
Dim strCell As String
strCell = ActiveCell.Address
Range("A1").Select
UserForm1.Show
Range(strCell).Select
End Sub


[B]TaxInvoice Userform[/B]
Private Sub CommandButton2_Click()
  Call lancia_cCalendar
  Me.D8.Text = Sheet1.Cells(1, 1).Value
End Sub

Private Sub D8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  Dim bRet As Boolean
  Cancel = False
  If IsDate(Me.D8.Text) Then
    Me.D8.Text = CStr(DateValue(Me.D8.Text))
  Else
    If Len(Me.D8.Text) > 0 Then
      Cancel = True
      bRet = MsgBox("Not a valid date", vbCritical, "Date Entry Error")
    End If
  End If
End Sub

 

[Teeroy]

It seems to be your Calendar1_DblClick event code that is placing the value in A1. Try commenting it out and see if it works as expected.

 

[sueanne]

Thank you for your reply, any suggestions on what to enter instead of "A1"? I know it cant be Me.D8.Text as this code is in Module 1 not the userform itself. I tried Taxinvoice.D8.Text but that gave me an error.

 

[Teeroy]

If you only load UserForm1 indirectly from the TaxInvoice Userform then you could use:

Private Sub Calendar1_DblClick()
TaxInvoice.D8.Text =  Calendar1.Value
Unload Me
End Sub

If you are using the UserForm1 calendar elsewhere then you'll need to check whether the TaxInvoice Userform is loaded and an object (probably a worksheet range or Custom Document Property) to hold the data if it is not.

 

[sueanne]

Thanks for your reply, if I am understanding correctly, if I need to be able to use the calendar in multiple userforms within the same workbook, and the taxinvoice userform is not going to be open at the same time as the other userforms using the calendar, then I need to reference it back to a cell on a worksheet under the module 1 code. Then in the specified userforms that I want it to be available, I need to put the coding in as per below and change the Me.D8.Text to wherever I want it on the relevant userform?

Is that right?

TaxInvoice Userform
Private Sub CommandButton2_Click()
Call lancia_cCalendar
Me.D8.Text = Sheet1.Cells(1, 1).Value
End Sub

Private Sub D8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim bRet As Boolean
Cancel = False
If IsDate(Me.D8.Text) Then
Me.D8.Text = CStr(DateValue(Me.D8.Text))
Else
If Len(Me.D8.Text) > 0 Then
Cancel = True
bRet = MsgBox("Not a valid date", vbCritical, "Date Entry Error")
End If
End If
End Sub