PDA

View Full Version : Solved: Vacation Request Not Downloading into Worksheet



coliervile
02-16-2008, 01:22 PM
"XLD" HELP! :help The following worksheet tracks the vacation request for employees. I've accomplished this with the help from "XLD". I've run into one problem...it seems when I added this macro to the worksheet, "Leave Request"...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("A:A").Column Then
Cells(.Row, "B").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
Next Cell

If Not Intersect(Target, Me.Range("B:B", "E:E")) Is Nothing Then

Me.Columns("A:E").Sort key1:=Me.Range("D2"), order1:=xlAscending, _
key2:=Me.Range("B2"), order2:=xlAscending, _
header:=xlYes
End If
End Sub

and new data is added to in between two existing rows from the userform onto the worksheet the "End" date doesn't go into column "E". The "End" date in column "E" remains empty or blank. It only seems to happen when new data is added and goes between data that's already on the worksheet. When "CC" was added to the worksheet from the user form and it was placed in between "AA" and "BB" the "End" date didn't go into column "E".

It's frustrating that I can't figure out where there conflict is restricting the "End" date from downloading into column "E". :banghead: :banghead: :banghead:

You're help is appreciated,

Charlie
















</IMG></IMG></IMG></IMG>

coliervile
02-16-2008, 01:23 PM
Oooops here's the file....

Charlie

Bob Phillips
02-16-2008, 02:08 PM
The sort is confusing the code. What you should do is to turn event off before adding to the worksheet for columns C:E, then turn the events on and add column A.

coliervile
02-16-2008, 02:10 PM
How do you just turn the event on and off for each column?

Best regards,

Charlie

coliervile
02-16-2008, 03:17 PM
I was trying to add another userform to the Leave Request userform that activate when the user clicked onto the Submit Request command button on the Leave Request userform that would prompt a Sort userform that must be used before exiting both userforms....does that make sense. I couldn't get the Sort userform to load up when clicking onto the Submit Request command button on the Leave Request userform. Please take a look and see if you can tell me where I went wrong. I do have another sort command button on the face of the worksheet "Leave Request" that does work, but would rather have the Sort userform attached to the Submit Request command button.

Best regards,

Charlie

coliervile
02-16-2008, 03:49 PM
I figured it out at the end of the Private Sub for the command button Submit Request I added the following...

'clear the data
Me.cboName.Value = ""
Me.cboType.Value = ""
Me.txtStart.Value = ""
Me.txtEnd.Value = ""
Me.cboName.SetFocus
frmSort.Show
End Sub

...when the user clicks on the Submit Request it unloads the userform and opens the Sort userform and prompts the user to click on the Sort command button. Does anyone have a better idea?

Best regards,

Charlie

Bob Phillips
02-16-2008, 04:50 PM
No, that's rubbish.

This is wht I was suggesting.

Chang



'copy the data to the database
ws.Cells(iRow, 1).Value = Me.cboName.Value
ws.Cells(iRow, 3).Value = Me.cboType.Value
ws.Cells(iRow, 4).Value = Me.txtStart.Value
ws.Cells(iRow, 5).Value = Me.txtEnd.Value


to this



'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 3).Value = Me.cboType.Value
ws.Cells(iRow, 4).Value = Me.txtStart.Value
ws.Cells(iRow, 5).Value = Me.txtEnd.Value
Application.EnableEvents = True
ws.Cells(iRow, 1).Value = Me.cboName.Value

coliervile
02-16-2008, 04:54 PM
Okay I'll give that a try...Ole VBA Wizard. Thanks for your help with this.

Best regards,

Charlie

coliervile
02-16-2008, 07:52 PM
Thanks again XLD you did a great job and another posting SOLVED.

Have a great day.

Best regards,

Charlie