Textbox Editable

J

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
780
Office Version
  1. 365
Hi,

I have a listbox how can make the textbox able to edit the text on the form the code i have is this:


VBA Code: 
Private Sub ListBox_Results_Click()
'Go to selection on sheet when result is clicked

Dim strAddress As String
Dim l As Long

    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
            
                COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 3).Value
                COGSform_All.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 13).Value
                COGSform_All.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 10).Value
                COGSform_All.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 14).Value
                COGSform_All.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 4).Value
                COGSform_All.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 5).Value
                COGSform_All.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 6).Value
                COGSform_All.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 11).Value
                COGSform_All.TextBox_Results11.Value = .Cells(.Range(strAddress).Row, 12).Value
                COGSform_All.TextBox_Results12.Value = .Cells(.Range(strAddress).Row, 8).Value
                COGSform_All.TextBox_Results13.Value = .Cells(.Range(strAddress).Row, 15).Value
                COGSform_All.TextBox_Results14.Value = .Cells(.Range(strAddress).Row, 16).Value
                COGSform_All.TextBox_Results15.Value = .Cells(.Range(strAddress).Row, 9).Value
                COGSform_All.TextBox_Results16.Value = .Cells(.Range(strAddress).Row, 7).Value
                COGSform_All.TextBox_Results17.Value = .Cells(.Range(strAddress).Row, 1).Value
            End With
            GoTo EndLoop
        End If
    Next l

EndLoop:
    
End Sub

Thank you,
 

Attachments

  • LISTBOX.PNG
    LISTBOX.PNG
    21.4 KB · Views: 16

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I am not sure what are you listing in the list box, the address of the cell? I can't make sure about your current design (how you feel the list box, how you get the associated cell address into the list box, etc.). If you could perhaps give a bit more information, then we would know more about how you are relating data and try to help better.

Just as a side note, if you have a unique value column in your table, as shown in the sample range below as Field A, then you can basically search in the range, and retrieve data for the found row accordingly.

Sample (alternative) implementation:

VBA Code: 
Private Sub ListBox1_Results_Click()
Dim cll As Range
    Set cll = ActiveSheet.Columns(1).Find(ListBox1_Results.Value, ActiveSheet.Range("A1"), xlValues)
    'cll is the selected row, now you can fill the textboxes accordingly
    Me.TextBox1.Value = cll.Cells(, 1)
    Me.TextBox2.Value = cll.Cells(, 2)
    Me.TextBox3.Value = cll.Cells(, 3)
End Sub

1620157265869.png
 
Upvote 0
I have is a vendor spreadsheet macro button for search the list is big what I want is when click search select the vendor and if need to change some the information for the that vendor be able to change from the userform instead going to the cell and edit there the unique number for all of them is column A which is the vendor id.


SAMPLE_VENDORS.xlsm
ABCDEFGHIJKLMNO
1TYPE NAME TO FITER=>
2PRIMUS-LEDGER ACCOUNTSAGE ID:VENDOR A/C#GROUP$TYPETERMSBANK_IDTAXCO CTRORG UNITG/L CODEG/L DESCEMAIL 1
46911:Exited Facility Costs151 FR151 FRONT ST. WEST HOLDINGS LTDGENCADPRE-AUTHDURBMO1600EHSTON752 Primus Field Ops601432-13-500-000Network Sites - Utilities : R - NWPLN - GACCOUNTSRECEIVABLE@ALLIEDREIT.COM
56911:Exited Facility Costs151 FR151 FRONT ST. WEST HOLDINGS LTDGENCADPRE-AUTHDURBMO1600EHSTON852 Facilities and Admin601432-13-500-000Network Sites - Utilities : R - NWPLN - GACCOUNTSRECEIVABLE@ALLIEDREIT.COM
66915:Corporate Restructuring151 FR151 FRONT ST. WEST HOLDINGS LTDGENCADPRE-AUTHDURBMO1600EHSTON854 Primus Facilities601432-13-500-000Network Sites - Utilities : R - NWPLN - GACCOUNTSRECEIVABLE@ALLIEDREIT.COM
76920:Transaction costs151 FR151 FRONT ST. WEST HOLDINGS LTDGENCADPRE-AUTHDURBMO1600EHSTON854 Primus Facilities601432-13-500-000Network Sites - Utilities : R - NWPLN - GACCOUNTSRECEIVABLE@ALLIEDREIT.COM
86102:Facilities Repairs & maintenanceABC FIABC FIRE EQUIPMENT LTD.GENCADEFTNET30BMO1600EHST15195 Primus Inbound Sales - Residential783111-13-345-000Repairs & Maintenance - Facilities: R - S&R - GAccounting@382com.com
96102:Facilities Repairs & maintenanceABC FIABC FIRE EQUIPMENT LTD.GENCADEFTNET30BMO1600EHST15711 Primus Customer Care - Residential783111-13-610-000Repairs & Maintenance - Facilities: R - CUST SVC - G
106102:Facilities Repairs & maintenanceABC FIABC FIRE EQUIPMENT LTD.GENCADEFTNET30BMO1600EHST15713 Primus Technical Support - Residential783111-13-625-000Repairs & Maintenance - Facilities: R - TECHSR - G
116131:Office Supplies and PrintingACCINFACCESS INFORMATION MANAGEMENT OF CANADA ULCGENCADEFTNET30BMO1600EHSTON744 Primus IT Operations743140-03-415-000Office Supplies : G - IT - G
126160:Hardware/Software MaintenanceACCINFACCESS INFORMATION MANAGEMENT OF CANADA ULCGENCADEFTNET30BMO1600EHSTON744 Primus IT Operations783110-03-415-000Repairs & Maintenance : G - IT - Gfilebankorders@accesscorp.com
136160:Hardware/Software MaintenanceACCINFACCESS INFORMATION MANAGEMENT OF CANADA ULCGENCADEFTNET30BMO1600EHSTON854 Primus Facilities783110-03-000-000Repairs & Maintenance : G - G - Gfilebankorders@accesscorp.com
146103:Facilities other operating expensesADT CAADT CANADA INCGENCADEFTNET30BMO1600EQSTGST854 Primus Facilities743000-03-000-000General Office Expenses : G - G - Gfilebankorders@accesscorp.com
156130:Postage and FreightADTRANADTRAN, INC.GENCADEFTNET30BMO1600EQSTGST0 None742120-03-000-000Courier & Delivery : G - G - Gdirectdeposit@adt.ca
166130:Postage and FreightADTRANADTRAN, INC.GENCADEFTNET30BMO1600EQSTGST753 Primus Network Engineering742120-03-550-000Courier & Delivery : G - NSE - G
176145:Expensed hardwareADTRANADTRAN, INC.GENCADEFTNET30BMO1600EQSTGST753 Primus Network Engineering760000-03-550-000Computer : G - NSE - G
186173:Consulting & Professional ServicesADVAN ADVAN TECHNOL INCGENCADEFTNET30BMO1600EHSTON745Primus - IS Software Sys Dev711143-03-425-000Subcontractors - Outsource : G - IS - G
196091:Advertising and brandingAEROPLAEROPLANGENCADEFTNET30BMO1600EQSTGST201 Primus Marketing - Residential720010-03-330-000 
206111:Corporate telecommunications expenseALIANTALIANT TELECOM INCCOGCADEFTNET30BMO1600EHST15744 Primus IT Operations760020-03-415-000Telephone : G - IT - G
216131:Office Supplies and PrintingALLIANALLIANCE PROTECTIONGENCADEFTNET30BMO1600EHST15195 Primus Inbound Sales - Residential743140-13-345-000Office Supplies : R - S&R - G
PRIMUS
Cell Formulas
RangeFormula
B4:B10,B14:B21B4=LEFT([@[VENDOR ]],6)
N4:N21N4=IFERROR(VLOOKUP([@[G/L CODE]],Table2,2,FALSE),"")&""
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:G3,F4:F21,G3:P21,A3:E21Expression=OR(CELL("col")=COLUMN(),CELL("row")=ROW())textNO
Cells with Data Validation
CellAllowCriteria
L3:L21List=Org_Unit
 
