 |
04-08-2012, 02:16 PM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
displaying data in a textbox after a combo box is selected
|
hi
i have a user for with a combobox and 5 text boxes, the combobox is liked to a sheet in my work book and after each list item are some details.
im wondering how when i select and item in the list in the combobox how the data next to it in the sheet can be displayed in the text box.
eg
list 1 Data 1 Data 2 Date 3
a 1 11 111
b 2 22 222
c 3 33 333
d 4 44 444
in userform combobox "a" is selected and in textbox 1 "1" textbox 2 "11" textbox 3 "111" ect ect
thank you
|
|
04-09-2012, 01:54 AM
|
 |
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
Let's assume your data is stored in a worksheet with code name "DATA" and “ComboBox1” is the name of your combo box in the userform “MyUserform”. You may therefore proceed as follows using the ComboBox change event:
Code:
Private Sub ComboBox1_Change()
'1) Retrieve the selected item value in ComboBox1 change event
Dim TheValue as String
TheValue = MyUserform.ComboBox1.Text
'2) Find the corresponding row in your worksheet assuming the key to look for is in first column of "DATA"
Dim TheSearch as Object
Dim TheRange As Range
Set TheRange = DATA.Range(DATA.Cells(1, 1), DATA.Cells(DATA.UsedRange.Rows.Count, 1))
Set TheSearch = TheRange.Find(What:=TheValue, After:=TheRange.Cells(TheRange.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'3) Populate the textboxes, assuming the data to display is in columns 2, 3, and 4
MyUserform.TextBox1.Text = DATA.Cells(TheSearch.Row,2)
MyUserform.TextBox2.Text = DATA.Cells(TheSearch.Row,3)
MyUserform.TextBox3.Text = DATA.Cells(TheSearch.Row,4)
End Sub
|
|
04-10-2012, 10:24 AM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
when i use this code an error is returned on this line
Set TheRange = DATA.Range(DATA.Cells(1, 1), DATA.Cells(DATA.UsedRange.Rows.Count, 1))
i have changed the word "DATA" to the name of my sheet which is "JobList"
so looks like
Set TheRange = JobList.Range(JobList.Cells(3, 2), JobList.Cells(JobList.UsedRange.Rows.Count, 1))
is that correct?
thanks ash
|
|
04-10-2012, 11:31 AM
|
|
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
|
As explained above, "DATA" is the code name (not worksheet name!) of your worksheet that needs to be replaced by your own worksheet code name. You can check the code names of your worksheets in the VBA editor on the left pane in "Microsoft Excel Objects".
|
|
04-10-2012, 12:50 PM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
i now have an error on this line
frmFindJob.cbofindcustomername.Text = Sheet2.Cells(TheSearch.Row, 4)
|
|
04-10-2012, 02:30 PM
|
|
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
Probably because the search key could not be found. You can check if the search returns a match using:
Code:
If Not TheSearch is Nothing then
...
Else
Msgbox "No match found! The Combobox / worksheet is erroneously populated!"
end If
|
|
04-10-2012, 03:01 PM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
still the same error line.
|
|
04-10-2012, 11:31 PM
|
|
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
|
04-11-2012, 10:00 AM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
rub time error '91'
object variable or with block variable not set
|
|
04-11-2012, 12:34 PM
|
|
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
|
This is an error that occurs when you are using a variable property that is not defined.
In that case, this happens when TheSearch is nothing (e.g. using "TheSearch.Row" produces that error when no match was found, i.e. when TheSearch is Nothing).
See my post above.
|
|
04-11-2012, 02:20 PM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
i put that in the code and it still came up with the same error. here is the code ive put in.
Private Sub cbofindjobnumber_Change()
'1) Retrieve the selected item value in ComboBox1 change event
Dim TheValue As String
TheValue = frmFindJob.cbofindjobnumber.Text
'2) Find the corresponding row in your worksheet assuming the key to look for is in first column of "DATA"
Dim TheSearch As Object
Dim TheRange As Range
Set TheRange = Sheet2.Range(Sheet2.Cells(3, 2), Sheet2.Cells(Sheet2.UsedRange.Rows.Count, 1))
Set TheSearch = TheRange.Find(What:=TheValue, After:=TheRange.Cells(TheRange.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'3) Populate the textboxes, assuming the data to display is in columns 2, 3, and 4
cbofindcustomername.Text = Sheet2.Cells(TheSearch.Row, 4)
txtfindcustomeraddress1.Text = Sheet2.Cells(TheSearch.Row, 5)
txtfindcustomeraddress2.Text = Sheet2.Cells(TheSearch.Row, 6)
If Not TheSearch Is Nothing Then
Else
MsgBox "No match found! The Combobox / worksheet is erroneously populated!"
End If
End Sub
there is data in the corresponding cells in columns 4,5 and 6
thank you
|
|
04-11-2012, 02:31 PM
|
|
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
My best advise would that you first try to understand what the code does.
Then you would come to the conclusion that the use of TheSearch.Row has to be in the If Not TheSearch Is Nothing part of step 3):
Code:
If Not TheSearch Is Nothing Then
cbofindcustomername.Text = Sheet2.Cells(TheSearch.Row, 4)
txtfindcustomeraddress1.Text = Sheet2.Cells(TheSearch.Row, 5)
txtfindcustomeraddress2.Text = Sheet2.Cells(TheSearch.Row, 6)
Else
MsgBox "No match found! The Combobox / worksheet is erroneously populated!"
End If
This means as well your ComboBox / worksheet is indeed erroneously populated... |
|
04-11-2012, 02:50 PM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
im sorry im really new to vb so learning as i go, ive put that code in but still comes back with the same error
|
|
04-11-2012, 03:03 PM
|
|
Centurion
|
|
Join Date: Jul 2009
Location: Earth
Posts: 155
|
|
|
Is TheRange correctly defined (starting at column 2 then ending at column 1) ?
Can you add the form reference "frmFindJob." in front of your three textboxes (cbofindcustomername, txtfindcustomeraddress1, txtfindcustomeraddress2) as you did with cbofindjobnumber?
Otherwise can you post (attachment) a minimal sample file that allows to reproduce your issue?
|
|
04-11-2012, 03:32 PM
|
|
Newcomer
|
|
Join Date: Mar 2012
Posts: 15
|
|
|
i have sorted it, thank you very much for your help i was being an idiot i had changed the code of the sheet so it was not "sheet2"
thank you so much for your help sorry it took me so long to find my simple error
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|
