Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Sorry for trouble u all again... (I know I'm quite troublesome...)
My problem is like this: I got a userform, which consists of a comboBox to let people choos their name. The row source of the comboBox is refer to sheet2, wher sheet 2 store the user name, and their hourly payment rate. They got different hourly payment rate which is $20 and $30 respectively. I had tried several methods... But I duno how to do so that when the select the username that time, it will match the username in sheet and then copy their hourly payment rate and placed in sheet 1 .... Wha I had done is, it just copied and paste the user name only... Sample Data in sheet 2: column A column B David $20 Dould $30 Owen $20 Hulad $20 How I can do so that when the user select "David" in the comboBox, hi hourly payment $20 will be select and paste in the column next to hi username in sheet 1 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Another problem is, my user form got a listbox in it. So, when the use
select their name in combobox, and key in some data in the userform an submit that time, their details will be display in a listbox that i also in the userform. My userform is for 30 user to fill in their daily activity. So, over month, there will be many data in the spreadsheet. How I can do so tha when they select "view" button that time, they can just see what the had typed in over the month? and they cannot view what others ha typed? My listbox function now is just can display all the entries that ha made.... but not specify to each user.... How I can specify so that th user can just view his entries only? Any help would be very appreciate.... : -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Why not just use a lookup in Sheet 1, like
=VLOOKUP(A1,Sheet2!A1:B50,2,False) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "unplugs " wrote in message ... Sorry for trouble u all again... (I know I'm quite troublesome...) My problem is like this: I got a userform, which consists of a comboBox to let people choose their name. The row source of the comboBox is refer to sheet2, where sheet 2 store the user name, and their hourly payment rate. They got 2 different hourly payment rate which is $20 and $30 respectively. I had tried several methods... But I duno how to do so that when they select the username that time, it will match the username in sheet 2 and then copy their hourly payment rate and placed in sheet 1 .... What I had done is, it just copied and paste the user name only... Sample Data in sheet 2: column A column B David $20 Dould $30 Owen $20 Hulad $20 How I can do so that when the user select "David" in the comboBox, his hourly payment $20 will be select and paste in the column next to his username in sheet 1? --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Dear Bob Phillips
Can u describe more about the VLookup function..? And how I ca do that programmatically? ComboBox & ListBox problem Why not just use a lookup in Sheet 1, like =VLOOKUP(A1,Sheet2!A1:B50,2,False) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Hi,
try somthing like if me.combobox1.value = "Dave" then sheets(1).cells(yourow, your colum).value = 20 else if me.comobo1.value = "next" then and so on if i was you though, i would bet up each user with a seprate part on the sheet, say 10 cols ecah, then you could just fill down the hourly rate colum, and... to show what they had entred, use if combobox.value = "Dave" then hide cols not for this person etc. not very good, becasue the perosnse could easily pick some else name, so you may need to issue passwords?, you should also use the very hiden method to hide the workbook, and copy the data accross when a user request it. Good Luck Ross |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
res = application.Vlookup(Range("A1").Value, _
worksheets("Sheet2").Range("A1:B50"),2,False) if not iserror(res) then cell.offset(0,1).Value = res End if -- Regards, Tom Ogilvy "unplugs " wrote in message ... Dear Bob Phillips Can u describe more about the VLookup function..? And how I can do that programmatically? ComboBox & ListBox problem Why not just use a lookup in Sheet 1, like =VLOOKUP(A1,Sheet2!A1:B50,2,False) -- HTH Bob Phillips .. looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Thanks for the response... Ross and Tom...
Tom, I am a bit slow... and can't catch what u trying to guide me.. :< Ross, ur method is ok... And I'm using now....Anyway, there ar problems if doing so....below is my code: If ComboBox1.Text = " " Then MsgBox "You must choose your name. " Exit Sub Else If ComboBox1.Text = "David" Then wage = 20 Else If ComboBox1.Text="Dould" then wage = 30 Else If ComboBox1.Text="Owen" then wage = 20 Else If ComboBox1.Text="Hulad" then wage = 20 ....... ......... ........( another 30+ name,data here) ........ End If End If End If End If This is my problem... My data are all store in sheet 2 from colum A1:B31....Since I don't know how to compare the entries that I get an compare to the data that I stored in sheet 2, I use this method.... an I will need to key in up to 30-40 lines of codes because of this nam comparison.... How I can do to shorten my codes?? -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Juz to make somethng clear...
My problem is when I select "name" from combobox in sheet 1, it wil get the wage from sheet 2 that I had stored there and display in shee 1 in the same row as the entries that I had made... Sorry if got anythings confuse u all... I know my description for m problem is quite poor... :< And I hope I can listen to any solution to tat... Else, there is tedious job for me to code the long codes.. -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Ok... With the help of Tom, I had figure out I can use vlookup to get m
data in sheet 2... Can u all help me to debug the following code? I gave me error msg... Sorry for asking so much Q.. CostCenter = Application.VLookup(Range("RangeName").Value, _ Worksheets("sheet2").Range("Rlookup"), 2, False) If Not IsError(CostCenter) Then cell.Offset(0, 1).Value = CostCenter End If wage = Application.VLookup(Range("RangeName").Value, _ Worksheets("Sheet2").Range("Rlookup"), 3, False) If Not IsError(wage) Then cell.Offset(0, 1).Value = wage End I -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox & ListBox problem
Ok... I think I know the answer... after many time I had tried!!!
:) Cells(NextRow, 9) = "=VLOOKUP(RangeName,Rlookup,3,FALSE)" Juz insert the vlookup function like this in my vba code... and I go it! : -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combobox/Listbox Difference | Excel Discussion (Misc queries) | |||
Combobox v Listbox | New Users to Excel | |||
Combobox/listbox | Excel Discussion (Misc queries) | |||
Populating combobox/listbox | Excel Programming | |||
Combobox or Listbox | Excel Programming |