please bear with my code. (I'm not a good coder and not familiar with all VBA syntax.)
I'm creating a database for all our household books.
I'm not using ACCESS or SQL, just simply recording all UserForm input data to an Excel Sheet.
In my UserForm, all data, which has a category like: Author, Genre, Publisher, Location of the book in house, etc., are input through a ComboBox.
The initial RowSource of each ComboBox is a range in an excel sheet. In this range, I have already typed-in some items for each category. So, upon executing the Macros, when the dropdown arrow of each ComboBox is clicked, list items are shown.
The function of "Private Sub CmdEditList_Click()" in the code below is, first, to update the list of items in each category, if the data in each ComboBox is not found in the existing list. And second, to update the RowSource of each ComboBox.
The purpose of the MsgBox code line below, in which I have a problem, is to inform the user which of the Categories has an item added to it's list.`
MsgBox "The following Categories were updated:" & vbNewLine & msg`
But in cases for example that 3 categories (Author, Publisher & Series) are updated, Author and Publisher is not shown, instead after 2 newlines, only "Series" is shown.
What is the cause of the problem? What is the solution?
Private Sub CmdEditList_Click()
Dim NextListRow As Long
Dim ComboArr()
Dim RangeArr()
Dim MsgBoxArr()
Dim CategoryArr()
Dim i As Integer
Dim UpdateItemCnt As Integer
Dim mbi As Integer
Const LASTINDEX = 8
i = 0
UpdateItemCnt = -1
ComboArr = Array(ComboAuthor, ComboGenre, ComboPublisher, _
ComboLocation, ComboSeries, ComboPropertyOf, _
ComboRating, ComboRatedBy, ComboStatus)
RangeArr = Array("R", "S", "T", "U", "V", "W", "X", "Y", "Z")
CategoryArr = Array("Author", "Genre", "Publisher", "Location", "Series", _
"Property Of", "Rating", "Rated By", "Status")
Do While i <= LASTINDEX
'Checks each Combobox, if ther's a data input.
If Len(Trim(ComboArr(i).Value)) <> 0 Then
Set wkb = ThisWorkbook
wkb.Sheets("Database").Activate
With ActiveSheet
'Finds the cell, where a new item of a Category can be placed in the excel sheet.
NextListRow = .Cells(.Rows.Count, RangeArr(i)).End(xlUp).Row + 1
End With
'Check if the entered data is not in the existing list.
'If True, ComboBox data is in the list.
If Application.CountIf(Range(RangeArr(i) & "2" & ":" & RangeArr(i) & NextListRow), _
ComboArr(i).Value) > 0 Then
GoTo NextRoutine
Else
UpdateItemCnt = UpdateItemCnt + 1
ReDim MsgBoxArr(UpdateItemCnt)
MsgBoxArr(UpdateItemCnt) = CategoryArr(i)
MsgBox MsgBoxArr(0) 'To Check the value of MsgBoxArr(0) after 2nd assignment.
'Upon checking via debug simulation, the value = "".
'Assigns the ComboBox Value under its corresponding Category in excel sheet.
Database.Cells(NextListRow, RangeArr(i)).Value = ComboArr(i).Value
'Refreshes the range of the list to be displayed via the ComBox dropdown arrow.
Range(RangeArr(i) & "2", Range(RangeArr(i) & Rows.Count).End(xlUp)).Name = "Dynamic"
ComboArr(i).RowSource = "Dynamic"
End If
NextRoutine:
Else
GoTo EndRoutine
EndRoutine:
End If
i = i + 1
Loop
MsgBox MsgBoxArr(0) 'To Check the value of MsgBoxArr(0) after loop.
'Upon checking via debug simulation, the value = "".
For mbi = LBound(MsgBoxArr) To UBound(MsgBoxArr)
msg = msg & MsgBoxArr(mbi) & vbNewLine
Next mbi
MsgBox "The following Categories were updated:" & vbNewLine & msg
'In cases, wherein new item for Author, Publisher and Series were input in the UserForm,
'the MsgBox only shows: The following Categories were updated:
'
'
' Series
End Sub
