Replies

My current formula approach cannot help because it is limited to sorting numeric values. It may be possible to adapt but that would be far more complex than any VBA solution would ever be.

Attached is a solution using only 2 columns, as required.

Each column requires a fairly complex array formula which essentially tests for the next customer in the sequence from either column and then displays either the number (if it is is in both source columns) or ‘New’ or ‘Lost’ if it is in only 1.

The formula also contains an extra if statement to test if the maximum customer number has already been reached. This would allow for an output table to built that does not need to be the exact size. Further power could be added to this solution by using dynamic names for the data in columns A and B, this would ensure that once created the formulae would always include ALL customer numbers in the calculation.

I hope this helps

Hans,

This was one of the many things I tried yesterday to no avail. However, I just tried it again and today it works.

Thanks,

Steve,

Thanks for that response. Unfortunately what I should have added in my original post is that I do not want to have the responsibility of producing this file, the intention is for a couple of users to do it by clicking on the button I provide them.

I hadn’t really thought about the add-in option, I think that is what I shall do.

Thanks for the suggestion

Thanks for the suggestion.

I have finally included this functionality and have settled on a Selection_Change event identifying help prompts to find the desired help message from a library and display it in a message box. I wanted a solution which did not involve another file that had to always be associated with this file and I also have no desire to be the long-term administrator of this Tool so I wanted to make it as easy as possible to add and edit help text.

Thanks again,

Hans,

Thanks for your response. It was along the lines of what I was thinking would be an improved solution. However, I thought it worthwhile to see if any cleaner approaches could be thought of.

I have incorporated this with a Selection_Change event and a test for a specific 4 character string at the start of the Target.Value with the text to lookup and display stored as the remainder of the string. This should:
1. allow me to limit the code required to a Selection_Change event on each worksheeet and the global ShowHelp macro
2. eliminate the possibility to leave orphaned buttons
3. make it much easier to add, delete and copy help text to cells
4. make it easier to create and edit help text
The only downside for me is losing the image which is very similar to Excel’s Help icon.

I am planning to incorporate this approach into the tool unless any further comments or suggestions are made before I get around to it.

Thanks again,

I use the following code to cycle through every cell in a selection and display the precedent arrows. If you run this on your ‘Source’ worksheet it should do what you are looking for.

Sub RangeDependentsShow()
Dim rngCell1 As Range
Application.ScreenUpdating = False
For Each rngCell1 In Selection
rngCell1.ShowDependents
Next rngCell1
Beep
Application.ScreenUpdating = True
End Sub

Jefferson/Hans,

Thanks for your help.

Hans I was in the middle of replying to Jefferson to ask how I broke the link to the custom template I was trying “” instead of “Normal” when I saw your reply that cleared the muddy water for me.

To answer some of the points you raised:
The users of the template have very basic PC skills so I wanted a solution that didn’t rely on any external source for the numbering and also on that would allow them to reset or change the number if absolutely necessary. This was also part of my decision to use the built-in properties as was my inexperience in VBA for Word. Finally, as far as I know I also do not need to worry about metadata as the electronic file doesn’t leave the company.

Thanks again for your help,

Cindy,

I tested it before I posted it so the procedure should work right now as it creates the object reference for itself.

I picked up some code from somewhere and adapted it to set my needs, I have edited it to suit your requirements:

Sub TidyVBA()
‘ Removes VBA Modules from Workbook
Dim WBCodeItem As Object
‘ Create an object reference for the VB Project

Hans,

Thanks for your comments.

1. I thought I may have been barking up a non-existent tree as I had already spent a lot of time scouring the object browser. I save a copy of the master and remove the unnecessary worksheets, forms and modules during the process, due to the complexity of the file I think this is the only reasonable approach. I think the answer to my problem lies in removing only redundant actions from my event macros so that the child file can still manipulate the toolbar.
2. I never really thought about taking a simple approach, it works fine.

Thanks again.

Legare, Brett and Steve,

Thanks for your comments. Sorry I haven’t replied earlier but it is a work problem and I had no desire to follow the thread from home over th weekend or on my day off yesterday.

I had completely overlooked the relevance of Cancel in the event. Thank you for pointing out the error of my ways.

That works fine. I knew there was more than likely an easier way but neither a search through the help files or the Lounge came up trumps.

Thanks.

An alternative way?

1. Design a user form to suit your purpose.
2. Assign to a shortcut key.
3. With the appropriate mouse and driver, assign this combination to one of the mouse’s buttons.

I have designed a very personal utility for myself in this way and much prefer the design flexibility that the user form provides when compared to a menu.

Hope this provides some food for thought,