• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need guidance to write a code to take date from useform3.calender1

Ajinkya

Member
Hi Masters,


Some one could please help me?


i'm not able to take date or write a code for selection of date into

Sheet name "Data" column "E" with the help userform3.calender1


sample data and file is uploaded....address is


https://hotfile.com/dl/162557126/7b42acc/Data_Entry.xlsm.html


thanks a lot in advance
 
Hi Ajinkya ,


1. Remove the following statement from the procedure CommandButton1_Click() in the code for UserForm1 :


Sheets("Data").Cells(nextrow, 5) = UserForm3.Calendar1.Value


2. Introduce the following statements in the procedure CommandButton1_Click() in the code for UserForm3 :


nextrow = WorksheetFunction.CountA(Sheets("Data").Range("A:A")) + 1

Sheets("Data").Cells(nextrow, 5) = Calendar1.Value


Narayan
 
The other way would be:

1. Insert a module and declare following line:

Code:
Global glOdDate As DataObject


2. Userform3.Commandbutton1's event:

[pre][code]Private Sub CommandButton1_Click()
Set glOdDate = New DataObject 'This object holds data
glOdDate.SetText Me.Calendar1.Value 'Here the calendar value gets copied to clipboard
Unload Me
End Sub
I have added Unload Me so that hitting commandbutton exits event


3. Userform1.Commandbutton1's event [where data gets pasted to clipboard]:

Private Sub CommandButton1_Click()
Dim nextrow As Integer
'=COUNTA (A:A)+1
nextrow = WorksheetFunction.CountA(Sheets("Data").Range("A:A")) + 1

Sheets("Data").Cells(nextrow, 1) = Now
Sheets("Data").Cells(nextrow, 2) = UserForm1.ComboBox1.Value
Sheets("Data").Cells(nextrow, 3) = UserForm1.ComboBox2.Value
Sheets("Data").Cells(nextrow, 4) = UserForm1.TextBox1.Value
Sheets("Data").Cells(nextrow, 5) = glOdDate.GetText(1) 'We get copied data from UF3

Set glOdDate = Nothing 'Release object

Unload UserForm1
End Sub[/code][/pre]
 
Hi Ajinkya ,


Can you check this uploaded file ?


http://speedy.sh/83fu2/Data-Entry.xlsm


It is your original file , modified to take care of data entry from the calendar.


Narayan
 
Narayan,


What if user hits 'cancel' button instead of 'update' button on Userform1 [The date entry will appear without any relevant data from UF1]?


Since entry is from another Userform, the inputting part is asynchronous which may lead to incorrect data placement.
 
Hi Shrivallabha ,


Thanks for pointing out a mistake.


What I find is that , as you say , when the CANCEL button is pressed , the selected date is entered in the first blank cell in column E.


However , if you repeat this process any number of times , the same cell will be overwritten with whatever fresh dates are selected , since the updating procedure is looking at column A to decide the value of the "nextrow" variable.


Therefore , whenever the user does decide to enter genuine data and selects UPDATE , the junk date which was pasted earlier in column E , will now be overwritten with the user-entered date.


Your point is taken , but the OP has to decide whether this problem needs to be taken care of , or whether he can live with it.


Thanks once again.


Narayan
 
Back
Top