userform mistake

[rjmdc]

i am doing something wrong. i am reusing a userform and table and d want to pass it on but somehow all the previous code is erroring on A4 select then xldown
where is my mistake?

Private Sub cboActions_Change()
    If Me.TextBoxActions.Value = "" Then
        Me.lblActionsProvided.ForeColor = vbRed
    Else
        Me.lblActionsProvided.ForeColor = vbBlack
    End If
End Sub
___________________________________________________
Private Sub Clear_Click()
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "ComboBox" Or TypeName(ctrl) = "TextBox" Then
            ctrl.Value = ""
                  
        End If
    Next ctrl
    
            Me.txtDateToday.Value = Format(Date, "mmmm dd, yyyy")
             Me.CboParticipants.SetFocus
    
    End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub
______________________________________________
Private Sub Enter_Click()

    ActiveWorkbook.Worksheets("Log").Select
        Range("A4").Select
        
    If ActiveCell.Value = "" Then
       ActiveCell.Select
    Else
       Selection.End(xlDown).Offset(1, 0).Select
     End If
     
     If Me.CboParticipants.Value = "" Then
        Me.lblParticipantsName.ForeColor = vbRed
        Me.CboParticipants.SetFocus
     Exit Sub
     End If
     
     If Me.TextBoxActions.Value = "" Then
        Me.lblActionsProvided.ForeColor = vbRed
        Me.TextBoxActions.SetFocus
     Exit Sub
     End If
    
        ActiveCell.Value = Me.CboParticipants
        ActiveCell.Offset(0, 1).Value = Me.txtDateToday
        ActiveCell.Offset(0, 2).Value = Me.TextBoxActions
        ActiveCell.Offset(0, 3).Value = Me.TextBoxFollowUp

         Call Clear_Click
         Me.lblActionsProvided.ForeColor = vbBlack
         Me.lblParticipantsName.ForeColor = vbBlack
         
         Call cmdClose_Click
   End Sub
_______________________________________

Private Sub cboNameofParticipant_Change()
     If Me.CboParticipants.Value = "" Then
        Me.lblParticipantsName.ForeColor = vbRed
     Else
        Me.lblParticipantsName.ForeColor = vbBlack
     End If
End Sub

Private Sub UserForm_Initialize()

    Me.CboParticipants.List = Worksheets("Participants").Range("Participants").Value
       
    Me.txtDateToday = Format(Date, "mmmm dd, yyyy")
    
    Me.CboParticipants.SetFocus
        
End Sub

 

[aRandomHelper]

somehow all the previous code is erroring on A4 select then xldown

When you select A4, and it's blank, you're selecting A4 again -- this is not necessary.
When it's not blank, let's say A4:A10 is filled, the code jumps to A10, then select offset 1 cell (A11) -- this should work fine.
When it's not blank, only A4 is filled, the code jumps to last row A1048576, then select offset 1 cell (out of the worksheet limit) -- this probably gave you the error.

A better way is to go bottom-up.
Change these:

        Range("A4").Select
        
    If ActiveCell.Value = "" Then
       ActiveCell.Select
    Else
       Selection.End(xlDown).Offset(1, 0).Select
     End If

to these:

With Range("A" & Rows.Count).End(xlUp)
    If .Row < 4 Then
        Range("A4").Select
    Else
        .Offset(1).Select
    End If
End With

 

[rjmdc]

hi
that works however
my output was set as a table
row 4 is the first blank row
your code ignores row 4 and begins in row 5 NOT as part of the table

 

[rjmdc]

i filled one row then set as a table and it works
thanks for the help

 

[aRandomHelper]

A table would work slightly differently.

Try changing to this:

With ActiveSheet.ListObjects(1).DataBodyRange
    If .Range("A" & .Rows.Count).Value = "" Then
        .Range("A" & .Rows.Count).End(xlUp).Offset(1).Select
    Else
        ActiveSheet.ListObjects(1).ListRows.Add
        .Range("A" & .Rows.Count).Offset(1).Select
    End If
End With

Assuming that's the first table on that worksheet, and it should add the table row properly if it's all filled up.

 

[rjmdc]

thanmks that works perfectly
why does my original workbook work with xldown and this one doesnt?

can it be that my original book already was a table with 7000 entries?

 

[aRandomHelper]

why does my original workbook work with xldown and this one doesnt?

Can't really pinpoint the problem here without access to the workbook. But my guess is it's a mixture of the problem I mentioned in my first post here plus a weird relation/conflict between regular worksheet range vs table's Data Body Range.

Bottom line is, when dealing with tables, it's best to use ListObject to refer to them, rather than using the worksheet Range object. And, when trying to look for the "last row" of a range, it's usually better to go bottom-up than top-down.