• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / 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

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosMultiselect listbox - if specific v…
sp_PrintTopic sp_TopicIcon
Multiselect listbox - if specific value selected, save textbox to same row, save values to cell or row
Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
1
December 9, 2020 - 9:31 pm
sp_Permalink sp_Print

Hi,

I have several multi-select listboxes across my multipage userform. There are essentially 3 points I am still stuck on, I have tried looking at other forums with no joy. They are all relating to multiselect listboxes which have confused me for a while. I hope that someone can help me. 

On the 1st page is a combobox (TestHouse), based on the selection made in this it fills the listbox (TestsRequired) from a named range on Sheet3.
In the named ranges is always an option called "Other (please specify)" which, depending on the TestHouse can appear at any stage in the list.

There is a text box called "Other (please specify)" which becomes visible when the specific TestHouse and TestsRequired are selected: -

If TestHouse = "High Street" Then
If TestsRequired.Selected(34) = False Then
Other.Value = ""
Other.Enabled = False
Other.Visible = False
Label27.Visible = False
Else
Other.Enabled = True
Other.Visible = True
Label27.Visible = True
End If
End If

At the bottom of the page is a command button called OK and one called Print.

Issue 1 - OK Button

When the user selects the various tests they would like from the TestsRequired listbox and clicks the OK button I would like the below to happen: -

The "Other (please specify)" value from the listbox and the Other textbox line up on the same row on the next blank cell.

So far I have: -

 For lItem = 0 To TestsRequired.ListCount - 1
If TestsRequired.Selected(lItem) = True Then
Worksheets("Sheet1").Cells(TestsRequired.List(lItem, 2), 14) = Other.Value
End If
Next 

However, this puts the Other.Value next to every value selected from the listbox. Is there a way so that when the Other (please specify) value is selected from the listbox, the Other textbox value is added to the same row?

Issue 2 - Print

When the user presses the Print button, it currently goes to the specific worksheet based on the TestHouse value, saves the file as a PDF and prints to the default printer. I have the code for saving values to the relevant cells as below, including each line of the TestsRequired listbox: -

If TestHouse = "High Street" Then
Worksheets("High Street").Activate
Cells(7, 8).Value = Me.TRNNumber.Value
Cells(6, 8).Value = CDate(Me.DateRequested.Text)
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"

However, the issue I am having, is that there are over 40 options to fill cell (40, 1). I would like it so that each of these, if selected, are saved in to the same cell as a list which can shrink/ grow depending on the values put in there.

I tried to use the cell (40, 1) on Sheet (High_Street) to save the values using the code: -

=IFERROR(INDEX(Sheet1!M:M,SMALL(IF(COUNTIF(H7,TRN_Number),MATCH(ROW(TRN_Number),ROW(TRN_Number)),""),ROWS($A$1:A1))),0)

However, this then copies every value from the listbox not just the ones I would like.

I have thought about maybe filling another listbox called Tests which can then be put in to cell (40, 1). Would this work?

If TestHouse = "High Street" Then
If Me.TestsRequired.Selected(0) Then
Tests.Value = Me.TestsRequired.List(0)
If TestsRequired.Selected(1) Then
Tests.Value = Me.TestsRequired.List(1)
End If

Issue 3 - Amend

The third issue is on the last multipage userform is Amend Test. This has several pre-filled textboxes and one listbox. This is where the user can amend the test which they have already requested. They type the Test request number in to the top textbox and press search. This fills the boxes on the userform.

The user can then amend the parts of the request they would like to and it then re-saves onto the spreadsheet. What I would like it to do is overwrite the test already on Sheet1 and put all the new values in its place, adding any other necessary lines onto the sheet depending on how many values have been selected from the listbox (TestsRequired2).

Current code I have is but this adds rows depending on how many tests have been selected from the listbox: -

Dim lItem As Long

For lItem = 0 To TestsRequired2.ListCount - 1
ActiveCell.EntireRow.Insert shift:=xlShiftDown
ActiveCell.Activate
Next lItem

For i = 0 To TestsRequired2.ListCount - 1

If TestsRequired2.Selected(i) Then
Cells(ActiveCell.Column, 13).End(xlDown).Offset(1, 0).Value = TestsRequired2.List(i)
End If
Next i

I have attached my workbook so you can see how these codes work and in what way. I hope this is clear and any help would be appreciated.

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
2
December 9, 2020 - 9:59 pm
sp_Permalink sp_Print sp_EditHistory
Good morning everyone,


nothing attached

Miguel,
Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
3
December 10, 2020 - 1:04 am
sp_Permalink sp_Print

Sorry - that was me rushing!

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
4
December 10, 2020 - 9:21 am
sp_Permalink sp_Print

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,

Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
5
December 10, 2020 - 8:03 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
6
December 11, 2020 - 12:45 am
sp_Permalink sp_Print sp_EditHistory

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,

Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
7
December 11, 2020 - 10:03 pm
sp_Permalink sp_Print

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.

Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
8
December 11, 2020 - 11:14 pm
sp_Permalink sp_Print

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.

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
9
December 12, 2020 - 1:07 am
sp_Permalink sp_Print

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,

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
10
December 12, 2020 - 2:09 am
sp_Permalink sp_Print sp_EditHistory

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,

Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
11
December 12, 2020 - 11:20 pm
sp_Permalink sp_Print

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

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
12
December 13, 2020 - 3:30 am
sp_Permalink sp_Print sp_EditHistory

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,

img-1-1.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage img-1-1.JPG (108 KB)
Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
13
December 14, 2020 - 7:32 pm
sp_Permalink sp_Print

Hi Miguel,

That is perfect - thank you so much!!

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
14
December 15, 2020 - 3:22 am
sp_Permalink sp_Print

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,



Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
15
December 15, 2020 - 7:48 pm
sp_Permalink sp_Print

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
Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
16
December 17, 2020 - 4:10 am
sp_Permalink sp_Print

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,

Avatar
Helen Warburton
Member
Members
Level 0
Forum Posts: 20
Member Since:
November 16, 2020
sp_UserOfflineSmall Offline
17
December 17, 2020 - 8:09 pm
sp_Permalink sp_Print

That is exactly what I wanted!!!

Thank you so much for your ongoing support - I could not have done this without you!

 

Learned a lot about VBA and controls from you 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Merav Tzori
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 217
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
David du Toit
leandro barbarini
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27796

 

Member Stats:
Guest Posters: 49
Members: 32326
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.