Help with code for data entry

[kantian1]

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.

[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]

[Bob Phillips]

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?

[kantian1]

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.

[kantian1]

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]

[kantian1]

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.

[Simon Lloyd]

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!

[Simon Lloyd]

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

[kantian1]

Hi Simon,

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

Thanks.

[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]

[kantian1]

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.

[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

[mikerickson]

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

[kantian1]

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]

[Simon Lloyd]

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!

[kantian1]

Hi Simon,

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

Thanks.

[Simon Lloyd]

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

[kantian1]

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'

[Simon Lloyd]

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?

[kantian1]

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.