Multiselect listbox - if specific value selected, save textbox to same row, save values to cell or row|VBA & Macros|Excel Forum|My Online Training Hub

Good morning everyone,

nothing attached

Miguel,

Hello,

I'm still trying to figure out what you want, so I'm changing your project step by step

I attached the file with just a few changes before I continue, and I need more clarification

there were and still many errors to correct

I advise you to always write: Option Explicit at the top of the module
and when projects are large, it is advisable to have all variables declared, as well as clearing the memory (ex: something = Nothing)

the organization is your choice, I’m just suggesting (I started making some changes in this regard in the userform), but you can just change it if you want

I added 2 modules with macros to be used in the future

I added 2 command buttons next to the list box to select or deselect all items at once.

I added 2 labels below the listbox, for reference of the total items in the listbox and the total of selected items

I changed the way the listbox is filled, it gave me an error if the range (named range) only had one line

I started with the button (OK), I added a msgbox asking if you want to transfer the data to the sheet

there are "variables" that I don't understand where they come from ... ex: RngValue

confirms the result of the button (OK) and you can clarify what is missing in this part

for now that's it

Miguel,

Hello,

I'm sorry, but I still couldn't figure it out ... until I selected those items in the listbox for the two textboxes to appear and insert values ​​in them, all right, I realized, but in your original command button code (OK), there is none no reference to these textboxes

when you say row 2,3,4 ... what do you mean, on the excel sheet?

you can do me a favor, manually enter the data in the excel sheet, as they should be (fill in about 10 lines) then leave 2 lines of space, and in the next line, in each column of data, write the name of the commands ( textbox, combobox, listbox) and some text of yours to describe under that line too

then take a picture, and attach it to your next post, or copy this data (just this sheet) into another workbook and attach

For example: -

Someone could select 
Breathability
Martindale - end point
Other (please specify)

These would go on to row 2,3,4 respectively. Currently when the user presses OK, the value from the Other textbox is put on row 2,3,4 and I would just like it to go on row 4.

attached file with changes to the button (PRINT) of page (2) of the multipage

I added more modules

I changed your PDF macro, new macro added, the macro will ask you first if you want to create the PDF document, if you say yes, the macro will ask for a name to be given (it will check if there is already a PDF document with that name or if it has prohibited characters), at the end, if there are no errors, msgbox will appear with the path and name of the PDF file

confirm that the button (Print) does what you wanted

I left some questions at the PrintForm_Click event, confirm each one and tell me ('' ??? ...)

I made changes to the "street ..." sheet


"never use merge cells, but use across in the selection"

sorry has to be step by step

for now that's it

Miguel,

hello,

confirm if this solves your problem number 1 ? the blue highlighted line is the one that accomplishes what you want

replace your code at the event Private Sub OK_Click() , with the following

according to your image in the word doc, the listbox data was missing in the column ("M") I added this line (highlighted in red) if not, delete that line

Private Sub OK_Click()

Dim wks As Worksheet: Set wks = ThisWorkbook.Worksheets("Sheet1")
Dim i As Integer, lastrow As Integer
Dim UserAnswer As VbMsgBoxResult

''--- ASK TO CONFIRM DIRECTIVES
UserAnswer = MsgBox("• Are you sure you want to register the options chosen in the database?", vbYesNo + vbQuestion, "Please confirm!")
If CStr(UserAnswer) = CStr(False) Then Exit Sub
If UserAnswer = vbCancel Then Exit Sub
If UserAnswer = vbNo Then Exit Sub

Application.ScreenUpdating = False

With wks
      lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
      For i = 0 To Me.TestsRequired.ListCount - 1
          If Me.TestsRequired.Selected(i) Then
                  .Cells(lastrow, 1).Value = Me.TRNNumber.Value                   ' textbox
                  .Cells(lastrow, 2).Value = Me.DateRequested.Value               ' textbox (date)
                  .Cells(lastrow, 3).Value = Me.TestHouse.Value                      ' combobox
                  .Cells(lastrow, 4).Value = Me.ProductName.Value                  ' textbox
                  .Cells(lastrow, 5).Value = Me.Shade.Value                            ' textbox
                  .Cells(lastrow, 6).Value = Me.POFabric.Value                        ' textbox
                  .Cells(lastrow, 7).Value = Me.Supplier.Value                         ' textbox
                  .Cells(lastrow, 8).Value = Me.Composition.Value                   ' textbox
                  .Cells(lastrow, 9).Value = Me.TestRequestedBy.Value            ' combobox
                  .Cells(lastrow, 10).Value = Me.TestAuthorisedBy.Value          ' combobox
                  .Cells(lastrow, 11).Value = Me.GLCode.Value                       ' combobox
                  .Cells(lastrow, 21).Value = Me.Washing.Value                      ' listbox
                  .Cells(lastrow, 22).Value = Me.Drying.Value                         ' listbox
                  .Cells(lastrow, 23).Value = Me.Ironing.Value                        ' checkbox

                  .Cells(lastrow, 13).Value = Me.TestsRequired.List(i)              ' selected item in this lisbox

                  If (i) = 35 Then .Cells(lastrow, 14).Value = Me.Other.Value  ' hidden textbox

                  lastrow = lastrow + 1
            End If
      Next i
