Good morning everyone,

nothing attached

Miguel,

Sorry - that was me rushing!

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,

Hi,

Thank you for looking in to this for me. I have looked at the error you have said about and changed the way which I add the items to my listbox with the below: -

.AddItem Worksheets("Sheet3").Range("F" & i).Value

I am still very much a novice at VBA so will take on board your comments on the basic formatting of the spreadsheet and look in to this as they will be good practice for future projects.

Step 1 - When the user presses the OK button at the bottom of the New Test (multipage.value = 1) userform, when the user selects the test "Other (please specify)" in the listbox TestsRequested, the Other2 textbox becomes visible and anything typed in there goes in to the same row as the Other.

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.

Step 2 - when the user presses the Print button on the bottom of the same userform. I would like some of the tests selected in the listbox TestsRequested to go in to cell 40, 1. They would need to go underneath each other.

For example: -

The user could select all of the below from the listbox TestsRequired when High Street is selected in the TestHouse combobox.

If TestsRequired.Selected(2) Then Cells(40, 1).Value = "BS 5852 ign source 5 WITHOUT watersoak"
If TestsRequired.Selected(3) Then Cells(40, 1).Value = "BS 5852 ign source 7"
If TestsRequired.Selected(4) Then Cells(33, 8).Value = "X"
If TestsRequired.Selected(4) Then Cells(34, 8).Value = "X"
If TestsRequired.Selected(5) Then Cells(36, 8).Value = "X"
If TestsRequired.Selected(9) Then Cells(40, 1).Value = "BS 7175 crib 5"
If TestsRequired.Selected(10) Then Cells(40, 1).Value = "BS 7175 crib 7"

The values from Selected 2, 3, 9 and 10 would need to go underneath each other in a list in row 40 of spreadsheet High Street, but not the values 4 and 5.

Step 3 - on the last page of the tabs (multipage.value = 5) when the user presses the OK button, I would like previous data for that request number to be overwritten and new details added.

I am not sure if this is any clearer and thank you very much for looking in to this for me.

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,

Hi,

I have tried the Print button and that does exactly what I need it to do - thank you so much, I didn't think this would even be possible!

I am currently working through the other list of points you have asked to make the relevant changes on the spreadsheet so thank you for these too!

The other 2 things are on the Word document I have attached on to this and hope this may help.

I completely understand the step by step process and am so grateful for the help.

Thank you again for looking in to this and will take on board your comments you have made on the module pages - very new still to this so very helpful.

Hi Miguel,

I have also gone through the sheet you last attached.

I have added my own queries next to some of yours with **on either side of my question**

These are not related to this post but do not know how to message separately or I should start a new thread.

Hello everyone,

answering your questions

# Question 1

Q. -> ' **What does all of this mean? Are they indicators for finding what needs to be done?**

R. -> you can eliminate all of this if you want, it's just informative, I always put an index on top of a module, and with it I know where all the macros written on the module are, imagine having more than 100 procedures (macros) in a module, getting knowing where to write a particular macro can be difficult if there is no reference ... basically it is like in books

# Question 2

Q. -> ''--- ERROR TRAP '**what is this?**

R. -> it is just a reference that I give to or the next lines, for example, when we write a macro, each line or set of lines has an objective, in this case I identify at the beginning of the macro, if the requirements are not the ones I want or that there is an error, the macro stops there and does not continuern

# Question 3

Q. -> ' **What do the In Up/ In Down mean?**

'''''''''''''''''''''' ...

' IN UP:

' IN DOWN:

'''''''''''''''''''''' ...

R. -> it's just reference / information between macros or group of macros, for example you have a command button, I group all the events of that control (click; change ...) and place one of these references on top of the group and another below the group... it helps me to know where the macros are and to identify faster what I'm looking for

example:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'IN UP:

'IN DOWN: listbox(TestsRequired) - multipage(1);page(2)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub TestsRequired_Change()

...

End Sub

Private Sub TestsRequired_Click()

...

End Sub

Private Sub TestsRequired_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)

...

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'IN UP: listbox(TestsRequired) - multipage(1);page(2)

' IN DOWN: commandbutton(OK) - multipage(1);page(2)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub OK_Click()

...

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' IN UP: commandbutton(OK) - multipage(1);page(2)

' IN DOWN:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

# Question 4

Q. -> ' **can all the below pdf code be deleted now if I have the code in the module?**

R. - > only your original code that is green can be deleted, I left it, because you might not want my macro and you want yours, I left and always leave your decision, but that part is not doing anything there

# Question 5

Q. -> ...' (MACRO - module) '** does this call the code from the module?**

R. -> this is a reference / information that I put together of one or more lines of code so that I know that this macro is not in the userform module, but in a standard module, it is easier to know where we have everything written

# Question 6

Q. -> ...' **what is the use/ function of the pause?**

R. -> in many procedures, excel may be delayed in the execution of some steps ... example: i have a macro with several steps that the excel application will have to do, but excel may have a delay between step 3 and step 4, starting step 4 without having finished step 3, and that will give errors, to avoid mistakes, I add a pause (usually thousands of seconds are enough) between the steps they need

there are more questions and answers, but I think I understand your idea of ​​problem 1, as soon as I can change and send a file

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,

Thank you so much for going through my questions on the spreadsheet. This just helps my understanding more!

Thank you for coming back to me on point 1. This would work however, I presume this would take the selected item of 35 from any of the tests required lists? The Other textbox can be filled in depending on the test house in the combobox. So would it need to be:-

If TestHouse.Value = "HighStreet"

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

Also, this would just add to the last row so if more tests were selected from the listbox after "other" then it would go on the same row as this.z

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,

Hi Miguel,

That is perfect - thank you so much!!

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,

Hi Miguel,

I have used the same adjustments that you gave me for the 2nd issue on the other pages and working exactly how I need it to. Thank you very much!

As for the 3rd issue: -

Please see my comments next to yours

_ 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)
I have tried several methods with this User Form. Ideally I wanted 2 listboxes: - One which populated based on the contents of TRNNumber 1
and one which populated using the TestHouse2 and deleted these from the change TRNNumber1 to remove duplicates. However, I did not know the 
code for this.

=> 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)?
I had not finished the textboxes/ check box list I just needed help with the OK button so posted this unfinished :)

=> 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) ?
If there are 10 tests for TRN Number 15, and the user deselects all of these and changes it to 5 new tests, then I would like the OK button to: -
1. Delete the original 10 tests for TRN Number 15
2. Insert the new 5 tests for TRN Number 15 between TRN Number 14 and 16

So far I can only get it to add in to the middle of the tests, not delete and add, so it ends up with 15 tests not the 5 new selected ones.

I hope this makes sense