PDA

View Full Version : Solved: Userform and data validation



Pete634
06-23-2008, 06:07 PM
Hi, was wondering if anyone could help me with this little problem.

I have a spreadsheet containing various data and on one of the columns is a list of companies. This number of companies is not particularly large and each company is alligned to a specific team. There are about 4000+ rows of data.

I'm at the end of my code (which has removed unnecessary entries and sorted those that remain (but unfortunately this is not by company for reasons that are just not with explaining!) and the last thing I want the spreadsheet to do is add into the final column the name of the team that deals with the company. The company/team list is kept on a separate spreadsheet (in fact the workbook containing the company/team list contains all the code, the spreadsheet that it works upon contains no code which is exactly how I need it).

The problem is because of the many ways my idiot colleagues write the company name :banghead: i'm not going to get an exact match and it's virtually impossible (for me!) to get Excel to work it out.

HOWEVER, I wanted the spreadsheet to do the following: -
1) When an unrecognised company is identified, open a userform displaying the company name, with X number of buttons with the team names on
2) When the team button is pressed, not only is the data table updated with the correct team but also the company/team list updated to include "yet another damn variation of how to spell the same company name"

Trouble is, I cant figure out the relationship with my code and the userform to do this. If it helps, the last part of my code is: -

For N = 1 To FinalRow
If WStarget.Rows(N).Font = 1 Then
ThisWorkbook.Activate
Worksheets("Sheet1").Cells(5, 7).Select

Do
If ActiveCell = WStarget.Cells(N, 6) Then
WStarget.Cells(N, 7) = ActiveCell.Offset(0, -1)
Exit Do
Else
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

' NOW I NEED THE CODE FOR THE USERFORM TO OPEN,
'GET THE CORRECT TEAM VIA USER INPUT,
'UPDATE ALL RELEVANT LISTS,
'CLOSE AND THEN CONTINUE THE FOR LOOP.
'NOTE: I WANT IT TO DO THIS FOR COMPANY NAMES IN BLACK FONT ONLY.


Any ideas????

All comments would be greatly appreciated....including "Replace your colleagues" (and I've seriously thought of doing that!)

Thanks.

mikerickson
06-23-2008, 08:18 PM
If you replace your colleagues, can I have their parking space?

I'm thinking that in addition to the (correctly spelt) Company - Team list,
You could create a Mis-spelt Company Name - Correct Company Name - Team list.
(The correctly spelt names would start the second list.)
The reason for the two lists is that if a company switches teams, updating the misspellings could be made automatic.

All of the code for adding to the list could be kept with the UserForm. The Userform's only job is to add a row to the BadName-GoodName-Team list.

Some clever person might be able to export the BadName-GoodName to Word's auto-correct list, so your colleagues (oh, they bicycle to work, no parking space?, thanks any way) don't accidentaly send out a letter with the Company's name misspelt. Heck, you might incorperate the BadName-GoodName to Excel's Replace Text as you Type list.

JimmyTheHand
06-23-2008, 10:33 PM
All comments would be greatly appreciated....including "Replace your colleagues" (and I've seriously thought of doing that!)
Pete,
Your colleagues may have caused you some headache, but there is the other side of the coin. If it weren't for them, you wouldn't be here, asking at the Forums, which would be a great loss for everyone involved. ;) They forced you on a learning path, which may be irritating just now, but it surely pays off in the long run.

As for the problem itself, I think I would use a different aproach to it all. Of course I don't know the details, so what I suggest may not be achievable, yet it's worth considering.

In your place, I would clean up the already existing raw data, and prevent colleagues from screwing up the company names anymore. Otherwise, I would always be one step behind them, spending time fixing something that could/should have been done right in the first place.

So I would create a standard list of company names, and use data validation or some other tool to force people choosing from the list.
In the existing raw data I would replace the countless company name versions with the standard ones, (which task still needs a solution similar to what you asked for,) and feed the main program with the consistent raw data.

Jimmy

Aussiebear
06-24-2008, 03:36 AM
Pete, I too have been down the road with the variations that other users apply when adding names of suppliers to a list. In my case, a new record was added even if the name was correct but a variation of the Mail Service (M/S, M.S., MS, m/s, m.s., ms), just to name a few.... I've ended up with a master list that I alone maintain, and then I edit all the names, addresses etc to suit.

In my spreadsheet, I use data validation when an error appears, so that all users can then display the acceptable name/ address format.

greymalkin
06-24-2008, 11:21 AM
I'm going to 3rd the Data Validation route. There's no end to the amount of trouble and re-work you will be putting yourself through when you could stop it all at the source by forcing a list of acceptable entries on the users.

When you leave blank cells that users can type anything into you are allowing for human error.

Pete634
06-24-2008, 04:46 PM
Jimmy...point well received. If it wasn't for their idiocy I wouldn't look so good!

I may have mislead a little. The list I'm manipulating is obtained from a computer system (and I was also incorrect, there are 9000+ rows). The amendment of the company names is already under discussion where I work, but at the moment I'm stuck with it and it's not going to change any time soon. The good news is there is a finite number of ways the company has been input on to the system and there won't be anymore different ones. Eventually I'm going to pick up everyone via my method anyway, but one hopes I get my way and the number of variations is amended first.

Anyway. I digress. I've attached the spreadsheet in the hope that helps. Regretfully I cannot attach the data sheet for obvious reasons.

On the data sheet a row corresponds to the location of a risk on an commercial insurance policy.
Column A is the first part of the postcode of the risk
Column B the second part of the postcode
Column D is a two letter branch code
Column N is the building sum insured at for the risk
Column X is the total sum insured at the location

The purpose of the macro is to populate a new spreadsheet with postcodes relating to a particular branch (and a branch may have more than one code, hence the large yellow box for additional branch codes) where there is an accumulation/grouping of risk, but only where the chosen branch has the largest exposure. This bit works fine (upto ENDSORT: )

After this the code fails when trying to launch the userform (NOTE - I've only coded Button A)

The idea was to go through the new list and where a particular risk (in black text) is noted to add the corresponding team name to Column Y which would be obtained from the company/team list on the control worksheet. If it's not "on the list" then the userform would show this company name and require the user to input the team via a button which would then populate the list for future reference and the new spreadsheet before moving on to the next appropriate row.

Again, any help would be greatly appreciated....

Thanks.

JimmyTheHand
06-25-2008, 12:04 AM
Pete,

please see the attached workbook. There's a function inside which you may find useful. The function is called IdentifyTeam.
There's also a testing sub inside, called test. I copy the code here:
Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet2")
MsgBox IdentifyTeam(ws, "E13")
End Sub
In the above code:
ws is the location of the team/company list.
For testing purpose I created a simple list on Sheet2 (see the attached workbook). Important: The strucure of the list is such that the 1st row contains team names/identifiers, and below each team are the corresponding company names.
"E13" is the company name you are looking for. Replace this by anything you wish.Also all the code on the userform were changed to suit my purposes.

Test it and tell me what you think.

Jimmy

Pete634
06-25-2008, 02:56 PM
Oh dear. Seems I know even less than I thought I did when it comes to VBA. Couldn't understand any of your code Jimmy.

Unfortunately it didn't work.

However, you did get the userform to display whereas I get the message Run-time error '424': Object Required

If I can get past this issue I reckon my code might work, although I cannot say this with any confidence having compared your code to my incredibly basic code. Am I reading the wrong book or something?!

Think I need to amend the title of this thread to "My mind is blank".

lucas
06-25-2008, 08:44 PM
Hi Pete,
While "My mind has gone blank" seems like an appropriate title we would appreciate it if you would choose a descriptive title for your threads. I have changed the title for you.....

Looks like you have plenty of pointers for your problem....don't give up on it even if it means you have to start over.....sometimes happens for the better.

JimmyTheHand
06-25-2008, 11:47 PM
Unfortunately, I can't reproduce your Run-time error '424': Object Required error message, as I have no Grouping.xls to open, so when I click on the Sort button on Sheet1, the program crashes at the very first line of code. That is why I didn't make suggestions to alter your code but wrote my own.

Also, I'm sorry, I can't make any useful suggestions here, because I don't know what you mean by "it didn't work". A full information from your part should include the error message, if any, and the line of code where the error occurs, or a description of the misfunction, in case the program runs without error, only doesn't do what is required.

The only thing I can do now is to give some explanation on the code. I will do this on the test environment I set up in the attachment uploaded earlier.
1) Run the subroutine called test(). It does nothing more than calling the function IdentifyTeam(), feeding it with input arguments, and displaying the returned value.

Now, the function has two arguments, one is a worksheet where all teams and corresponding company names are listed. The other is the company name that is being searched for.

2) I use basically two ranges: rngTeam and rngCompany. These are defined by the lines that begin with Set rng...
rngTeam is the cells of team names, which is, in the attached workbook, Range("A1:I1").
rngCompany is the range of company names, Range("A2:I10").

