• 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.

Help needed regarding VBA Code within Validation Funtion Date Drop Down List

A

ajayxlnc

New Member
Hello Excel Gurus,


I have a table where i have two Categories, in that i have sub categories also. I will capture the count on a daily basis for all the items (Categories) and by the end of the day i will take the sum of all those items separately. Now i want the same table to be used for the entire month (By Selecting the date in drop down). I have created a Date drop down Validation List. My Requirement is when i change the Date the contents in the Column A,B,D & E should remain constant and only the count in Column C & F should change (whenever the date is changed).


Please help me out in this regard. I tried many things but did'nt work out properly. Hope this will be solved and i will get some valuable help from you.


Sample data :


https://sites.google.com/site/ajayxlncs1/software/Sample%20Data.xlsx


ajayxlnc
 
Hi Ajay ,


Can you please clarify how you want the counts in columns C and F to change , when you change the date in A2 ?


Narayan
 
Thank You for your quick reply Narayank991.


Yes, you are right. When i change the date in A2, i want the counts in Columns C & F to Change. As shown in the sample data file for 13th Mar'12 the counts are already there and when i select the 14th Mar'12 tomorrow from the date drop, the count should be empty and i could be able to enter the values again. THe same for all the days in the month. When i want to check the historical data, i want to select the date and then the count should be displayed as per that days count which i have entered.


I am sorry if i am confusing you. I hope that you have understood the help i needed. If i am wrong please correct me.


Thank You in advance.
 
Hi Ajay ,


I am still confused !


You have shown the counts for the entered date of March 13 ; you say that when the date is changed through the drop-down in A2 , the counts should be cleared so that you can enter fresh values.


Then you mention that when you want to look at historical data , you will select the date , and then the count for that date should populate the cells in columns C and F ; from where will this historical data appear ?


Do you want each day's data to be stored in the same workbook ?


Can you please give more details ?


Narayan
 
Yes, I need each day's data to be stored in the same workbook. To be precise the whole month's data in the same work book. Is this possible ????


If not in this way is there any other alternative where i can achieve this kind of environment.


Thank You in advance.
 
Hi Ajay ,


Certainly it is possible.


You need two macros :


1. One to clear all count data from columns C and F ; this will happen whenever the date entered through the drop-down in A2 is for a date which is not already present in the workbook.


2. The other to retrieve existing data when the entered date is one for which data already exists in the workbook.


The date-wise data can be stored in the workbook , by entering each new date's data on a separate worksheet , which is named according to the date ; the only issue is how to decide when data entry is over , so that the data on the data entry sheet can be stored as a new worksheet ; probably a STORE DATA button can be given on the data entry worksheet ; when the user clicks this , a new worksheet tab is added and the data on the data entry sheet is copied to it.


Narayan
 
Thank You so much Narayan.


If possible can you please help me out in providing the code.


Looking forward for you co-operation.


Thank You in Advance..
 
Hi Ajay ,


Put the following code in the "Count" worksheet section of the VBA Project explorer.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Set date_range = Range("A2")
If Application.Intersect(Target, date_range) Is Nothing Then Exit Sub
Application.EnableEvents = False
selected_date = Format(date_range.Value, "mmm-dd")
name_found = check_name(selected_date)
If name_found Then
ThisWorkbook.Worksheets(selected_date).Activate
' The parameters in the next 4 statements are hard-coded
' 3 is because column C is column 3
' 6 is because column F is column 6
ActiveSheet.Range("C4", ActiveSheet.Cells(Rows.Count, 3).End(xlUp)).Copy
ThisWorkbook.Worksheets("Count").Range("C4").PasteSpecial xlValues
ActiveSheet.Range("F4", ActiveSheet.Cells(Rows.Count, 6).End(xlUp)).Copy
ThisWorkbook.Worksheets("Count").Range("F4").PasteSpecial xlValues
Else
Worksheets.Add
ActiveSheet.Name = selected_date
ThisWorkbook.Worksheets("Count").Cells.Copy
ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues
ActiveSheet.Range("A1").Select
End If
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Count").Activate
ActiveSheet.Range("A1").Select
Application.EnableEvents = True
End Sub