End With

Application.ScreenUpdating = True

If Not wks Is Nothing Then Set wks = Nothing

End Sub

Miguel,

Hello,

Confirm that it is in accordance with the attached image

changes highlighted in red

replace the event Private Sub OK_Click() code with the following

Private Sub OK_Click()

Dim wks As Worksheet: Set wks = ThisWorkbook.Worksheets("Sheet1")
Dim i As Integer, lastrow As Integer
Dim UserAnswer As VbMsgBoxResult

''--- ASK TO CONFIRM DIRECTIVES
UserAnswer = MsgBox("• Are you sure you want to register the options chosen in the database?", vbYesNo + vbQuestion, "Please confirm!")
If CStr(UserAnswer) = CStr(False) Then Exit Sub
If UserAnswer = vbCancel Then Exit Sub
If UserAnswer = vbNo Then Exit Sub

Application.ScreenUpdating = False

With wks
     lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
     For i = 0 To Me.TestsRequired.ListCount - 1
         If Me.TestsRequired.Selected(i) Then
             .Cells(lastrow, 1).Value = Me.TRNNumber.Value ' textbox
             .Cells(lastrow, 2).Value = Me.DateRequested.Value ' textbox(date)
             .Cells(lastrow, 3).Value = Me.TestHouse.Value ' combobox
             .Cells(lastrow, 4).Value = Me.ProductName.Value ' textbox
             .Cells(lastrow, 5).Value = Me.Shade.Value ' textbox
             .Cells(lastrow, 6).Value = Me.POFabric.Value ' textbox
             .Cells(lastrow, 7).Value = Me.Supplier.Value ' textbox
             .Cells(lastrow, 8).Value = Me.Composition.Value ' textbox
             .Cells(lastrow, 9).Value = Me.TestRequestedBy.Value ' combobox
             .Cells(lastrow, 10).Value = Me.TestAuthorisedBy.Value ' combobox
             .Cells(lastrow, 11).Value = Me.GLCode.Value ' combobox
             .Cells(lastrow, 21).Value = Me.Washing.Value ' listbox
             .Cells(lastrow, 22).Value = Me.Drying.Value ' listbox
             .Cells(lastrow, 23).Value = Me.Ironing.Value ' checkbox

             .Cells(lastrow, 13).Value = Me.TestsRequired.List(i) ' selected item in this lisbox

             ' hidden textbox
             If Me.TestHouse.Value = "High Street" Then If Me.TestsRequired.List(i) = "Martindale abrasion - Other (please specify)" Then .Cells(lastrow, 14).Value =  Me.Other.Value

             lastrow = lastrow + 1
         End If
     Next i
End With

Application.ScreenUpdating = True

If Not wks Is Nothing Then Set wks = Nothing

End Sub

Miguel,

Hello everyone,

so the first problem is solved, the second problem still needs some adjustments 🙂

regarding the third problem, I need more clarification

(page 7 of MultiPage1)

_ I put the search value in the textbox field (TRNNumber1)
_ I click the command button (Search)

_ the following textbox fields (TestHouse2; ProductName2; Shade2; POFabric2; Supplier2; Composition2; Other3), are populated based on demand

=> but the listbox (TestsRequired2) is re-filled with all items, based on the change event of the textbox (TestHouse2),
that is, it ends up having all the items available and not related to the search in the sheet by the textbox (TRNNumber1)

=> and the following controls are not mentioned in your original code (Other4; CheckBox1; CheckBox2) ... is it to add a reference to the command button (search)?

=> clicking the command button (OK), what is it to do?  
is to eliminate all lines found with the textbox search (TRNNumber1) ,
and add in the excel sheet, new data selected on this page 7 of multipage1, with the number of the search in textbox(TRNNumber1) ?
  

   

Miguel,

Hello,

attached file with the following changes:

=> in multipage(1);page(7):

_ new search option - when you click on the textbox (TRNNumber1), an inputbox will appear, where the number should be inserted,

and then click on the search button

_ now this page has 2 listboxes - one with the "search" data and the other with the general list

_ I added two options to control the two list boxes, to remove the selected item from the list box (with "search" data)

can be by double click or with command button... to transfer / add data from the listbox (general list) to the listbox (with search data) can be done by drag & drop or command button

_ command button (OK) with new macro, will ask the user if he wants to continue, after confirmation will delete all rows with the textbox number (TRNNumber1), will retain the number of the first row that had the textbox number(TRNNumber1), and from that row number, you will insert a row for each item in the listbox (from the survey data) and insert new data for each row with the changes made... I don't know if that's how you wanted it

with this procedure the new data are inserted in the sequence of the sheet data, example: search for number 2 (which in the sheet is between number 1 and number 3), clicking on (OK) deletes all rows with number 2 , and insert the new data (with the number 2 as well) between the data of the number 1 and 3, regardless of the number of lines that will be added

NOTE: I didn't change or add the missing controls (text box; checkbox ...) that you want to add, but you have everything ready to follow your project

confirm if this is what you wanted?

Miguel,