PDA

View Full Version : Solved: Can Someone Explain Why This is Happening



coliervile
03-12-2008, 01:41 PM
In the following woorkbook employees can submit time-off from work. Everything else seems to correctly with the excpetions of the one thing. The Edit (UserForm4) portion on the Administrative Form doesn't unload correctly. When the user originially submits their request on the Employee Leave Request Form everything unloads proprerly onto the worksheet "Leave Request". When the information is edited via the Edit (UserForm4) portion on the Administrative Form I found a glitch and can't figure out why it's happening. I opened the Administrative Form (on the Dashboard) and then clicked on to the Edit Selection and opened the Userform4 and edited the Name- "CC"- Start- date to Mar-13-2008 and when I hit the Submit Changes button the Start date unloaded on to the Leave Request worksheet as mmm-dd-yyyy and not as Mar-13-2008?

It seems to me that there's some sort of date/number format issue that I can't firgure out between UserForm4 and the ListBox1. Could someone please take a look and explain to me where the problem is. I can't afford to pull out any more hair!!! LOL :bug: :dunno

coliervile
03-12-2008, 01:59 PM
Here's the workbook...

stapuff
03-12-2008, 02:01 PM
Charlie - can you post the code.

coliervile
03-12-2008, 02:07 PM
Stapuff here's the coding for UserForm4...

Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With frmRequest.ListBox1
'Check for selected item
If (.Value <> vbNullString) Then
Range(.RowSource)(.ListIndex + 1, 1).Value = UserForm4.TextBox1.Value
Range(.RowSource)(.ListIndex + 1, 2).Value = UserForm4.TextBox2.Value
Range(.RowSource)(.ListIndex + 1, 3).Value = UserForm4.TextBox3.Value
Range(.RowSource)(.ListIndex + 1, 4).Value = UserForm4.TextBox4.Value
Range(.RowSource)(.ListIndex + 1, 5).Value = UserForm4.TextBox5.Value

Else
MsgBox "Please Enter Data"

End If
End With
'End With
Unload Me
With Worksheets("Leave Request")
.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes
End With
Range("A1").Select
Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Unload Me
Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub

Private Sub Userform_Initialize()
With frmRequest.ListBox1
'Check for selected item
If (.Value <> vbNullString) Then

'If more then one data rows
If (.ListIndex >= 0 And xlLastRow("Leave Request") > 1) Then

UserForm4.TextBox1.Value = Range(.RowSource)(.ListIndex + 1, 1).Value
UserForm4.TextBox2.Value = Range(.RowSource)(.ListIndex + 1, 2).Value
UserForm4.TextBox3.Value = Range(.RowSource)(.ListIndex + 1, 3).Value
UserForm4.TextBox4.Value = Range(.RowSource)(.ListIndex + 1, 4).Value
UserForm4.TextBox5.Value = Range(.RowSource)(.ListIndex + 1, 5).Value

End If
End If
End With
'Unload Me
End Sub

Bob Phillips
03-12-2008, 02:38 PM
I told you this before in the earlier thread.

The Leave Request worksheet



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, "mmm-dd-yyyy hh:mm:ss")
Cells(.Row, "D").NumberFormat = "mmm-dd-yyyy"
Cells(.Row, "E").NumberFormat = "mmm-dd-yyyy"
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


Userform4



Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With frmRequest.ListBox1
'Check for selected item
If (.Value <> vbNullString) Then

Range(.RowSource)(.ListIndex + 1, 1).Value = UserForm4.TextBox1.Value
Range(.RowSource)(.ListIndex + 1, 2).Value = UserForm4.TextBox2.Value
Range(.RowSource)(.ListIndex + 1, 3).Value = UserForm4.TextBox3.Value
Range(.RowSource)(.ListIndex + 1, 4).Value = CDate(UserForm4.TextBox4.Value)
Range(.RowSource)(.ListIndex + 1, 5).Value = CDate(UserForm4.TextBox5.Value)

Else

MsgBox "Please Enter Data"
End If
End With

Unload Me

With Worksheets("Leave Request")

.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes
End With

Sheets("Dashboard").Select
Application.ScreenUpdating = True
End Sub

coliervile
03-12-2008, 02:50 PM
How are you today Bob (XLD)? Listening to any good tunes? Thanks for pointing put my error....I was certain I had made the necessary changes before. I obviously didn't follow through on something allog the way. :hammer: Sometimes this is what it takes....

Have a good day.

Bob Phillips
03-12-2008, 02:57 PM
I'm good Charlie, watching Wainwright's Walks today and listening to some good old bys from your neck of the woods.

I am sure that you made those corrections too, it solved a previous problem. It must have got lost in a later change.

Still not using meaningful cotrol names yet though.

coliervile
03-12-2008, 03:26 PM
Which one of those "good ole boy" are you listening too??? I'll be gradually correctly the control names. Have a good evening.

Bob Phillips
03-12-2008, 04:18 PM
Ricky Skaggs, Dolly, the Louvins, The Stanley Brothers, and more besides.

coliervile
03-12-2008, 04:35 PM
Good tunes have a great evening. :friends: