[xpertus]

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]

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]

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

[bomb #21]

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

[xpertus]

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]

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.

[xpertus]

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]

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]

Thanks for the warning -

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

HELP!

[bomb #21]

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)

[xpertus]

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]

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

[bomb #21]

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?

[xpertus]

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]

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!