Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel Macro Dropdown Menu Cell Contents


(!)

xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
02-Feb-2010, 01:27 PM #16
Wow! Thanks that worked.

However, it is hard to read, and format.

A way around this would be ....

Copy the contents of INPUT!D5 to a new location, such as INPUT!I5, and then cop0y the comments, to that cell - we they would appear just as they are seen in ACTIVE!Ax
Is this doable?

As for updating the cell contents..

I want to update the corresponding ROW in ACTIVE!H - by incrementing it by +1. [this is the row that corresponds to the name in INPUT!D5

Thanks a bunch.

Errol
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
02-Feb-2010, 01:46 PM #17
My 2 cents.

Insert a comment at G2 on Active, set it to be permanently on display.

Then a slight modification to turbo's code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim busNcell As Long, R As Range
Set R = ThisWorkbook.Names("Bus_Name2").RefersToRange
On Error Resume Next
If Target.Cells = Range("D5") Then
busNcell = WorksheetFunction.Match(Range("D5"), R, 0)
Range("G2").Comment.Text Text:=Sheets("Active").Range("A" & busNcell + 4).Comment.Text
End If
End Sub
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
02-Feb-2010, 02:56 PM #18
Guys!

You folk are experts!
I am a slow learner - and a beginner.
So each time I add something - it messes up something else.

Could one of you kindly incorporate the Macro - and the Function, and upload it, or email me the file to errolw AT xpertus DOT com
Then I will go through it and learn how this is set up,and modify it where necessary.

This will certainly speed up my path on teh learning curve.

Thanks a bunch.

Errol

Last edited by valis; 03-Feb-2010 at 11:25 AM.. Reason: edited out email
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
02-Feb-2010, 06:24 PM #19
1. I stripped lots of rows from the Active sheet to shrink the file size (I don't have WinZip); you can rebuild them from your copy.

2. I converted Bus_Name2 to a dynamic named range using the formula:

=OFFSET(Active!$A$1,4,0,COUNTA(Active!$A:$A)-4,1)

the "minus 4" being to compensate for the text entries on Active (A1, A2, A12, A14). You can find out about dynamic named ranges at the Contextures site, which from the file properties I see you're already familiar with.

3. Added large comment at G2 on Active -- using the dropdown at D5 updates it, largely thanks to turbo's worksheet code. As you'll see, it can still be messy due to how the comments are to begin with.

4. I combined the 2 "go to sheet x" macros into one: GoToSheet. This is done by naming the buttons, then using the Application.Caller method.

5. I tweaked turbo's UpdateComments_PaymentNo some. Since I'm not really in his league, I added 'new at the end of any line I added or changed for clarity/the sake of his reputation.

Select "Affordable Tire", because the comment's quite short. Enter an amount in D8. Enter "New comment" in D10. Run UpdateComments_PaymentNo.

When I do this, the updated comment (on Active) has "New comment" at the start (although I'm not sure that vbCrLf actually works) & Active!H7 increments from 3 to 4.

Various "top & tailing" on Input may be required (clearing input cells, updating the comment, etc.), you'll have to let us know.

6. Please remove the email address from your last post or you may get loads of spam.

HTH
Attached Files
File Type: xls Julian.xls (84.0 KB, 94 views)

Last edited by bomb #21; 02-Feb-2010 at 06:31 PM..
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
02-Feb-2010, 06:40 PM #20
Updating Count
Wow! I just don't know how to thank you guys!

You have been so generous with your time and talent.

One thing.. I could not get the count to update by +1 [in ACTIVE].
You said it when you used "ALEX" - it updated from 3 to 4.
But I tried it even with other names - and it did not increment the value.
Can you please check?

Can we clear all entries - each time? [from INPUT?]

Thanks a bunch....
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
02-Feb-2010, 06:53 PM #21
I tried it for another -- A-Jacks - and it worked. An amount has to be entered, and you have to run the code (UpdateComments_PaymentNo) manually (for now). Was it/did you?

"Can we clear all entries - each time?"

Best way is to define a named range for those you want to clear (e.g. "InputClear", refers to =Input!$D$8,Input!$D$10), then use:

Sheets("Input").Range("ClearInput").ClearContents

Or something like that.

EDIT: Please remove the email address from your previous post or you may get loads of spam.

Last edited by bomb #21; 02-Feb-2010 at 06:59 PM..
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
02-Feb-2010, 06:59 PM #22
Update comments
Ahh!

I did not run it manually....
I assumed that step was embedded in the Macro "Add to Database"

I'll play with this heavy programing stuff.

Thanks bunch.

Errol
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
02-Feb-2010, 08:04 PM #23
Errol,
to reiterate what bomb said - please go back to post 18 and remove or reformat your email address (something like
name
at
address
dot
com
would work) - bots troll sites like this all the time and will spam you at your company forever.
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
02-Feb-2010, 08:07 PM #24
Email address
Thanks for the warning -

I don;t know how to go back to a previous posting..

HELP!
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Feb-2010, 05:37 AM #25
Quote:
Originally Posted by xpertus
Thanks for the warning -

I don;t know how to go back to a previous posting..

HELP!
At the bottom-right of your posts, you should see:

Report/Edit/Quote/+/Reply

You want the 2nd one.

I took out the formulas in D6 and I8 (Active) ; if you're using worksheet code to pull in comments info, you might as well use it to pull in those too - makes it more "secure", in my opinion.

I also discovered that deleting D5 (i.e. Lessee Name = blank) sent the Worksheet_Change code into overdrive -- at least on my computer.

For now, I've added a couple of EnableEvents lines to the Worksheet_Change code, as a precaution. More later.

("the count to update by +1 [in ACTIVE]" can easily be done within UpdateLogWorksheet)
Attached Files
File Type: xls Julian.xls (86.5 KB, 83 views)
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
03-Feb-2010, 09:15 AM #26
Dropdown Menu
Thanks a bunch again....

Have another interesting question....
Just curious; but may be very useful.

When I click on the arrow - in the drop-down menu- I see the list of clients...

IS there a way - to speed up the search - such that - when I type the first letter - it goes down to the group of names starting with that letter...
Saves a lot of time.. search...?

Or better still - without touching the pull down arrow, simply typing the first letter would open up the list and go to the corresponding group..?


Thanks
valis's Avatar
Moderator with 63,270 posts.
 
Join Date: Sep 2004
Location: as above
03-Feb-2010, 11:29 AM #27
xpertus:

I edited your email out of your posts. It was brought to my attention by slurpee. If you want a real fast way to end an email addresses usefulness, posting on public forums such as this is a way to sign up for hundreds of thousands of emails a day. This is why you have to take the 'capcha' test; we need to make sure you are human.

Yer in good hands with these guys; I've known them for several years, and they've always helped me out. Point is, in order to learn from 'em, you got to listen to 'em, and both bomb and slurpee have pointed that out to you.

cheers,

v
__________________
Microsoft M.V.P. - Windows IT Professional | M.C.S.A. | M.C.P. - MS Server 2k3 | blog | rate me

"Ask Bill why the string in function 9 is terminated by a dollar sign. Ask him, because he can't answer. Only I know that". - Gary Kildall
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
03-Feb-2010, 11:32 AM #28
I don't know a way you can do that with "standard" data validation; check the Contextures site, there might be something on there.

Or: you could have something like the attached: a VBA combobox with some code.

The advantages are that you can set the number of dropdown items (20 "ListRows", in the case of the attached) and, with the box clear, typing (e.g.) "T" will bring up the first in the list beginning with "T" -- and then you can use the Up & Down arrows.

Something like that?
Attached Files
File Type: xls errol.xls (32.0 KB, 88 views)
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
03-Feb-2010, 11:59 AM #29
Edit previous emails
Thanks = will check it out.
You have been a good teacher.

By the way.. can someone tell me how to delete my email address from a previous posting...

Thanks

E
xpertus's Avatar
xpertus xpertus is offline
Computer Specs
Member with 36 posts.
THREAD STARTER
 
Join Date: Feb 2010
Experience: Beginner
03-Feb-2010, 12:01 PM #30
Oops - did not see the email response -
Thanks for taking care of it...

I agree your techies are very knowledgeable.
But what makes them special is that they are so open to sharing their know-how!
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
cell-contents, excel, macro

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