VBA with Dates

[fekish]

Hello,

i am not very literate in VBA but through google i managed to make a database style worksheet for my sister to record inventory stock.

What i cannot figure out now is the selection of date.
When we click NEW STOCK then there are some fields to complete. Is there a way to have also a field with now date to be automatically added?

For now the person should select from the drop down list the date, but i dont mind either if the date is automatically put there, or if the date is automatically put the other sheet where the data is stored.
If in the end the date is selected from the list, is there a way to show the actual date on the form after is selected, rather than just numbers?

The code for now is like this:

Private Sub EntertheData_Click()
Dim RowCount As Long
Dim ctl As Control

' Chexk user input
If Me.Quantity.Value = "" Then
MsgBox "Please enter the quantity if shoes", vbExclamation, "Staff Expenses"
Me.Quantity.SetFocus
Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("Sale").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Sale").Range("B1")
.Offset(RowCount, 1).Value = Me.Quantity.Value
.Offset(RowCount, 2).Value = Me.Shoes.Value
.Offset(RowCount, 3).Value = Me.Size.Value
.Offset(RowCount, 4).Value = Me.Number.Value
.Offset(RowCount, 5).Value = Me.DateNow.Value

End With

' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

 

[Norie]

If you want the current date in a textbox on the form you can simply use something like this.

Me.DateNow.Value = Date() ' use Now() if the time is needed to

Not sure where you would put that, perhaps if you have button that's clicked to add a new record it should go in it's code.

 

[fekish]

Thanks, that is super!!

 

[fekish]

Can i ask something else in order not to open a new topic?

I have a list of names, telephones, emails.
I have created a check where a pop up comes up; you enter the mobile number, then when you press ok, and then a cell tells you if we have the person in database or not.
Additionally I have created a form that pop-ups in order to fill in the three above categories and when you click done these are entered in a list in a sheet.

My question: is there a way to have a similar form as the second one, in order instead of all cells being empty, to show me for the mobile number that i will click the name and email that correspond to it? In order to show instantly if there are any missing information and add them to the list? Something like a vlookup but in vba ....

 

[Norie]

Why not use the same form?

 

[fekish]

Thanks for the reply, the reason:
1. I want to have a check first if the mobile number is actually in database. Thus because i didnt know how to create a pop with a "yes" or "no", i created anotehr one just to write the number in a cell, and then show in an excel cell if the number is in database or not.
The best thing that i wanted but i didnt know how to do it was:
a form to say "enter number", then a pop up to say "this number is in database" or "this number is not in database". Then on both of these to have another button to click and have another form to enter any other details...

2. Then, is there actually a way for excel to check that that number is actually in database and show in the form the other details that are already in database?

 

[Norie]

What I was thinking was one userform.

It would have a combobox with a list of all the mobile numbers in the database.

If the user enters an existing number or picks one from the dropdown then the rest it the details fir that number are filled in.

If they enter a number not in the database they are asked if they want to add the number to the database.

If they choose yes they fill out the rest of the form.

Once they are finished doing that they click a button and all the data is added3 to the database.

If they choose no the form us reset.

 

[fekish]

Exactly that is what i wanted!

But i don't know how to do it!

 

[Norie]

Could you post some sample data?

 

[fekish]

Basically the code for checking if new customer is in database is the below.

I tried to use differnt if commands that i found in internet but i couldnt. What i wanted is to press check customer and then a box to appear saying if customer is in database or not. I already have a cell in excel that shows if is database or not, so i was linking the if command to that cell...


Private Sub CheckCustomer_Click()
Dim RowCount As Long
Dim ctl As Control

' Chexk user input

If Me.MobileNumber.Value = "" Then
MsgBox "Please enter mobile number of customer", vbExclamation, "Staff Expenses"
Me.MobileNumber.SetFocus
Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("Customer Check").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Customer Check").Range("B1")
.Offset(RowCount, 0).Value = Me.MobileNumber.Value

End With

' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub