Concatenate column into textbox on input form - VBA

rpadminserver

Board Regular
Joined
Nov 7, 2007
Messages
108
hello All....
I have an excel input form where the user enters a WORKORDER NUMBER (WONUM) and Asset assigned. Most of the time there will be multiple assets per wonum, each of these assets are a separate entry.
What I'm trying to accompish:
I want a text box that will list these assets in a single row.
Example:
The user will enter
WONUM ASSET
1234 Monitor
1234 Keys
1234 Wires

I need the text box to display;
"Monitor, Keys, Wires".
When the user is done the input form will be cleared and I need the same functionality for the next wornum
Couple of notes:
1. The user will NEVER go back and update a WONUM after moving on to the next WONUM
2. There will always be at least 1 asset per wonum, but the max number assets per wonum is unlimited.
3. I need this box because the user needs to copy this information and paste it into another system.

Any help is appreciated
Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,476
Office Version
365
Platform
Windows
A few questions
1) In your example will the user enter 1234?
2) how will they enter the number? Textbox, combobox, Listbox?
3) where are the Wonum & Asset?
4) Are the Wonums actual numbers, numbers stored as text, or alphanumeric text strings?
5) Is this being done on a UserForm?
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,136
This will copy the values in column B to the clipboard (so it available to paste) and then display a message.

Code:
[COLOR=darkblue]Sub[/COLOR] Assets()
    
    [COLOR=darkblue]Dim[/COLOR] objClipboard [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Set[/COLOR] objClipboard = CreateObject("new:1C3B4210-F441-11CE-B9EA-00AA006B1A69")
    [COLOR=darkblue]Dim[/COLOR] txt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    txt = Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp))), ", ")
    objClipboard.SetText txt
    objClipboard.PutInClipboard
    
    MsgBox txt, vbInformation, "Copied to the Clipboard"
    
    [COLOR=green]'Clear A2 to last used cell in column B[/COLOR]
    'Range("A2", Range("B" & Rows.Count).End(xlUp)).ClearContents
    
End [COLOR=darkblue]Sub[/COLOR]
 
Last edited:

rpadminserver

Board Regular
Joined
Nov 7, 2007
Messages
108
Fluff...
1+2 I have code that creates a unique wonum every time the user hits a "create new workorder" button.
3. saved to sheet3.....WONUM is column A and Asset is column AB
4. The workorder number is stored as TEXT (FYI... the 1234 is just an example the actual WONUM will always start with a T followed by a 8 digit number)
5. Yes, on a user form.
 

rpadminserver

Board Regular
Joined
Nov 7, 2007
Messages
108
Thanks Alpha... But I'm not sure this going to work for me.
1. I need the Asset numbers put into a text box because the users will need to edit the info before they copy it into the other system
2. How will this code know only to use the current workorder?
3. the asset column can't be cleared

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,476
Office Version
365
Platform
Windows
Ok how about
Code:
[COLOR=#0000ff]Dim UfDic As Object[/COLOR]

Private Sub ComboBox1_Click()
    Me.TextBox1.Value = UfDic(Me.ComboBox1.Value)
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   
   Set UfDic = CreateObject("scripting.dictionary")
   UfDic.CompareMode = 1
   With Sheets("Sheet3")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Not UfDic.exists(Cl.Value) Then
            UfDic.Add Cl.Value, Cl.Offset(, 27).Value
        Else
            UfDic(Cl.Value) = UfDic(Cl.Value) & ", " & Cl.Offset(, 27).Value
        End If
      Next Cl
   End With
   Me.ComboBox1.List = UfDic.Keys
End Sub
The line in blue needs to go at the very top of the module, before any code.
Change the combo_click event to wherever the wonum is stored.
 

Forum statistics

Threads
1,078,356
Messages
5,339,732
Members
399,320
Latest member
zim1984

Some videos you may like

This Week's Hot Topics

Top