UserForm not Displaying Correctly

Hello again,

I have built a UserForm (Excel 2003) that looks up a Row dependant on Criteria and displays the values on the User Form. See Below

Private Sub UserForm_Initialize()
 
If Worksheets("Main Sheet").Range("E7") = ActiceCell Then RowNumber = 4
 
If Worksheets("Main Sheet").Range("E8") = ActiveCell Then RowNumber = 5
 
If Worksheets("Main Sheet").Range("E9") = ActiveCell Then RowNumber = 6
 
If Worksheets("Main Sheet").Range("E10") = ActiveCell Then RowNumber = 7
 
If Worksheets("Main Sheet").Range("E11") = ActiveCell Then RowNumber = 8
 
If Worksheets("Main Sheet").Range("E12") = ActiveCell Then RowNumber = 9
 
If Worksheets("Main Sheet").Range("E13") = ActiveCell Then RowNumber = 10
 
If Worksheets("Main Sheet").Range("E14") = ActiveCell Then RowNumber = 11
 
If Worksheets("Main Sheet").Range("E15") = ActiveCell Then RowNumber = 12
 
If Worksheets("Main Sheet").Range("E16") = ActiveCell Then RowNumber = 13
 
Worksheets("Sheet1").Select
 
GetData
 
End Sub

The UserForm then runs the following.

Private Sub GetData()
 
Dim r As Long
 
If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
 
Else
    ClearData
    MsgBox "You have reached the Maximum"
    Exit Sub
 
End If
 
If r > 3 And r <= 35 Then
TextBox1.Text = Cells(r, 1)
TextBox2.Text = Cells(r, 2)
TextBox3.Text = Cells(r, 3)
TextBox4.Text = Cells(r, 4)
TextBox5.Text = Cells(r, 5)
TextBox6.Text = Cells(r, 6)
TextBox7.Text = Cells(r, 7)
TextBox8.Text = Cells(r, 8)
TextBox9.Text = Cells(r, 9)
TextBox10.Text = Cells(r, 10)
TextBox11.Text = Cells(r, 11)
TextBox12.Text = Cells(r, 12)
TextBox13.Text = Cells(r, 13)
 
ElseIf r = 3 Then
    ClearData
    MsgBox "You have reached the Maximum"
 
End If
 
 
End Sub

The problem I'm having is that when running through the processes step by step it all works perfectly but when actualy running from a command button, its like it is to fast for it and the UserForm either does not display all the details or is a mixture of the current and previous Rows details.

Does anyone know why or how to remedy this?


Regards

Hello Strugglin,

Move your code from the UserForm_Initialize() event procedure to the UserForm_Activate() procedure. Use the initialize procedure to load values into TextBoxes, Comboxes, and ListBoxes. Use the Activate event for dynamic data. What is happening is the data changes but not in sync with the UserForm events. Because the UserForm is busy, it doesn't see the repaint command.

Hello Strugglin,

Obviously you have not made the proper offerings at the VBA alter and the gods are angry! Can you post your workbook for review. It would be the quickest way to resolve these issues.

It looks like you may have a variable 7 a button with the same name - RowNumber. This may cause problems.

Your activate code could be shorter, what about

Private Sub UserForm_Activate()
Dim RowNumber As Long
If ActiveCell.Column = 5 Then RowNumber = ActiveCell.Row - 3
GetData
End Sub

If you are using the button RowNumber then you need to RowNumber.Caption, not just RowNumber.

I'll take a closer look at the form later.

Hello strugglin,

OK, I got it working. There were a few errors. Here is the macro for the "Add Contact" button. I removed the code to hide the UserForm. Let the user dismiss the form when they ready.

Private Sub CommandButton1_Click()

    'AddContract.Hide
    
    With Worksheets("Sheet1")
      StartRow = 3
      NextRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
      NextRow = IIf(NextRow < StartRow, StartRow, NextRow)
      .Cells(1, 10).Value = Me.DTPicker1.Value
      .Range("A1:M1").Copy _
        Destination:=.Cells(NextRow, "A")
      .Range("A1:M1").Clear
    End With
        
End Sub

I changed the name of the TextBox called "RowNumber" to some other name.