3) Set Hit = rngCompany.Find...(etc.) performs a search for CmpName, inside the cell range called rngCompany. This is the same as using Excel's Find command from the Edit menu.

4) Here comes an examination of the result of search operation.
Hit Is Nothing criteria means that the value was not found in the specified range.
Not Hit Is Nothing means that the value has been found.
In case the value has been found, the range type variable called Hit will be assigned to the first cell where the value was found. (In ideal case there are no duplicates in the company list, so the first hit means the only one hit.)

5) So, in case the company name (= the 2nd argument of the function) was found, the function returns the corresponding team name, which is in the intersection of rngTeam (A1:I1), and the column where the company name was found.

6) Things get a little more complicated if the company name was not found. This is when the userform comes up. This is the Else branch of the If...Then...Else... statement.
The userform needs to be set up properly before being displayed.

7) There are 4 buttons on the form that correspond to teams. There is a 5th button which means that the team we are looking for is not represented by any of the 4 buttons. I'm not sure what the 6th button was originally meant for, in my code it means that the company doesn't belong to any teams.

I'm saving this now, and continue later.

JimmyTheHand
06-26-2008, 03:04 PM
Continuing my last post.

8) There can be more than 4 teams, but there are only 4 buttons, so you can display only 4 team names at the same time. To make all the teams available for choosing, I designed the code so that by clicking the 5th button (labelled as "Other") the user can discard the current 4 teams, and go to the next 4 ones, and the next 4 ones, etc. until he finds find the team he is looking for, or there are no more teams to display. This is what the Do..Loop statement does.

9) The variable called Subset indicates which 4 teams are currently displayed. In the uploaded exmple there are 3 subsets of teams:
When Subset = 1 then Buttons A to D correspond to Team1 to Team4, respectively.
When Subset = 5 then Buttons A to D correspond to Team5 to Team9, respectively.
When Subset = 9 then Button A corresponds to Team9, and the other buttons have no teams assigned.

10) Setting up the buttons for the current subset of teams includes
making the buttons A to D invisible
making those buttons, which have team names assigned, visible again
changing the button captions to reflect the team names of the current subset11) After the buttons are set up, the userform is displayed (Show method). It is very important that as long as the userform is up, the execution of the code of this (i.e. IdentifyTeam) function is suspended.

12) The buttons on the userform do only this:
loading their number into the Tag property of the form
hiding the form and returning code execution to function IdentifyTeamSo, clilcking on Button1 (A) loads the string "1" into the Tag property, Button2 (B) loads "2" there, and so on.

13) Upon returning from the userform, the form's Tag property is examined. If it is "5", it means that Button5 (labelled as "Other") was clicked, so the loop continues with the next subset of teams. Otherwise the loop exits.
Another cause of exiting the loop is if the value of Subset grows higher than the number of teams. This latter condition is evaluated at the beginning of the loop, and if true, it actually causes the code execution to exit the whole function, not just the loop.

14) When the user clicks on button 1, 2, 3, 4 or 6, the execution exits the loop. Further action depends on what button was clicked, that is, what string was put into the Tag property of the userform.
If it's "6" or empty string (which can happen if the form is closed without clicking on any buttons), then the IdentifyTeam function returns a boolean result with False value.
If it's "1" to "4" then the IdentifyTeam function returns the team name corresponding to the button that was clicked, and also puts the new company name into the first empty cell in the column of the choosen team.15) Finally, the form is unloaded, which means it's not simply hidden, but cleared out from memory.

Hope this helps.

Jimmy

Pete634
07-01-2008, 06:23 PM
Jimmy you beauty it works like a dream.

Thanks for the full explanation too. Getting a solution is one thing but having it explained if far more valuable in my opinion as I learn from it.

Thanks again.

Pete.

JimmyTheHand
07-02-2008, 02:35 AM
Welcome :)

Jimmy

(PS: How did we got 3000+ views, I wonder... it's definitely strange.)