Need Help with Excel VBA userform finding the first empty cell in any given sheet and pasting the data from the textbox?!?

[MatheMagician33]

I created a Userform that prompts the user to enter a proposers name and which disciplines they are proposing on. Each discipline has its own tab(worksheet) and each discipline has its own checkbox in the Userform. I want the userform to add the proposer to each discipline tab that has been checked. Here is what I have so far:

Sub NewProposer_Initialize()
'Empty txtboxProName
txtboxProName.Value = ""
'Uncheck DataCheckBoxes
chbAll.Value = False
chbAaPl.Value = False
chbEnaD.Value = False
chbPraF.Value = False
chbLand.Value = False
chbEnvA.Value = False
chbEaFA.Value = False
chbPFaS.Value = False
chbDGaM.Value = False
'Set Focus on txtboxProName
txtboxProName.SetFocus
End Sub
Sub cmdCancel_Click()
'Cancel
Unload Me
End Sub
Sub cmdCreate_Click()

If chbAll.Value = True Then
Worksheets("Sheet13").Range("A1").End(xlDown).Offset(1).Row 1 = txtboxProName.Value
End If
'copy the data to the database
'Worksheets("Sheet5").Range("A1").End(xlDown).Row 1 = txtboxProName.Value
'ElseIf chbAaPl = True Then Worksheets("Results" ^ "General" ^ "Analysis & Planning").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbEnaD = True Then Worksheets("Results" ^ "General" ^ "Engineering & Design").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbPraF = True Then Worksheets("Results" ^ "General" ^ "Process & Facilitation").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbLand = True Then Worksheets("Results" ^ "General" ^ "Land Use").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbEnvA = True Then Worksheets("Results" ^ "General" ^ "Environmental Analysis").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbEaFA = True Then Worksheets("Results" ^ "General" ^ "Economic & Financial Analysis").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbPFaS = True Then Worksheets("Results" ^ "General" ^ "Public Facilities & Services").Range("A1").Row 1 = txtboxProName.Value
'ElseIf chbDGaM = True Then Worksheets("Results" ^ "General" ^ "Data-Mapping-GIS").Range("A1").Row 1 = txtboxProName.Value
'Else: Me.Caption = "No Disciplines have been selected"

End Sub
Sub cmdClear_Click()
'Clear New Proposer Dialog
Call NewProposer_Initialize
End Sub
Sub NewProposer_Click()
'Show Userform
NewProposer.Show
End Sub


can anyone help?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I changed your command button macro to look like this:

Private Sub cmdCreate_Click()

    Dim ws As Worksheet
    Dim oCtrl As Control
    Dim NextRow As Long
    
    For Each oCtrl In Me.Controls
        If TypeName(oCtrl) = "CheckBox" Then
            If oCtrl.Value Then
                For Each ws In ThisWorkbook.Worksheets
                    If ws.CodeName = oCtrl.Name Then
                        With ws
                            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                            .Cells(NextRow, "A").Value = txtboxProName.Text
                        End With
                    End If
                Next
            End If
        End If
     Next

End Sub

That loops round the check boxes and also loops round the worksheets. To make that possible I changed the internal name, CodeName, of all my worksheets to match the names of the check boxes.

The worksheet code names are changed in the VB Editor by changing the entry in the worksheet properties just like you do to change control names. Then you can use it in the macro by using CodeName.

Basically, the macro loops round the controls and selects only the ones with a type of CheckBox.
When it finds one it looks to see if it's value = True
If so then it loops round the worksheets ooking for one with the same name as the checkbox.
Then it adds the Proposer to the worksheet.

I know your macro does something different but that should give you a start.

Also, don't underestimate the amount of work involved with using UserForms. The checking and data validation can get a bit complicated.

Regards,

 

[MatheMagician33]

Thank you RickXL for a great solution and a quick reply!!!

Here is what my final code looked like:

Private Sub NewProposer_Initialize()
    'Empty txtboxProName
    txtboxProName.Value = ""
    'Uncheck DataCheckBoxes
    chbAaPl.Value = False
    chbEnaD.Value = False
    chbPraF.Value = False
    chbLand.Value = False
    chbEnvA.Value = False
    chbEaFA.Value = False
    chbPFaS.Value = False
    chbDGaM.Value = False
    chbSDP.Value = False
    chbMDP.Value = False
    chbGen.Value = True
    'Set Focus on txtboxProName
    txtboxProName.SetFocus
End Sub
Private Sub chbMDP_Click()
    'Check all discipline boxes
    chbAaPl.Value = True
    chbEnaD.Value = True
    chbPraF.Value = True
    chbLand.Value = True
    chbEnvA.Value = True
    chbEaFA.Value = True
    chbPFaS.Value = True
    chbDGaM.Value = True
    chbGen.Value = True
    chbSDP.Value = False
End Sub
Private Sub chbSDP_Click()
    chbAaPl.Value = False
    chbEnaD.Value = False
    chbPraF.Value = False
    chbLand.Value = False
    chbEnvA.Value = False
    chbEaFA.Value = False
    chbPFaS.Value = False
    chbDGaM.Value = False
    chbGen.Value = True
    chbMDP.Value = False
End Sub
Private Sub cmdCancel_Click()
    'Cancel
    Unload Me
End Sub
Private Sub cmdCreate_Click()
    If chbSDP.Value = False And chbMDP.Value = False Then
        MsgBox ("A proposer type has not been selected, please select a proposer type")
    Else
        
        Dim ws As Worksheet
        Dim oCtrl As Control
        Dim NextRow As Long
    
        For Each oCtrl In Me.Controls
            If TypeName(oCtrl) = "CheckBox" Then
                If oCtrl.Value Then
                    For Each ws In ThisWorkbook.Worksheets
                        If ws.CodeName = oCtrl.Name Then
                            With ws
                                NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                                .Cells(NextRow, "A").Value = txtboxProName.Text
                            End With
                        End If
                    Next
                End If
            End If
        Next
        MsgBox ("The Proposer: " & Me.txtboxProName & " has been successfully added.")
        Call cmdCancel_Click
    
    End If
End Sub
Private Sub cmdClear_Click()
    'Clear New Proposer Dialog
     Call NewProposer_Initialize
End Sub
Private Sub NewProposer_Click()
     'Show Userform
     NewProposer.Show
End Sub
Private Sub chbGen_Click()
    chbGen.Value = True
End Sub

 

[RickXL]

No problem.

It looks like you have a good solution and thanks for the feedback.

Regards,