Make my UserForm a "Multi-Tasker"

[underpressure]

This UserFormAddMembers code is used to add new members to Sheets(“Members”)
I wish to use the same UserForm to update Member Data when cells are empty.

Private Sub UserFormAddMembers_Initialize()
Dim i As Long

With Sheets("Members")
    For i = 2 To .Cells(Rows.Count, "C").End(xlUp).Row
        If .Cells(i, "C").Value = UserFormPost.ComboBox1.Value Then _
       
        'look for empty cells in Members DataBase columns D E F G H N
       
            TextBox1.Value = .Cells(i, "A").Value ’FirstName
            TextBox2.Value = .Cells(i, "B").Value ’LastName
            TextBox4.Value = .Cells(i, "D").Value ’Ph#
            TextBox7.Value = .Cells(i, "E").Value ’email
            TextBox8.Value = .Cells(i, "H").Value ’DOB
            TextBox11.Value = .Cells(i, "N").Value ’ID   
           
    End If
    Next i
 
End With
End Sub

So, I’m struggling with how to best integrate these ideas into the procedure:

If
.Cells(i, "D") is empty
.Cells(i, "E") is empty
.Cells(i, "H") is empty
.Cells(i, "N") is empty

Then
UserForm Label8.Caption = “Update Member Data”
SetFocus on the 1^st empty UserFormTextBox

Suggestions?

 

[dmt32]

Hi,
my first suggestion is that you should not rename events

Private Sub UserFormAddMembers_Initialize()


End Sub

The Initialize event is ALWAYS UserForm_Initialize regardless of the name you give the form

Second suggestion would be I think you are using the wrong event for what you want your code to do.

Try the following updated codes

Place ALL codes in your userforms code page

Dim rng As Range
Dim m As Variant
Dim wsMembers As Worksheet


Private Sub ComboBox1_Change()


    If Me.ComboBox1.List = -1 Then Exit Sub
    With wsMembers
        Set rng = .Cells(1, 3).Resize(.Cells(.Rows.Count, 3).End(xlUp).Row, 1)
    End With
    
    m = Application.Match(Me.ComboBox1.Value, rng, 0)
    
    If Not IsError(m) Then
        m = CLng(m)
'get selected record
           With wsMembers
                TextBox1.Value = .Cells(m, "A").Value 'FirstName
                TextBox2.Value = .Cells(m, "B").Value 'LastName
                TextBox4.Value = .Cells(m, "D").Value 'Ph#
                TextBox7.Value = .Cells(m, "E").Value 'email
                TextBox8.Value = .Cells(m, "H").Value 'DOB
                TextBox11.Value = .Cells(m, "N").Value 'ID
            End With
    End If
           
End Sub


Private Sub CommandButton1_Click()
'update record
    With wsMembers
        .Cells(m, "A").Value = TextBox1.Value   'FirstName
        .Cells(m, "B").Value = TextBox2.Value   'LastName
        .Cells(m, "D").Value = TextBox4.Value   'Ph#
        .Cells(m, "E").Value = TextBox7.Value   'email
        .Cells(m, "H").Value = TextBox8.Value   'DOB
        .Cells(m, "N").Value = TextBox11.Value  'ID
    End With
End Sub


Private Sub UserForm_Initialize()
'intialize variable
    Set wsMembers = ThisWorkbook.Worksheets("Members")
End Sub

It is assumed that you are populating your combobox via other code.
Selection made in combobox1 should select correct record from your worksheet & populate your textboxes

CommandButton1 updates the worksheet from the textboxes

Note the Variables at the TOP of the codes - these must sit at very top of your forms code page.

This is just intended as an example of how you could approach your project requirement - you will need to adapt as required.

Dave

 

[underpressure]

Dave,
WOW! Thanks for taking the time to provide the tutelage for this solution. You are quite generous!
Your approach is considerably more advanced than mine and will take some time to digest.
I'll attempt to modify my project with your suggestions...as soon as possible. It might take me a while.

I'm a bit overwhelmed.​