Hi, this is my first post and I hope I have followed the rules as directed correctly.
I have searched many forums for developing my VBA Userform, and as a beginner, I have created my userform through test samples and other peoples guidance/code as I came across error/problems.
As a result,pieced bits together I have now created something which works, has some dynamic coding, with drop down choices interactive sheet pre-selections from prior user selections navigating the user automatically to the next required page based on selection (all pages are hidden to the user upon initialise).
- My userform is multipage, populates listbox and combobox via "Additem" using a variety of cases/index within the VBA coding. Nothing is populated from the excel sheet.
- The userform is actioned via a click event on the sheet to action the UserForm_Initialize() sub for user entries.
- The populated excel sheet "Sheet1" for example, works as I want, populating the sheet from framed option buttons, text boxes, dates and auto unique ID for each new row created by userform as each user enters data, all good and very pleased.
Column H, is where the ListBox1 populates the user selected choices. The Listbox1 is populated via two ComboBoxes. The First ComboBox selection dictates the contents of the second ComboBox. The second ComboBox user selection, dictates the ListBox1 contents for the User to select one or more items. (fmMultiSelectMulti)
- As an example, next available clear row is populated with the userform details, so if row 2 is already filled with data, row 3 will be next, with each column receiving specific userform data i.e Col A = ID#, Col B = Name, Col C = Project Number etc. Col H received the ListBox1 user selected strings. these populate the next available cell in Col H with a comma separated string(s). these range from a single predefined selection choice upto 10 choices.
In Col H, (assuming the user selects four of the choices available from the list), the cell contents after click control (OK) this looks like:
Text String Selected No 1,Text String Selected No 2,Text String Selected No 3,Text String Selected No 4,etc
I want to have:
Text String Selected No 1
Text String Selected No 2
Text String Selected No 3
Text String Selected No 4
I have tried using the split function without success, which is no doubt my not understanding all the coding language, so my question is how can I achieve a forced line in the cell for each user selection chosen when "printing" to the sheet?
I have pasted the excerpt of code below which I am having difficulty.
Private Sub OKButton_Click() Dim emptyRow As Long 'Make Sheet1 active Sheet1.Activate ActiveSheet.Unprotect "password" 'Determine emptyRow emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1 'Transfer information Cells(emptyRow, 1).Value = IDTextBox.Value Cells(emptyRow, 3).Value = ProjectNumberTextBox.Value Cells(emptyRow, 4).Value = CompletedByTextBox.Value Cells(emptyRow, 5).Value = DateTextBox.Value 'Transfers selected items to Col H 'The Flg is a TRUE/FALSE variable which becomes TRUE when there is one or more item selected in Listbox. Dim i As Long, txt As String, Flg As Boolean With Me.ListBox1 For i = 0 To .ListCount - 1 If .Selected(i) Then Flg = True txt = txt & "," & .List(i) End If Next End With If Flg Then With Sheets("Design Checklist and QAQC") .Range("H" & .Rows.Count).End(xlUp).Offset(1).Value = Mid$(txt, 2) End With 'the above code works fine and populates into Col H next available row and correct cell, but is not split into forced lines 'new code (2) to split text in active cell - trying to integrate it with above working code Dim strInput As String, strOutput As String Dim varZz As Variant, j As Integer ' change from i to j as error with duplicate i strInput = Active.Cell.Value ' tried changing from activecell.Value to Flg to txt none worked. varZz = Split(strInput, ",") 'thought this would help split my txt For j = LBound(varZz) To UBound(varZz) strOutput = varZz(j) Next j 'end of new code (2) End If If '..... rest of code for option buttons etc., then locking sheet and end.
I hope the above is no too truncated and look forward to some useful pointers, correction, guidance to reach my requirements successfully. Thanks