1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro Dropdown Menu Cell Contents

Discussion in 'Business Applications' started by xpertus, Feb 1, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    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
     
  3. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    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. :D

    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:

  5. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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....
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    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.
     
  7. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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. :(
     
  9. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    Thanks for the warning -

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

    HELP!
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    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:

  11. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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
     
  12. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    67,490
    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
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,268
    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:

  14. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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
     
  15. xpertus

    xpertus Thread Starter

    Joined:
    Feb 1, 2010
    Messages:
    36
    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
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/899510