Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2016
    Posts
    3

    Unhappy Answered: EXCEL USERFORM - Command Button "Find next data entry" fails

    Hi,

    I'm struggling with the following code. For some reasons I can't attach the Excel Workbook to this email.
    I have created a Userform including 3 command buttons: Update Database, Find Next entry, Find Previous entry. I can't get my head around the issue. Find next/ previous entry stumble over this code line "lastrow = Sheets("AWP_UPDATE").Range("A" & Rows.Count).End(xlUp).Row"
    While Update database doesn't do anything... Still a bit green in the World of macros... Thanks for your help.

    Private Sub cmdNext_Click()
    lastrow = Sheets("AWP_UPDATE").Range("A" & Rows.Count).End(xlUp).Row
    currentrow = currentrow + 1
    If currentrow = lastrow + 1 Then
    MsgBox ("You have reached the last row of data!")
    currentrow = lastrow
    End If
    txtSub_Activity.Text = Cells(currentrow, 1).Text
    txtLEADName.Text = Cells(currentrow, 2).Text
    txtActual_Start.Text = Cells(currentrow, 3).Text
    txtActual_Completion.Text = Cells(currentrow, 4).Text
    txtComments_on_progress.Text = Cells(currentrow, 6).Text
    txtQ1.Text = Cells(currentrow, 7).Text
    txtQ2.Text = Cells(currentrow, 8).Text
    txtQ3.Text = Cells(currentrow, 9).Text
    txtQ4.Text = Cells(currentrow, 10).Text
    txtAW2018.Text = Cells(currentrow, 11).Text
    cbxStatus1.Value = Cells(currentrow, 12).Value
    cbxStatus0.Value = Cells(currentrow, 12).Value
    End Sub


    Private Sub cmdUpdate_Click()
    Dim Sub_Activity As String, LEADName As String, Actual_Start As String, Actual_Completion As String, Comment_on_progress As String, Q1 As String, Q2 As String, Q3 As String, Q4 As String, AW2018 As String, Status As String
    txtSub_Activity.Text = Cells(currentrow, 1).Text
    txtLEADName.Text = Cells(currentrow, 2).Text
    txtActual_Start.Text = Cells(currentrow, 3).Text
    txtActual_Completion.Text = Cells(currentrow, 4).Text
    txtComments_on_progress.Text = Cells(currentrow, 6).Text
    txtQ1.Text = Cells(currentrow, 7).Text
    txtQ2.Text = Cells(currentrow, 8).Text
    txtQ3.Text = Cells(currentrow, 9).Text
    txtQ4.Text = Cells(currentrow, 10).Text
    txtAW2018.Text = Cells(currentrow, 11).Text
    cbxStatus1.Value = Cells(currentrow, 12).Value
    cbxStatus0.Value = Cells(currentrow, 12).Value

    End Sub

    Private Sub cmdPrevious_Click()
    currentrow = currentrow - 1
    If currentrow > 1 Then
    txtSub_Activity.Text = Cells(currentrow, 1).Text
    txtLEADName.Text = Cells(currentrow, 2).Text
    txtActual_Start.Text = Cells(currentrow, 3).Text
    txtActual_Completion.Text = Cells(currentrow, 4).Text
    txtComments_on_progress.Text = Cells(currentrow, 6).Text
    txtQ1.Text = Cells(currentrow, 7).Text
    txtQ2.Text = Cells(currentrow, 8).Text
    txtQ3.Text = Cells(currentrow, 9).Text
    txtQ4.Text = Cells(currentrow, 10).Text
    txtAW2018.Text = Cells(currentrow, 11).Text
    cbxStatus1.Value = Cells(currentrow, 12).Value
    cbxStatus0.Value = Cells(currentrow, 12).Value
    ElseIf currentrow = 1 Then
    MsgBox "Now you are in the header row!"
    currentrow = currentrow + 1
    End If
    End Sub

  2. Best Answer
    Posted by weejas

    "You need to zip Excel files before you can attach them.

    I could be wrong, but I think that
    Code:
    Sheets("AWP_UPDATE").Range("A" & Rows.Count).End(xlUp).Row
    Will always return 1, assuming that the range is fully populated.
    Try:
    Code:
    Private Sub cmdNext_Click()
    lastrow = Application.WorksheetFunction.Counta(Sheets("AWP_UPDATE").Range("A:A")) - 1
    currentrow = currentrow + 1
    If currentrow > lastrow Then
    MsgBox ("You have moved past the last row of data - moving back")
    currentrow = lastrow
    End If
    
    'Repetitive code moved to a subroutine
    UpdateForm currentrow
    
    End Sub
    
    
    Private Sub cmdUpdate_Click()
    Dim Sub_Activity As String
    Dim LEADName As String
    Dim Actual_Start As String
    Dim Actual_Completion As String
    Dim Comment_on_progress As String
    Dim Q1 As String
    Dim Q2 As String
    Dim Q3 As String
    Dim Q4 As String
    Dim AW2018 As String
    Dim Status As String
    
    'I've moved the variable declarations onto individual lines for readability.
    'They're not used in this code - I assume that you'll do something with them later?
    
    'Your code wasn't updating the spreadsheet because it's still set to pull data from the current worksheet into the userform.
    Cells(currentrow, 1).Text = txtSub_Activity.Text
    Cells(currentrow, 2).Text = txtLEADName.Text
    Cells(currentrow, 3).Text = txtActual_Start.Text
    Cells(currentrow, 4).Text = txtActual_Completion.Text
    Cells(currentrow, 6).Text = txtComments_on_progress.Text
    Cells(currentrow, 7).Text = txtQ1.Text
    Cells(currentrow, 8).Text = txtQ2.Text
    Cells(currentrow, 9).Text = txtQ3.Text
    Cells(currentrow, 10).Text = txtQ4.Text
    Cells(currentrow, 11).Text = txtAW2018.Text
    'Not sure how this will work with your actual data - given that you're pulling one cell's data into two controls, 
    'the logical assumption is that you want to push the contents of both controls into the same cell.  Unless you 
    'concatenate it, the sheet will always contain the value of the control that appears second in the code's execution.
    Cells(currentrow, 12).Value = cbxStatus1.Value
    Cells(currentrow, 12).Value = cbxStatus0.Value
    
    End Sub
    
    Private Sub cmdPrevious_Click()
    
    if currentrow = 2 Then Exit Sub 'You can break this into a message if you prefer
    
    currentrow = currentrow - 1
    
    'Repetitive code moved to a subroutine
    UpdateForm currentrow
    
    End Sub
    
    Sub UpdateForm(SourceRow as Long)
    
    txtSub_Activity.Text = Cells(SourceRow , 1).Text
    txtLEADName.Text = Cells(SourceRow , 2).Text
    txtActual_Start.Text = Cells(SourceRow , 3).Text
    txtActual_Completion.Text = Cells(SourceRow , 4).Text
    txtComments_on_progress.Text = Cells(SourceRow , 6).Text
    txtQ1.Text = Cells(SourceRow , 7).Text
    txtQ2.Text = Cells(SourceRow , 8).Text
    txtQ3.Text = Cells(SourceRow , 9).Text
    txtQ4.Text = Cells(SourceRow , 10).Text
    txtAW2018.Text = Cells(SourceRow , 11).Text
    cbxStatus1.Value = Cells(SourceRow , 12).Value
    cbxStatus0.Value = Cells(SourceRow , 12).Value
    
    End Sub
    I've set up a few such forms, and generally give them navigation buttons to the first and last records as well as next and previous. It's possible to code them so that they don't let the users try to navigate beyond the bounds of the data, and so that if they try to move away from a record that has been changed but not saved, a warning appears."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    You need to zip Excel files before you can attach them.

    I could be wrong, but I think that
    Code:
    Sheets("AWP_UPDATE").Range("A" & Rows.Count).End(xlUp).Row
    Will always return 1, assuming that the range is fully populated.
    Try:
    Code:
    Private Sub cmdNext_Click()
    lastrow = Application.WorksheetFunction.Counta(Sheets("AWP_UPDATE").Range("A:A")) - 1
    currentrow = currentrow + 1
    If currentrow > lastrow Then
    MsgBox ("You have moved past the last row of data - moving back")
    currentrow = lastrow
    End If
    
    'Repetitive code moved to a subroutine
    UpdateForm currentrow
    
    End Sub
    
    
    Private Sub cmdUpdate_Click()
    Dim Sub_Activity As String
    Dim LEADName As String
    Dim Actual_Start As String
    Dim Actual_Completion As String
    Dim Comment_on_progress As String
    Dim Q1 As String
    Dim Q2 As String
    Dim Q3 As String
    Dim Q4 As String
    Dim AW2018 As String
    Dim Status As String
    
    'I've moved the variable declarations onto individual lines for readability.
    'They're not used in this code - I assume that you'll do something with them later?
    
    'Your code wasn't updating the spreadsheet because it's still set to pull data from the current worksheet into the userform.
    Cells(currentrow, 1).Text = txtSub_Activity.Text
    Cells(currentrow, 2).Text = txtLEADName.Text
    Cells(currentrow, 3).Text = txtActual_Start.Text
    Cells(currentrow, 4).Text = txtActual_Completion.Text
    Cells(currentrow, 6).Text = txtComments_on_progress.Text
    Cells(currentrow, 7).Text = txtQ1.Text
    Cells(currentrow, 8).Text = txtQ2.Text
    Cells(currentrow, 9).Text = txtQ3.Text
    Cells(currentrow, 10).Text = txtQ4.Text
    Cells(currentrow, 11).Text = txtAW2018.Text
    'Not sure how this will work with your actual data - given that you're pulling one cell's data into two controls, 
    'the logical assumption is that you want to push the contents of both controls into the same cell.  Unless you 
    'concatenate it, the sheet will always contain the value of the control that appears second in the code's execution.
    Cells(currentrow, 12).Value = cbxStatus1.Value
    Cells(currentrow, 12).Value = cbxStatus0.Value
    
    End Sub
    
    Private Sub cmdPrevious_Click()
    
    if currentrow = 2 Then Exit Sub 'You can break this into a message if you prefer
    
    currentrow = currentrow - 1
    
    'Repetitive code moved to a subroutine
    UpdateForm currentrow
    
    End Sub
    
    Sub UpdateForm(SourceRow as Long)
    
    txtSub_Activity.Text = Cells(SourceRow , 1).Text
    txtLEADName.Text = Cells(SourceRow , 2).Text
    txtActual_Start.Text = Cells(SourceRow , 3).Text
    txtActual_Completion.Text = Cells(SourceRow , 4).Text
    txtComments_on_progress.Text = Cells(SourceRow , 6).Text
    txtQ1.Text = Cells(SourceRow , 7).Text
    txtQ2.Text = Cells(SourceRow , 8).Text
    txtQ3.Text = Cells(SourceRow , 9).Text
    txtQ4.Text = Cells(SourceRow , 10).Text
    txtAW2018.Text = Cells(SourceRow , 11).Text
    cbxStatus1.Value = Cells(SourceRow , 12).Value
    cbxStatus0.Value = Cells(SourceRow , 12).Value
    
    End Sub
    I've set up a few such forms, and generally give them navigation buttons to the first and last records as well as next and previous. It's possible to code them so that they don't let the users try to navigate beyond the bounds of the data, and so that if they try to move away from a record that has been changed but not saved, a warning appears.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Dec 2016
    Posts
    3
    Thank you Weejas, this is helpful.

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  6. #5
    Join Date
    Oct 2017
    Posts
    1
    Quote Originally Posted by weejas View Post
    You need to zip Excel files before you can attach them.

    I could be wrong, but I think that
    Code:
    Sheets("AWP_UPDATE").Range("A" & Rows.Count).End(xlUp).Row
    Will always return 1, assuming that the range is fully populated.
    Try:
    Code:
    Private Sub cmdNext_Click()
    lastrow = Application.WorksheetFunction.Counta(Sheets("AWP_UPDATE").Range("A:A")) - 1
    currentrow = currentrow + 1
    If currentrow > lastrow Then
    MsgBox ("You have moved past the last row of data - moving back")
    currentrow = lastrow
    End If
    
    'Repetitive code moved to a subroutine
    UpdateForm currentrow
    
    End Sub
    
    
    Private Sub cmdUpdate_Click()
    Dim Sub_Activity As String
    Dim LEADName As String
    Dim Actual_Start As String
    Dim Actual_Completion As String
    Dim Comment_on_progress As String
    Dim Q1 As String
    Dim Q2 As String
    Dim Q3 As String
    Dim Q4 As String
    Dim AW2018 As String
    Dim Status As String
    
    'I've moved the variable declarations onto individual lines for readability.
    'They're not used in this code - I assume that you'll do something with them later?
    
    'Your code wasn't updating the spreadsheet because it's still set to pull data from the current worksheet into the userform.
    Cells(currentrow, 1).Text = txtSub_Activity.Text
    Cells(currentrow, 2).Text = txtLEADName.Text
    Cells(currentrow, 3).Text = txtActual_Start.Text
    Cells(currentrow, 4).Text = txtActual_Completion.Text
    Cells(currentrow, 6).Text = txtComments_on_progress.Text
    Cells(currentrow, 7).Text = txtQ1.Text
    Cells(currentrow, 8).Text = txtQ2.Text
    Cells(currentrow, 9).Text = txtQ3.Text
    Cells(currentrow, 10).Text = txtQ4.Text
    Cells(currentrow, 11).Text = txtAW2018.Text
    'Not sure how this will work with your actual data - given that you're pulling one cell's data into two controls, 
    'the logical assumption is that you want to push the contents of both controls into the same cell.  Unless you 
    'concatenate it, the sheet will always contain the value of the control that appears second in the code's execution.
    Cells(currentrow, 12).Value = cbxStatus1.Value
    Cells(currentrow, 12).Value = cbxStatus0.Value
    
    End Sub
    
    Private Sub cmdPrevious_Click()
    
    if currentrow = 2 Then Exit Sub 'You can break this into a message if you prefer
    
    currentrow = currentrow - 1
    
    'Repetitive code moved to a subroutine
    UpdateForm currentrow
    
    End Sub
    
    Sub UpdateForm(SourceRow as Long)
    
    txtSub_Activity.Text = Cells(SourceRow , 1).Text
    txtLEADName.Text = Cells(SourceRow , 2).Text
    txtActual_Start.Text = Cells(SourceRow , 3).Text
    txtActual_Completion.Text = Cells(SourceRow , 4).Text
    txtComments_on_progress.Text = Cells(SourceRow , 6).Text
    txtQ1.Text = Cells(SourceRow , 7).Text
    txtQ2.Text = Cells(SourceRow , 8).Text
    txtQ3.Text = Cells(SourceRow , 9).Text
    txtQ4.Text = Cells(SourceRow , 10).Text
    txtAW2018.Text = Cells(SourceRow , 11).Text
    cbxStatus1.Value = Cells(SourceRow , 12).Value
    cbxStatus0.Value = Cells(SourceRow , 12).Value
    
    End Sub
    I've set up a few such forms, and generally give them navigation buttons to the first and last records as well as next and previous. It's possible to code them so that they don't let the users try to navigate beyond the bounds of the data, and so that if they try to move away from a record that has been changed but not saved, a warning appears.
    Thank you, I had the same problem and it helped.

Tags for this 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
  •