View Full Version : Challenging Macro

05-13-2008, 10:52 AM

i not got a clue how to do this,,,totally lost

Need a macro that will tell the user if they try and enter the same demand customer name that already exits in any worksheet that is visible in the workbook.......

i.e. a message box. "Demand Customer is already included in the selected worksheet cannot add again" - warning message box.......

if it is a new demand customer then proceed as normal...

please find attached the workbook.....

05-13-2008, 10:54 AM
apologies the macro has to be linked to the Add Supply macro button on the main worksheet....

05-13-2008, 10:55 AM
apologies the macro has to function when the user clicks the Add Supply macro button,,,,,,All other function that the macro button does must remain........

05-13-2008, 12:15 PM
polease ignore pervious workbook,,,,,,,find attached new workbook.....with minor changes

05-13-2008, 12:43 PM
When I click on Add Supply the userform comes up.
When I click on one of the on-the-fly Option Buttons, Excel locks up and I have to force quit. (There is no event code behind those buttons.)
I don't see where the list of existing Demand Customers is stored.
Are "USA", "Canada", etc. demand customers?

05-13-2008, 12:48 PM
apologies try this workbook

05-13-2008, 12:55 PM
What is a Demand Customer and where are they stored?

05-13-2008, 01:02 PM
usa and canada are demand customer......where they are stored i do not know as the workbook was designed by some else.........

05-13-2008, 01:14 PM
looking at the workbook....i believe that the userform read the demand and supply customer from the column the were add in originally A8 fro supply and H8 for demand customers....

05-13-2008, 01:16 PM
It looks like Main Sheet!H:H holds the list of demand customers. Validation would prevent you from adding duplicates to that list.

I suspect that part of the problem I'm having with the on-the-fly controls is that the project you posted is protected.

How is the Add Supply userform supposed to work? What does pressing the OK button do?

05-13-2008, 11:18 PM
Add userform.....you select and supply customer then on the opposite side you select a demand customer and post that demand customer into the supply customer workhsheet > press OK button...

05-14-2008, 12:46 AM
Seeing as the option buttons et al are added at run time, the code cannot know what is there so there is no code to react to anything.

Why don't you start from scratch and say what you would like to do and throw away those forms?