PDA

View Full Version : Help with code for data entry



kantian1
07-04-2008, 04:37 PM
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
07-04-2008, 05:27 PM
assuming the date is in combobox1 and the name in combobox2 and assuming your command button to enter is as below then:

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

Bob Phillips
07-05-2008, 01:15 AM
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
07-05-2008, 05:32 AM
assuming the date is in combobox1 and the name in combobox2 and assuming your command button to enter is as below then:

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

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
07-05-2008, 06:06 AM
assuming the date is in combobox1 and the name in combobox2 and assuming your command button to enter is as below then:

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

Hi Simon,

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


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

kantian1
07-05-2008, 06:10 AM
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
07-05-2008, 06:23 AM
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
07-05-2008, 06:26 AM
You probably need something like this in the combobox change:

Me.Combobox1.value=Format(Combobox1.value, "mmmm dd")

kantian1
07-05-2008, 06:39 AM
Hi Simon,

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

Thanks.

Simon Lloyd
07-05-2008, 08:12 AM
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:

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

kantian1
07-05-2008, 08:28 AM
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:

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

Hi Simon,

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

Thanks.

Simon Lloyd
07-05-2008, 08:29 AM
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
07-05-2008, 08:54 AM
Instead of searching for ComboBox1.Value , the search term could be DateValue(ComboBox1.Value)

kantian1
07-05-2008, 08:59 AM
Hi Simon,

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

See my code below:





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

Simon Lloyd
07-05-2008, 09:56 AM
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
07-05-2008, 10:10 AM
Hi Simon,

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

Thanks.

Simon Lloyd
07-05-2008, 10:20 AM
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
07-05-2008, 11:00 AM
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
07-05-2008, 11:45 AM
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
07-05-2008, 12:15 PM
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.

mdmackillop
07-05-2008, 01:29 PM
This is turning the entered data into text in the format March 07 (7th March)

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


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.

Simon Lloyd
07-05-2008, 01:40 PM
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.

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

kantian1
07-05-2008, 02:18 PM
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

Simon Lloyd
07-05-2008, 02:46 PM
Actually that code doesn't work well, swap it for this:

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

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!

kantian1
07-05-2008, 03:10 PM
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.

kantian1
07-06-2008, 07:34 AM
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.

Simon Lloyd
07-06-2008, 10:40 AM
I have it but never even opened it! you should start a new thread though!