Public Function check_name(ByVal instring As String) As Boolean
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets(instring)
If ws Is Nothing Then check_name = False Else check_name = True
On Error GoTo 0
End Function
[/pre]

Whenever you select a date for which data does not exist in the workbook , a new worksheet will be added , and named "mmm-dd". Data from the "Count" worksheet will be copied and pasted into this new worksheet.


Whenever you select a date for which data already exists in the workbook , date from that worksheet will be copied and pasted into columns C and F ( starting with cells C4 and F4 ) of the "Count" worksheet.


Narayan
 
Hello Narayan,


Thanks a ton, it is working like a charm !!!!!


But i have few problems. Whenever i am selecting a date a new sheet is getting created but the count is the same for all the sheets (For all the dates). When i change the date the count as per that day has to be displayed in Column C & F but i can see the same count be copied in all the new sheets formed (when i changed the date) and not able to retrieve the historical data for the particular day.


Can you please make few changes like retrieving the historical data when the date is changed and also blank Column C & F when the date is changed so the i can enter the count manually. And is there any possibility that whenever a date is changed the new sheet created will be auto hidden automatically.

Adding another point i want all the manual entry to be done in the 'Count' sheet only.


I am sorry if i am asking too much.


Please help me out Narayan !!
 
Hi Ajay ,


Incorporate an ActiveX command button in your worksheet , on the tab labelled "Count" ; you can change its caption to something like "SAVE DATA" ; when the button is clicked , data on the "Count" tab will be saved to a newly added worksheet.


If a date is entered in A2 , data for which already exists in the workbook , the saved data will be retrieved into columns C and F ( starting with C4 and F4 ) of the "Count" tab.

[pre]
Code: 
Private Sub CommandButton1_Click()
Application.EnableEvents = False
Set date_range = Range("A2")
selected_date = Format(date_range.Value, "mmm-dd")
name_found = check_name(selected_date)
If Not name_found Then
Worksheets.Add
ActiveSheet.Name = selected_date
ThisWorkbook.Worksheets("Count").Cells.Copy
ActiveSheet.Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select
ThisWorkbook.Worksheets("Count").Activate
ActiveSheet.Range("A1").Select
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set date_range = Range("A2")
If Application.Intersect(Target, date_range) Is Nothing Then Exit Sub
Application.EnableEvents = False
selected_date = Format(date_range.Value, "mmm-dd")
name_found = check_name(selected_date)
If name_found Then
ThisWorkbook.Worksheets(selected_date).Activate
' The parameters in the next 4 statements are hard-coded
' 3 is because column C is column 3
' 6 is because column F is column 6
ActiveSheet.Range("C4", ActiveSheet.Cells(Rows.Count, 3).End(xlUp)).Copy
ThisWorkbook.Worksheets("Count").Range("C4").PasteSpecial xlValues
ActiveSheet.Range("F4", ActiveSheet.Cells(Rows.Count, 6).End(xlUp)).Copy
ThisWorkbook.Worksheets("Count").Range("F4").PasteSpecial xlValues
Else
ActiveSheet.Range("C4", ActiveSheet.Cells(Rows.Count, 3).End(xlUp)).ClearContents
ActiveSheet.Range("F4", ActiveSheet.Cells(Rows.Count, 6).End(xlUp)).ClearContents
End If
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Count").Activate
ActiveSheet.Range("A1").Select
Application.EnableEvents = True
End Sub

Public Function check_name(ByVal instring As String) As Boolean
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets(instring)
If ws Is Nothing Then check_name = False Else check_name = True
On Error GoTo 0
End Function
[/pre]

Narayan
 
Hello Sir,


Good Afternoon


Sir, i need the same code for date field because in some systems there date time settings or not equal to our work books so i think we create one VB Code for date field as like dd/mm/yyyy


So what ever the date field in their system but the date field is accept as per our code is given in the ranges and msg box "Date field must be dd/mm/yyyy vbok"
 
Hi Patnaik ,


I am not able to understand your requirement ; can you please send me an email explaining your requirements in detail ?


Narayan
 
You must log in or register to reply here.
Back
Top