List box posting results in last active cell?

greysrule

New Member
Joined
Mar 15, 2004
Messages
6
Petty cash disbursement list
I have a two column GL account list that using a List Box gives me the result needed in a set cell. (for example c2)

Ie.
Description shows
Electricity

Result is
15871

The sheet works great, I would just like the result to be placed into the correct cell, so that I have one less step of entering the answer into the correct cell/

Is there a was to have the result placed in the last active cell?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
OK, dumb question, but is this one value string separated by a carriage return in a single cell? Any clue would be welcome regarding the column or sheet name or anything that would help a code-writer know where to start.

Why do greys rule? I have own an African Grey Congo parrot and if that's what you are referring to I totally agree. If it's hair color...well, I dunno 'bout that.
 

greysrule

New Member
Joined
Mar 15, 2004
Messages
6
Thanks for your response,
Sorry, very new to Excel but have learned TONS from this board!

Basically I have column Y and AA
AA is the descriptions
Y is the accounting code.
Using a pull down list I get the number of the item selected.
I did forget this step,
=INDEX(Y267:Y401,MATCH(AC267,Z267:Z401,FALSE),1)
Using the above I get the accounting code result.

The above works fine, just wanting to have the result follow me around the worksheet as I work, (Yea, I know, I could just type it in, I know this can be done!)
I have been reading about ways of doing this and just cant seem to get it.

Yes, African Grey Parrots RULE! (no dirty green birds!)
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
Sorry, I'm still lost. First you said you have a ListBox, and maybe that's what you meant when you later said "pull-down list" but I want to be sure you mean ListBox and not a data-validated cell with drop-downs.

I assume there is no userform involved...that if you are using a ListBox it is an ActiveX ListBox on the worksheet itself. Is that right?

So you have cells with a ListBox or data validation in Accounting code column Y and the formula you posted is for the Description to appear in column AA. Is that right?

Now, what exactly do you mean by
"just wanting to have the result follow me around the worksheet as I work"?

Maybe it's me but I just don't get what you are after. Try to explain as if no one ever saw your worksheet.
 

greysrule

New Member
Joined
Mar 15, 2004
Messages
6

ADVERTISEMENT

First thing, a big thanks to everyone that posts. I never would be this far along without the questions and answers.

I think I got the information for you, so that you can help me!
This what it looks like,
Using the List Box I can scroll thru the GL Account Descriptions and get the correct GL Account code.
It lists the answer correctly, just would be cool to have post it list on the line I am working on!
For example I entered a bill for a yard sign, looked up the GL Account for Signage and got the answer of 1648 displayed in cell D11.
Is there a way to have the answer of 1648 enter itself on the line or cell I am working on? Ie. D17?


(I could not get the list box to show in HTML maker)

Ken
pettycash.xls
ABCDEFGHIJ
5GLCodeGLCodeDescription
6Alistboxisusedtoget16401Furn&Fixtures3
7theGLAccountbelow16452Furniture&Fixtures
816483Signage 
916704Equipment
1016905ConstructionInProgress
11GLAccount164820056AccountsPayable
1261107AdvertRadio/Tv-Musak-HStore
1361158Advertising-AptGuides
1461209Advertising-Brochures/Pamph
15612510Advertising-DirectionalSign
16DateVendorDescriptionGLAccountAmount613011Advertising-Paper/Chronicles
1702/15/04FredsYardsign1648$151.12613512Advertising-Promotions
18614013Advertising-YellowPages
19614514MiniModelThemes(Monthly)
20614615ModelFurnitureExpense
21615016Promotions-Parties
22615517Promotions-Refreshments
23616018ReferralFees-Brokers
24616519ReferralFees-ResIncentive
Sheet1
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
Assuming it's an ActiveX ListBox, right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet. Modify for ListBox name if it is not ListBox1.

Private Sub ListBox1_Click()
Cells(Cells(Rows.Count, 4).End(xlUp).Row + 1, 4).Value = Range("D11").Value
End Sub
 

greysrule

New Member
Joined
Mar 15, 2004
Messages
6
Thanks, but I must not be doing something correctly,
I right clicked the sheet1 name and selected “view code”
In the box that opened, I copied and pasted the code.
I did not change the box on the top left (General) is selected.
The one on the top right changed to ListBox1_Click
I am suspecting that my problem is that the listbox is not named ListBox1, I am going to nose around for awhile and see if I can figure this out.

Ken
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
All you need to do to see that is to go into Design Mode on your spreadsheet and doubleclick the ListBox. That will take you to the sheet module and create a procedure that looks like the below one, with the cursor flashing in between the Sub and End Sub line. Then all you'd need to do is paste the code I sent (it's just a one-liner) in between those two lines and delete the code I sent that refers to a ListBox name you don't have.

Private Sub ListBox1_Click()

End Sub


Again, this depends on your information, that you *are* using a ListBox, that it *is* an ActiveX ListBox, *not* a data validation list as we've discussed already.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

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
Top