Upvote 0
Question: Have you ever considered using the Excel Data Form feature for this instead of creating a custom data filter/entry/edit userform? See the following for more info about Excel Data Form.

Add, edit, find, and delete rows by using a data form - Microsoft Support

If a worksheet contains so many columns that you can't see them all at a glance, use a form to make entering and browsing for data easier.
support.microsoft.com support.microsoft.com

I believe more info will be needed: Any helper will need your userform VBA code to understand how you set up Listbox.

Note: Hopefully you didn't use any live data in your sample data above.
 
Upvote 0
this is the userform vba code used:

VBA Code: 
Private Sub ListBox_Results_Click()
'Go to selection on sheet when result is clicked

Dim strAddress As String
Dim l As Long

For l = 0 To ListBox_Results.ListCount
If ListBox_Results.Selected(l) = True Then
strAddress = ListBox_Results.List(l, 1)
ActiveSheet.Range(strAddress).Select
'Populate textboxes with results
With ActiveSheet
           
COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 3).Value
COGSform_All.TextBox_Results3.Value = .Cells(.Range(strAddress).Row, 13).Value
COGSform_All.TextBox_Results4.Value = .Cells(.Range(strAddress).Row, 10).Value
COGSform_All.TextBox_Results5.Value = .Cells(.Range(strAddress).Row, 14).Value
COGSform_All.TextBox_Results6.Value = .Cells(.Range(strAddress).Row, 4).Value
COGSform_All.TextBox_Results8.Value = .Cells(.Range(strAddress).Row, 5).Value
COGSform_All.TextBox_Results9.Value = .Cells(.Range(strAddress).Row, 6).Value
COGSform_All.TextBox_Results10.Value = .Cells(.Range(strAddress).Row, 11).Value
COGSform_All.TextBox_Results11.Value = .Cells(.Range(strAddress).Row, 12).Value
COGSform_All.TextBox_Results12.Value = .Cells(.Range(strAddress).Row, 8).Value
COGSform_All.TextBox_Results13.Value = .Cells(.Range(strAddress).Row, 15).Value
COGSform_All.TextBox_Results14.Value = .Cells(.Range(strAddress).Row, 16).Value
COGSform_All.TextBox_Results15.Value = .Cells(.Range(strAddress).Row, 9).Value
COGSform_All.TextBox_Results16.Value = .Cells(.Range(strAddress).Row, 7).Value
COGSform_All.TextBox_Results17.Value = .Cells(.Range(strAddress).Row, 1).Value
End With
GoTo EndLoop
End If
Next l

EndLoop:
   
End Sub
 
Upvote 0
josros60 said:
no help?
Click to expand...
  1. Is your current code working correctly to get the selected vendor information filled into the text boxes? (It has some mistakes, but I am trying to understand this part is already working).
  2. How do you populate data in your ListBox in the first place? Are you using the RowSource property OR using a subroutine to fill data into the ListBox? I am trying to find out if there is a connection between the ListBox and the worksheet data. I can't see that without seeing what and how you have data in your ListBox.
  3. Does column A contain unique values?
Side note for the alternative solution I suggested: I gave you the link for the Excel Forms, and you didn't answer if it would work or not for your needs. I believe that it will work perfectly without single lines of code.
 
Upvote 0
1. yes all vendor information filled into the text boxes
2. Tried form but isn't that great for me


thank you,
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Invalid qualifier
Replies
9
Views
460
josros60
J
J
  • Question
Adding a year today total textbox.
Replies
6
Views
705
DanteAmor
DanteAmor
R
  • Question
Textbox in User-form displaying three digits (first digit is a 0)
2
Replies
10
Views
1K
DanteAmor
DanteAmor
J
  • Question
Add Currency format
Replies
5
Views
652
boxxcar
B

Forum statistics

Threads
1,215,124
Messages
6,123,187
Members
449,090
Latest member
bes000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top