So I recently managed to figure out how to finally add data to multiple worksheets depending on what value the combo box was selected.
E.g. Worksheets PL531e, PL931 & PL968. If you selected PL931 from the combobox, inputed your data into the text boxes and clicked the "Save Data" button it would add the inputted data to the worksheet "PL931" & vice versa if you picked PL968 or PL531e it would save the inputted data directly into the relevant worksheet.
I have a listbox at the bottom of my userform, which currently displays the data only in the 1st 2 rows (Headers & 1st row of data).
If there is no data in the worksheet and I click the "Save Data" button the listbox will automatically update and show me the data I've entered; however if I decide to add additional data it will not display any other rows bar rows 1 & 2 whereby row 1 is the headers & row 2 is the 1st row of data.
I know the data is being saved to the spreadsheet because when I close the form itself, and the view the relevant spreadsheet, the data is there, just the listbox doesn't want to display anymore rows than the 1st two.
Can anyone explain to me how I can modify my code to fix this issue (See code below)?
Note: Please ignore the layout and messiness of the code, I prefer working to get the code working before then looking for more efficient replacements.
Option Explicit
Dim iExit As VbMsgBoxResult
'Dim updateRow As Integer
Private Sub ComboBox1_Change()
Dim iRow As Long
iRow = [Counta(Overview!D:D)]
With DataEntry
.TestNo.Value = ""
.NeuronID.Value = ""
.DateCode.Value = ""
.TextBox4.Value = ""
.TextBox5.Value = ""
.TextBox6.Value = ""
.TextBox7.Value = ""
.TextBox8.Value = ""
.TextBox9.Value = ""
.TextBox10.Value = ""
.TextBox11.Value = ""
.TextBox12.Value = ""
.TextBox13.Value = ""
.TextBox14.Value = ""
.TextBox15.Value = ""
.TextBox16.Value = ""
.TextBox17.Value = ""
If ComboBox1.Value = "PL531e" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
.lstDatabase.ColumnCount = 17
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL531e!A1:Q" & iRow
Else
.lstDatabase.RowSource = "PL531e!A2:Q2"
End If
Label10.Caption = Sheets("PL531e").Cells(1, 9)
Label11.Caption = Sheets("PL531e").Cells(1, 10)
Label12.Caption = Sheets("PL531e").Cells(1, 11)
Label13.Caption = Sheets("PL531e").Cells(1, 12)
Label14.Caption = Sheets("PL531e").Cells(1, 13)
Label15.Caption = Sheets("PL531e").Cells(1, 14)
Label16.Caption = Sheets("PL531e").Cells(1, 15)
Label17.Caption = Sheets("PL531e").Cells(1, 16)
Label18.Caption = Sheets("PL531e").Cells(1, 17)
ElseIf ComboBox1.Value = "PL931" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 13
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL931!A1:M" & iRow
Else
.lstDatabase.RowSource = "PL931!A2:M2"
End If
Label10.Caption = Sheets("PL931").Cells(1, 9)
Label11.Caption = Sheets("PL931").Cells(1, 10)
Label12.Caption = Sheets("PL931").Cells(1, 11)
Label13.Caption = Sheets("PL931").Cells(1, 12)
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "PL968" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 10
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL968!A1:J" & iRow
Else
.lstDatabase.RowSource = "PL968!A2:J2"
End If
Label10.Caption = Sheets("PL968").Cells(1, 9)
Label11.Caption = Sheets("PL968").Cells(1, 10)
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "PN410X" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 10
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PN410X!A1:J" & iRow
Else
.lstDatabase.RowSource = "PN410X!A2:J2"
End If
Label10.Caption = Sheets("PN410X").Cells(1, 9)
Label11.Caption = Sheets("PN410X").Cells(1, 10)
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "PN510" Then
TextBox9.Visible = False
TextBox10.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 8
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PN510!A1:H" & iRow
Else
.lstDatabase.RowSource = "PN510!A2:H2"
End If
Label10.Caption = ""
Label11.Caption = ""
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
ElseIf ComboBox1.Value = "GL100" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
.lstDatabase.ColumnCount = 10
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "GL100!A1:J" & iRow
Else
.lstDatabase.RowSource = "GL100!A2:J2"
End If
Label10.Caption = Sheets("GL100").Cells(1, 9)
Label11.Caption = Sheets("GL100").Cells(1, 10)
Label12.Caption = ""
Label13.Caption = ""
Label14.Caption = ""
Label15.Caption = ""
Label16.Caption = ""
Label17.Caption = ""
Label18.Caption = ""
End If
End With
End Sub
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim AddNew As Range
If ComboBox1.Value = "PL531e" Then
Set sh = ThisWorkbook.Sheets("PL531e")
ElseIf ComboBox1.Value = "PL931" Then
Set sh = ThisWorkbook.Sheets("PL931")
ElseIf ComboBox1.Value = "PL968" Then
Set sh = ThisWorkbook.Sheets("PL968")
ElseIf ComboBox1.Value = "PN410X" Then
Set sh = ThisWorkbook.Sheets("PN410X")
ElseIf ComboBox1.Value = "PN510" Then
Set sh = ThisWorkbook.Sheets("PN510")
ElseIf ComboBox1.Value = "GL100" Then
Set sh = ThisWorkbook.Sheets("GL100")
End If
Set AddNew = sh.Range("A6536").End(xlUp).Offset(1, 0)
AddNew.Offset(0, 0).Value = TestNo.Text
AddNew.Offset(0, 1).Value = NeuronID.Text
AddNew.Offset(0, 2).Value = DateCode.Text
AddNew.Offset(0, 3).Value = TextBox4.Text
AddNew.Offset(0, 4).Value = TextBox5.Text
AddNew.Offset(0, 5).Value = TextBox6.Text
AddNew.Offset(0, 6).Value = TextBox7.Text
AddNew.Offset(0, 7).Value = TextBox8.Text
AddNew.Offset(0, 8).Value = TextBox9.Text
AddNew.Offset(0, 9).Value = TextBox10.Text
AddNew.Offset(0, 10).Value = TextBox11.Text
AddNew.Offset(0, 11).Value = TextBox12.Text
AddNew.Offset(0, 12).Value = TextBox13.Text
AddNew.Offset(0, 13).Value = TextBox14.Text
AddNew.Offset(0, 14).Value = TextBox15.Text
AddNew.Offset(0, 15).Value = TextBox16.Text
AddNew.Offset(0, 16).Value = TextBox17.Text
End Sub
Private Sub RefreshListBox()
Me.lstDatabase.Clear
Dim ListRange As Range
End Sub
Private Sub reset_Click()
iExit = MsgBox("Confirm if you want to exit", vbQuestion + vbYesNo, "Data Entry Form")
If iExit = vbYes Then
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
Label1.Caption = Sheets("Overview").Cells(2, 1)
ComboBox1.List = [Products!A2:A7].Value
Label2.Caption = Sheets("Overview").Cells(4, 1)
Label3.Caption = Sheets("Overview").Cells(4, 2)
Label4.Caption = Sheets("Overview").Cells(4, 3)
Label5.Caption = Sheets("Overview").Cells(4, 4)
Label6.Caption = Sheets("Overview").Cells(4, 5)
Label7.Caption = Sheets("Overview").Cells(4, 6)
Label8.Caption = Sheets("Overview").Cells(4, 7)
Label9.Caption = Sheets("Overview").Cells(4, 8)
TextBox9.Visible = False
TextBox10.Visible = False
TextBox11.Visible = False
TextBox12.Visible = False
TextBox13.Visible = False
TextBox14.Visible = False
TextBox15.Visible = False
TextBox16.Visible = False
TextBox17.Visible = False
ComboBox1.ListIndex = 0
End Sub
Fixed the code - Turned out I needed to add the following line
iRow = [Counta(SheetName!A:A)]
Placement of said line is located here:
Private Sub ComboBox1_Change()
Dim iRow As Long
With DataEntry
If ComboBox1.Value = "PL531e" Then
TextBox9.Visible = True
TextBox10.Visible = True
TextBox11.Visible = True
TextBox12.Visible = True
TextBox13.Visible = True
TextBox14.Visible = True
TextBox15.Visible = True
TextBox16.Visible = True
TextBox17.Visible = True
Label10.Caption = Sheets("PL531e").Cells(1, 9)
Label11.Caption = Sheets("PL531e").Cells(1, 10)
Label12.Caption = Sheets("PL531e").Cells(1, 11)
Label13.Caption = Sheets("PL531e").Cells(1, 12)
Label14.Caption = Sheets("PL531e").Cells(1, 13)
Label15.Caption = Sheets("PL531e").Cells(1, 14)
Label16.Caption = Sheets("PL531e").Cells(1, 15)
Label17.Caption = Sheets("PL531e").Cells(1, 16)
Label18.Caption = Sheets("PL531e").Cells(1, 17)
iRow = [Counta(PL531e!A:A)]
.lstDatabase.ColumnCount = 17
.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
If iRow > 1 Then
.lstDatabase.RowSource = "PL531e!A1:Q" & iRow
Else
.lstDatabase.RowSource = "PL531e!A2:Q2"
End If
Additionally I had to add a Sub reset() with the following code:
Sub reset()
Dim iRow As Long
With DataEntry
If ComboBox1.Value = "PL531e" Then
iRow = [Counta(PL531e!A:A)]
If iRow > 1 Then
DataEntry.lstDatabase.RowSource = "PL531e!A1:Q" & iRow
Else
DataEntry.lstDatabase.RowSource = "PL531e!A2:Q2"
End If
After this was added the issue was sorted, I additionally found a issue which involved the headers in the listbox displaying the column header names themselves E.g. Column A, B, C etc. instead of the TestNo, NeuronID, Date Code etc. I solved this by simply deleting the following line of code:
.lstDatabase.ColumnHeads = True
Not gonna paste the entire code all fixed as those basic fixes are literally copied/pasted in their relevant sections just instead of PL531e its PL931, PL968 etc.
Edit: Following on from what @Tim Williams suggested, Me.Controls("Textbox" & i) I ended up changing the Textbox9.Visible = True etc.. section to the following using both Me.Controls and the tagging property:
Dim oneControl As MSForms.Control
For Each oneControl In Me.Controls
If TypeName(oneControl) = "TextBox" Then
With oneControl
Select Case .Tag
Case "typeA"
.Visible = True
.Value = ""
Case "typeB"
.Visible = Not (ComboBox1.Value = "PN510")
.Value = ""
Case "typeC"
.Visible = ((ComboBox1.Value = "PL931") Or (ComboBox1.Value = "PL531e"))
.Value = ""
Case "typeD"
.Visible = (ComboBox1.Value = "PL531e")
.Value = ""
End Select
End With
End If
Next oneControl