Help with code for data entry

Hi,

I have to design a data entry program for a 'time sheet tracker' in Excel; unfortunately my VBA skills are still very limited.

I am trying to create a data entry form so when a certain name and date is entered; a 'received' text is entered into that specific cell to show that the time sheet has been received from that employee for that date.

So if I entered 'Joe blobs' as the name then picked the week ending date from the date picker, and clicked the 'Add Entry' Button, the program would zero in on that cell and automatically add a 'Received' text in white with a green background.

I have attached a screen shot of what I am trying to do.

The image shows tables that are split into months; one of the tables is expanded.

I have designed the data entry form with one combo box which has a drop down with all the employees listed, a date picker and two buttons.

All I need now is the good stuff, the code!

Any help would be appreciated and any little tips like clearing the data entry boxes when an entry has been added, stuff like that.

Thanks.

assuming the date is in combobox1 and the name in combobox2 and assuming your command button to enter is as below then:
[vba]
Sub CommandButton2_Click()
Dim rFound As Long
Dim Rng As Range
Dim RecDate As Range
rFound = Range("C:C").Find(What:=Userform1.ComboBox2.Value, After:=Range("C1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
Set Rng = Range(Cells(rFound - 1, 3), Cells(rFound - 1, Columns.Count).End(xlToLeft))
MsgBox Rng.Address
For Each RecDate In Rng
If RecDate = UserForm1.ComboBox1.Value Then
RecDate.Offset(1, 0).Value = "Recieved"
End If
Next RecDate

End Sub
[/vba]

Why don't you just have data validation dropdowns in reach of the date/name intersections with the list of allowable values and so away with the combo and VBA?

Quote:

---

Originally Posted by Simon Lloyd

assuming the date is in combobox1 and the name in combobox2 and assuming your command button to enter is as below then:
[vba]
Sub CommandButton2_Click()
Dim rFound As Long
Dim Rng As Range
Dim RecDate As Range
rFound = Range("C:C").Find(What:=Userform1.ComboBox2.Value, After:=Range("C1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
Set Rng = Range(Cells(rFound - 1, 3), Cells(rFound - 1, Columns.Count).End(xlToLeft))
MsgBox Rng.Address
For Each RecDate In Rng
If RecDate = UserForm1.ComboBox1.Value Then
RecDate.Offset(1, 0).Value = "Recieved"
End If
Next RecDate

End Sub
[/vba]

---

Thanks Simon, I just pasted your code in but I recieved an error 'Run Time error 91' 'Object Variable or with block variable not set'

Is this something to do ranges? Where do I source my ranges from for each of the combo boxes?

Can't I use a date picker instead of the combo box?

Thanks.

Quote:

---

Originally Posted by Simon Lloyd

assuming the date is in combobox1 and the name in combobox2 and assuming your command button to enter is as below then:
[vba]
Sub CommandButton2_Click()
Dim rFound As Long
Dim Rng As Range
Dim RecDate As Range
rFound = Range("C:C").Find(What:=Userform1.ComboBox2.Value, After:=Range("C1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
Set Rng = Range(Cells(rFound - 1, 3), Cells(rFound - 1, Columns.Count).End(xlToLeft))
MsgBox Rng.Address
For Each RecDate In Rng
If RecDate = UserForm1.ComboBox1.Value Then
RecDate.Offset(1, 0).Value = "Recieved"
End If
Next RecDate

End Sub
[/vba]

---

Hi Simon,

This is what my code is looking like at the moment:

[VBA]
Private Sub ComboBox1_Change()


End Sub

Private Sub ComboBox2_Change()

End Sub

Private Sub CommandButton1_Click()

Dim rFound As Long
Dim Rng As Range
Dim RecDate As Range
rFound = Range("C:C").Find(What:=UserForm1.ComboBox2.Value, After:=Range("C1"), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
Set Rng = Range(Cells(rFound - 1, 3), Cells(rFound - 1, Columns.Count).End(xlToLeft))
MsgBox Rng.Address
For Each RecDate In Rng
If RecDate = UserForm1.ComboBox1.Value Then
RecDate.Offset(1, 0).Value = "Recieved"
End If
Next RecDate

End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Quit button!"
End If
End Sub
[/VBA]

Quote:

---

Originally Posted by xld

Why don't you just have data validation dropdowns in reach of the date/name intersections with the list of allowable values and so away with the combo and VBA?

---

I am trying to learn it a bit more about VBA, and I would prefer a form type interface.

Thanks.

Kantian, xld is correct in the fact that inbuilt functions in Excel perform faster and are a lot more robust than VBA, aside from that VBA contributes to the size of your workbook. As for the problems you are having - i have no idea what it is, i created the code purely on guess work as you didn't attach a workbook so you got my best shot without it!

You probably need something like this in the combobox change:
[VBA]
Me.Combobox1.value=Format(Combobox1.value, "mmmm dd")
[/VBA]

Hi Simon,

I've attached the workbook, see if you can work it out.

Thanks.

You'll need to sort your list of dates or allow direct entry by changing Match Required to False on combobox2 but this worked fine in my test:
[vba]
Private Sub CommandButton1_Click()
Dim rFound As Long, dFound As Long
Dim Rng As Range
Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")
rFound = Range("C22:C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=UserForm1.ComboBox1.Value, _
After:=Range("C22"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
dFound = Cells.Find(What:=UserForm1.ComboBox2.Value, After:=Range("C22"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Column
Cells(rFound, dFound).Value = "Recieved"
Unload Me

End Sub
[/vba]

Quote:

---

Originally Posted by Simon Lloyd

You'll need to sort your list of dates or allow direct entry by changing Match Required to False on combobox2 but this worked fine in my test:
[vba]
Private Sub CommandButton1_Click()
Dim rFound As Long, dFound As Long
Dim Rng As Range
Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")
rFound = Range("C22:C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=UserForm1.ComboBox1.Value, _
After:=Range("C22"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
dFound = Cells.Find(What:=UserForm1.ComboBox2.Value, After:=Range("C22"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Column
Cells(rFound, dFound).Value = "Recieved"
Unload Me

End Sub
[/vba]

---

Hi Simon,

Still did not work for me? How are you entering the date into the combo box?

Thanks.

Quote:

---

Originally Posted by Simon Lloyd

You'll need to sort your list of dates or allow direct entry by changing Match Required to False on combobox2

---

As i said......
Dates entered in this format 05/07/2008

Instead of searching for ComboBox1.Value , the search term could be DateValue(ComboBox1.Value)

Hi Simon,

I am still getting this 'run time error', where am I going wrong?

See my code below:




[VBA]
Private Sub ComboBox1_Change()

End Sub

Private Sub ComboBox2_Change()

Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")

End Sub

Private Sub CommandButton1_Click()
Dim rFound As Long, dFound As Long
Dim Rng As Range
Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")
rFound = Range("C22:C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=UserForm1.ComboBox1.Value, _
After:=Range("C22"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
dFound = Cells.Find(What:=UserForm1.ComboBox2.Value, After:=Range("C22"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Column
Cells(rFound, dFound).Value = "Recieved"
Unload Me

End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Quit button!"
End If

End Sub
[/VBA]

Get rid of both the combobox changes! and as long as you performed the tasks i mentioned in 2 previous posts it should work fine as it did for me in your sample workbook!

Hi Simon,

Can you post (attach) your version of the program to this forum so I can see it?

Thanks.

I have created a Dynamic Named Range on sheet 1 of all the unique employee names from 2007-2008 sheet, to add employees just add them to the bottom of the list and then sort a-z, the named range is called EmployeeNames, enter a date in this format xx/xx/xxxx

Hi Simon,

I copied the names out of sheet1, and pasted them into column C on the 2007-2008 worksheet and sorted them a-z.

Still no joy, I'm getting a different error now?

What am I doing wrong?

Thanks.

Sorry I get what you mean now, but I am still getting an error? 'Run Time error 91' 'Object Variable or with block variable not set'

Why copy the names from sheet1? that was your lookup list, sheet 1 needed to stay complete with names! i had no errors with that workbook i selected a name then typed a date in this format xx/xx/2008 and it worked perfect! can't help you any more than that!

Did you try the one i uploaded before modifying it?

Sorry I get what you mean now, but I am still getting an error? 'Run Time error 91' 'Object Variable or with block variable not set'

I have tried it as you posted it and it still gives me the above error and sometimes it gives me nothing and refers me back to VBA with no error message.

Try 'Paul Hunt' 20/07/2008 and see what happens for you.

How can it work for you and not me?

Thanks.

This is turning the entered data into text in the format March 07 (7th March)
[VBA]
Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")
[/VBA]

Column 1 (why are you searching all cells?) contains date values, shown in the format Mar-07 (March 2007)

Not surprisingly, nothing is found hence the error.

Firstly Paul Hunt doesn't exist for that combination, secondly the code was only for the worksheet that the button resided on, thirdly your second sheet was protected!
This code will do what you need, provided sheet1 is still in place with the names.
[VBA]
Private Sub CommandButton1_Click()
Dim Sht As Worksheet
Dim rFound As Range, dFound As Range
Dim rRow As Long, dCol As Long
Dim Rng As Range
Dim OriginalSheet As String
OriginalSheet = ActiveSheet.Name
Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")
Application.ScreenUpdating = False
For Each Sht In Sheets
Sht.Select
Sht.Unprotect
If Sht.Name = "Sheet1" Then GoTo Nxt
On Error Resume Next
Set dFound = Cells.Find(What:=Me.ComboBox2.Value, After:=Range("C22"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
On Error GoTo 0
If Not dFound Is Nothing Then
dCol = dFound.Column
Else
MsgBox "Date Not Found on Sheet " & Sht.Name
GoTo Nxt
End If
On Error Resume Next
Set rFound = Range("C22:C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=Me.ComboBox1.Value, _
After:=Range("C22"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
On Error GoTo 0
If Not rFound Is Nothing Then
rRow = rFound.Row
Else
MsgBox "Name Not Found On Sheet " & Sht.Name
GoTo Nxt
End If
Cells(rRow, dCol).Value = "Recieved"

Nxt:
Sht.Protect
Next Sht


Sheets(OriginalSheet).Activate
Application.ScreenUpdating = True
Unload Me

End Sub
[/VBA]

Simon, you are a genius!

It works great now, but only when I actually enter 'april 1st' into the combo box instead of 01/04/2008? This is not a problem.

Just a couple of more things, how can I get the program to enter a GREEN background when it adds the white received text?

Also, how do I get rid of that sheet protection code if I need to?

Thanks again, I appreciate all you time and effort and I can only hope to be as proficient as you in these skills, one

Actually that code doesn't work well, swap it for this:
[VBA]
Private Sub commandbutton1_click()
Dim Sht As Worksheet
Dim rFound As Range, dFound As Range
Dim rRow As Long, dCol As Long
Dim Rng As Range
Dim OriginalSheet As String
OriginalSheet = ActiveSheet.Name
Me.ComboBox2.Value = Format(ComboBox2.Value, "mmmm dd")
Application.ScreenUpdating = False
For Each Sht In Sheets
Sht.Select
Sht.Unprotect
If Sht.Name = "Sheet1" Then GoTo Nxt
On Error Resume Next
Set dFound = Cells.Find(What:=Me.ComboBox2.Value, After:=Range("C22"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
On Error GoTo 0
If Not dFound Is Nothing Then
dCol = dFound.Column
Else
MsgBox "Date Not Found on Sheet " & Sht.Name
GoTo Nxt
End If
On Error Resume Next
Set rFound = Range("C" & dFound.Row - 1 & ":C" & Range("C" & Rows.Count).End(xlUp).Row).Find(What:=Me.ComboBox1.Value, _
After:=Range("C" & dFound.Row - 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
On Error GoTo 0
If Not rFound Is Nothing Then
rRow = rFound.Row
Else
MsgBox "Name Not Found On Sheet " & Sht.Name
GoTo Nxt
End If
Cells(rRow, dCol).Value = "Recieved"
Cells(rRow, dCol).Interior.ColorIndex = 4

Nxt:
Sht.Protect
Next Sht


Sheets(OriginalSheet).Activate
Application.ScreenUpdating = True
Unload Me

End Sub

[/VBA]Ive added changing the cell to green but you might want to change the number for a darker green!

Protection code: just remove it from top and bottom of my code (as that is where it is now handled) then go to each sheet and click on unprotect before running the code again!

Hi Simon,

Sorry to bother you again, but try 'Mick Burrows' july 6th 2008, for some reason it does not add a 'received?

Thanks.

Sorry it works with your new code.

Hi again Simon,

What's your experience with Visual Studio 2008?

I have created and Excel Addin with an Office Ribbon and a button on it.

I want to click that button on the ribbon and launch the Tracker form you helped me with, but I don't know the code within Visual Studio 2008 to execute that.

Any thoughts?

Thanks.

I have it but never even opened it! you should start a new thread though!