Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 29

Thread: Solved: User Form Problems

  1. 03-01-2008, 01:49 PM #1
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location

    Solved: User Form Problems

    When the last row of information is deleted from the listbox via the "Delete" button the listbox gets the following error...
    "Run-time error '380': Could not set the RowSorce propert. Invalid property value."

    [VBA]Private Sub cmdDel_Click()
    With frmRequest.ListBox1
    'Check for selected item
    If (.Value <> vbNullString) Then

    'If more then one data rows
    If (.ListIndex >= 0 And xlLastRow("Leave Request") > 2) Then
    Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
    'Update listbox
    .RowSource = "'Leave Request'!A2:E" & xlLastRow("Leave Request")

    'If only one data row
    ElseIf (.ListIndex = 0 And xlLastRow("Leave Request") = 2) Then
    Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
    'Update listbox
    .RowSource = "'Leave Request'!A2:E"
     End If
    Else
    MsgBox "Please Select Data"

    End If
    End With
    End Sub[/VBA]

    The columns in the listbox are Name, Requested, Type, Start, and End. Once the listbox reloads the columns are set to Column A, Column B Column C, Column D, and Column E and the the first row in the listbox is Name, Requested, Type, Start, and End. How can I keep this from happening and keep the columns set as Name, Requested, Type, Start, and End???

    Issue number two; how can I have the listbox update immediately after information is added, removed or edited via the userform "frmRequest".

    Issue number three; if the last row of information is deleted from the listbox and then a new row is added this new information doesn't fill the Edit Selection form, "UserForm4"???

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  2. 03-01-2008, 01:57 PM #2
    tstav
    tstav is offline
    VBAX Mentor tstav's Avatar
    Joined
    Feb 2008
    Location
    Athens
    Posts
    350
    Location
    The "red part" misses the Row coordinate. It should be
    [VBA]
    .RowSource = "'Leave Request'!A2:E" & xlLastRow("Leave Request")
    [/VBA]
    He didn't know it was impossible, so he did it. (Jean Cocteau)
    Reply With Quote Reply With Quote
  3. 03-01-2008, 02:12 PM #3
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    quicker to use

    [vba]

    .RowSource = "'Leave Request'!A2:E2"
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  4. 03-01-2008, 02:17 PM #4
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Thanks "tstav" that takes care of the problem of Delete function. Is there a better way of keeping the headers set to "Name, Requested, Type, Start, and End"???

    I also can't figure out why the listbox also doesn't load the "Edit Selection"???

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  5. 03-01-2008, 02:22 PM #5
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Thanks "xld", but isn't that what I have already???

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  6. 03-01-2008, 02:35 PM #6
    tstav
    tstav is offline
    VBAX Mentor tstav's Avatar
    Joined
    Feb 2008
    Location
    Athens
    Posts
    350
    Location
    Sorry Charlie, gotto go...
    I' ll be with you again tomorrow.

    tstav
    He didn't know it was impossible, so he did it. (Jean Cocteau)
    Reply With Quote Reply With Quote
  7. 03-01-2008, 02:37 PM #7
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    No you are missing the 2. tstav gave you code to work out the row to insert, but the test had already determined that it was 2, so it is wasteful to run the test again. In fact the best code would be this IMO

    [vba]

    Private Sub cmdDel_Click()
    Dim mpLastRow As Long

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

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

    Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
    'Update listbox
    .RowSource = "'Leave Request'!A2:E" & mpLastRow
    Else

    MsgBox "Please Select Data"
    End If
    End If
    End With
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  8. 03-01-2008, 02:50 PM #8
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Sorry for doubting you . It works great. Is there a reason that you can see why the edit form dosn't load the information???

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  9. 03-01-2008, 03:01 PM #9
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Found my own error: If (.ListIndex >= 0 And xlLastRow("Leave Request") > 2) Then

    changed the 2 to a 1 and it works correctly.

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

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  10. 03-01-2008, 05:37 PM #10
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Another problem has come up. I've taken the worksheet "Leave Request and hidden it and when a new request is submitted through the userform "frmRequest" it does everything it is suppose to except update the listbox on the userform "frmRquest". Here's the coding and the workbook....


    Best regards,

    Charlie


    [VBA]Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Leave Request")

    Sheets("Leave Request").Visible = True
    Sheets("Leave Request").Select

    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    'check for a name
    If Trim(Me.cboName.Value) = "" Then
    Me.cboName.SetFocus
    MsgBox "Please enter your name"
    Exit Sub
    End If

    'check for a type
    If Trim(Me.cboType.Value) = "" Then
    Me.cboType.SetFocus
    MsgBox "Please enter requested leave"
    Exit Sub
    End If

    'check for a start
    If Trim(Me.txtStart.Value) = "" Then
    Me.txtStart.SetFocus
    MsgBox "Please enter start date"
    Exit Sub
    End If

    'check for a end
    If Trim(Me.txtEnd.Value) = "" Then
    Me.txtEnd.SetFocus
    MsgBox "Please enter end date"
    Exit Sub
    End If

    'copy the data to the database
    Application.EnableEvents = True
    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
    Application.EnableEvents = False

    'clear the data
    Me.cboName.Value = ""
    Me.cboType.Value = ""
    Me.txtStart.Value = ""
    Me.txtEnd.Value = ""
    Me.cboName.SetFocus

    Sheets("Leave Request").Visible = False

    End Sub[/VBA]
    Reply With Quote Reply With Quote
  11. 03-01-2008, 05:58 PM #11
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    [vba]

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Leave Request")

    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    'check for a name
    If Trim(Me.cboName.Value) = "" Then
    Me.cboName.SetFocus
    MsgBox "Please enter your name"
    Exit Sub
    End If

    'check for a type
    If Trim(Me.cboType.Value) = "" Then
    Me.cboType.SetFocus
    MsgBox "Please enter requested leave"
    Exit Sub
    End If

    'check for a start
    If Trim(Me.txtStart.Value) = "" Then
    Me.txtStart.SetFocus
    MsgBox "Please enter start date"
    Exit Sub
    End If

    'check for a end
    If Trim(Me.txtEnd.Value) = "" Then
    Me.txtEnd.SetFocus
    MsgBox "Please enter end date"
    Exit Sub
    End If


    Sheets("Leave Request").Visible = True
    Sheets("Leave Request").Select

    'copy the data to the database
    Application.EnableEvents = False
    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
    Me.ListBox1.RowSource = "'Leave Request'!A2:E" & xlLastRow("Leave Request")
    Application.EnableEvents = True

    'clear the data
    Me.cboName.Value = ""
    Me.cboType.Value = ""
    Me.txtStart.Value = ""
    Me.txtEnd.Value = ""
    Me.cboName.SetFocus

    Sheets("Leave Request").Visible = False

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  12. 03-01-2008, 06:10 PM #12
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    "xld" that does work and takes care of the problem, but it loads up so fast that the userform "frmRequest unloads so quickly on to the "Leave Request" worksheet that the date/time (formatted: dd-mmm-yyy hh:mm:ss) stamp doesn't fill in column "B"???


    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  13. 03-01-2008, 06:29 PM #13
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    The date and time stamp in column "B" on the "Leave Request" worksheet runs on a Worksheet_Change Event:

    Best regards,

    Charlie

    [VBA]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[/VBA]
    Reply With Quote Reply With Quote
  14. 03-02-2008, 06:09 AM #14
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    I'm getting an hour glass icon when I run the following after the date fills the textbox on the userform. If you click else where on the user form the hour glass disappears. Is there a way to get rid of the hour glass???

    I also have the rremaining problems listed in threads 11-13. any ideas on these?

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
  15. 03-02-2008, 06:10 AM #15
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Here's the coding that's causing the hour glass icon:

    Charlie

    [VBA]Private Sub txtEnd_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Load frmCalendar
    frmCalendar.ocxCalendar.Value = Now()
    If Not frmCalendar.UserCancelled Then
    If IsDate(frmCalendar.ocxCalendar.Value) Then
    txtEnd.Text = Format(frmCalendar.ocxCalendar.Value, "dd-mmm-yyyy")
    End If
    End If
    Unload frmCalendar
    txtEnd.SelStart = 1
    txtEnd.SelLength = Len(txtEnd.Text)

    End Sub[/VBA]
    Reply With Quote Reply With Quote
  16. 03-02-2008, 06:10 AM #16
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Here's the coding that's causing the hour glass icon:

    Charlie

    [VBA]Private Sub txtEnd_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Load frmCalendar
    frmCalendar.ocxCalendar.Value = Now()
    If Not frmCalendar.UserCancelled Then
    If IsDate(frmCalendar.ocxCalendar.Value) Then
    txtEnd.Text = Format(frmCalendar.ocxCalendar.Value, "dd-mmm-yyyy")
    End If
    End If
    Unload frmCalendar
    txtEnd.SelStart = 1
    txtEnd.SelLength = Len(txtEnd.Text)

    End Sub[/VBA]
    Reply With Quote Reply With Quote
  17. 03-02-2008, 11:54 AM #17
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    I've added this coding in red and it seems to have taken care of the problems, but I don't think it's really the correct way of coding???

    Best regards,

    Charlie

    [vba]Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Leave Request")

    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    'check for a name
    If Trim(Me.cboName.Value) = "" Then
    Me.cboName.SetFocus
    MsgBox "Please enter your name"
    Exit Sub
    End If

    'check for a type
    If Trim(Me.cboType.Value) = "" Then
    Me.cboType.SetFocus
    MsgBox "Please enter requested leave"
    Exit Sub
    End If

    'check for a start
    If Trim(Me.txtStart.Value) = "" Then
    Me.txtStart.SetFocus
    MsgBox "Please enter start date"
    Exit Sub
    End If

    'check for a end
    If Trim(Me.txtEnd.Value) = "" Then
    Me.txtEnd.SetFocus
    MsgBox "Please enter end date"
    Exit Sub
    End If

    Sheets("Leave Request").Visible = True
    Sheets("Leave Request").Select

    'copy the data to the database
    Application.EnableEvents = True
    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
    Application.EnableEvents = False
    'clear the data
    Me.cboName.Value = ""
    Me.cboType.Value = ""
    Me.txtStart.Value = ""
    Me.txtEnd.Value = ""
    Me.cboName.SetFocus

    Unload Me

    Sheets("Leave Request").Visible = False

    frmRequest.Show

    End Sub[/vba]
    Reply With Quote Reply With Quote
  18. 03-02-2008, 05:42 PM #18
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    I've got everything right now working the way I want it to except one item!!!!! The value from the textbox "txtEnd" from the userform "frmRequest" isn't unloading on to the worksheet "Leave Request" the first three boxes are unloading correctly. If I get this issued cleared up everything is okay to go.....PLEASE HELP!!!!!

    Best Regards,

    Charlie

    [VBA]Private Sub cmdAdd_Click()
    Dim strLastRow As Integer
    'Get last row
    strLastRow = xlLastRow("Leave Request")

    Sheets("Leave Request").Visible = True
    Sheets("Leave Request").Select

    With frmRequest
    'If textboxes not null then fill data of textboxes to worksheet.
    If (.cboName.Value <> vbNullString And .cboType.Value <> vbNullString And _
    .txtStart.Value <> vbNullString And .txtEnd.Value <> vbNullString) Then
    Cells(strLastRow + 1, 1).Value = frmRequest.cboName.Value
    Cells(strLastRow + 1, 3).Value = frmRequest.cboType.Value
    Cells(strLastRow + 1, 4).Value = frmRequest.txtStart.Value
    Cells(strLastRow + 1, 5).Value = frmRequest.txtEnd.Value
    strLastRow = strLastRow + 1

    'Update listbox with added values
    frmRequest.ListBox1.RowSource = "'Leave Request'!A2:E2" & strLastRow

    'Empty textboxes
    .cboName.Value = vbNullString
    .cboType.Value = vbNullString
    .txtStart.Value = vbNullString
    .txtEnd.Value = vbNullString

    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("Leave Request").Visible = False
    End Sub[/VBA]
    Reply With Quote Reply With Quote
  19. 03-02-2008, 06:08 PM #19
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Nothing obvious, post the workbook.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber
    Reply With Quote Reply With Quote
  20. 03-02-2008, 06:12 PM #20
    coliervile
    coliervile is offline
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Here you go... I still get the hour glass when I put the dates in the txtStart and txtEnd boxes???

    Best regards,

    Charlie
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules