.show userform from selection in a data validation list

Ive been doing all i can to google an answer to my title. I am trying to open a userform from a specific selection in a data validation list i made. Is this possible? Ive found many vba scripts that claim they work but every one gives me an ambiguous something or other error...

Ive attached the workbook.

I'm trying to get frmProductionCost to open when i select Production from the data valitation list in the Product column.

PLEASE HELP!!!! /wrists

thanks

mike

Also one other thing Im having trouble with is the date in column A. It is auto put in when I put in a value in column B, Ticket Number. That works great. As you can see, when I put a value in column B I have several other columns that auto fill also. This is because all those values are usually the same. I have put in ways to change them if I ever need to, so im good there. The problem Im having is when I remove a ticket number all the cells that auto fill go back to blank, which is what I want, but column A, Date, remains there. Can you look at the vb script that Im using to auto fill the date and show me how to make it go blank also.

Thanks again,


Mike

Hello Mike

Minor addition to your Worksheet_Change event will accomplish what you are asking.

For your info, you have posted this in the Formulas catagory of the forum.
Questions regarding VBA should be posted in the VBA Programming catagory to be better directed at those who would be most likely to help.

Good Luck with the project
NoS

NoS, once again you have my back. Both of those worked perfectly. I have however abandoned putting the info in through the worksheet. I have incorporated all my input through 1 userform. It working great but I have stumbled on another problem i just cant figure out. It seems just to be a placement issue because it executes perfectly the first time but it seems to be going through a second time and thats were the snag is. Its in an if/then statement.

this is whats going on...i have a worksheet change macro that is looking for the last cell in the worksheet to be filled. when that happens it renames the worksheet, opens a template file, copies the blank sheet and adds it after the one that just got filled up.

there are 2 issues with it. The first is that after it puts the new sheet in it runs back through the worksheet change macro and tries to rename the new one what the old one just got named. I dont understand, even if it is going through the macro again, i have it set so it is checking the activeworksheet and the new one should be triggering the exit sub because there is no data in any cell. So i get a name already used error and have to stop and end the debugger and what not.

the second thing is with the way i have it putting the new sheet in the xlsm file i open to get it stays open. Ive tried several methods i googled to close that file but i cant get any of them to work.

If you try it out the pop up warning window was just me trying something new to solve the problems. I dont care if whoever helps me leaves that in or not. I also didnt try having a blank sheet on the workbook to copy from for the new sheet either. so if thats an easier way than how i have tried so be it, show me the way. Ive just been on this prob for the last few hrs on top the all the hrs that got me to this point. lol. im beat.

So to get around in my mess...in the column headers i put the "add ticket" button that will open the userform to input the data. It will populate that last empty row and trigger where the mess is if you want to try it out first. I guess to reset it you can close without saving and reopen.

I do have a few other errors that pop up here and there but they are not as drastic as this problem and i haven't even looked at them yet to know if i can fix them myself or not.

Oh i do have one more thing that shouldnt be too taxing, if no one minds. I need an easy hopefully one line script in the macro somewhere that will save the just completed sheet as a pdf file by the sheet name. I was thinking of having that done at the same time the new sheet is being put in.

anyway..../yawwwwwwwwwwwwwwwnnnnnnnnnnnnnnnnnnn. THANK YOU ALL WHO WILL HELP ME. THANK YOU ALL THAT HAVE HELPED ME. THANK YOU VERY VERY VERY MUCH NOS, YOU HAVE HELPED ME THE MOST.

good day to everyone

mike


EDIT....TST.xlsm is the template file i use to add a new sheet.

Looking at the workbook of your original post and wondered why not use the userform for the whole thing.

I can't run your latest version because I don't have, and won't be adding, that calendar/date picker to my Excel.

Filling in everything from the userform you shouldn't need the worksheet_change event to do anything.
Should have a procedure of the userform doing it. That way you control the what and when of things.

That said you can temporarily turn off the worksheet_change event using EnableEvents=False, but make sure you turn it back on with EnableEvents=True or you'll go insane trying to figure out why Excel no longer does what you expect.
Should your testing macros stall out after executing the false and before executing the true......... go to the VBA immediate window and run EnableEvents=True to turn them back on.

This evening I'll have a look at removing/disabling the calendar/date picker from the copy of your workbook on my computer so I can have a look at the other issues.

Thanks for looking at it and replying. I actually got on here now because Ive made significant progress on that problem. What was going on is I wasnt updating the script in the template file TST.xlsm so it was doing a second worksheet_change on the first sheet. I have also moved some of the script around and have it working almost perfect. It is doing everything right, renaming the completed sheet, adding a new sheet named sheet1 and saving the previous sheet as .pdf.

What it is doing wrong now is...saving the file as pdf.pdf and not the sheet name. That should be an easy me learning the proper syntax to capture the sheet name. The only other thing is again with the worksheet_change and placement of the userform newTS.show. It is coming up 2 times after everything is done. Its a little annoying now but my workbook is essentially usable. I'm sure there is another better way than worksheet_change but I really dont know what. The issue is the sheet knowing when row 30 gets filled and more specific K30. That filling is the trigger that its time for a new sheet.

If you have a better way for me to trigger a new sheet when row 30; K30, is full i would love to see it.

As for not starting out with a userform for the whole thing, it was just a lack of knowledge about userform. Having never really used them I didn't know how much i could do with one and how much they are just like everything else in programming in excel.

Thanks again

I'd eliminate Worksheet_Change completely. Your lRow variable knows if it's time for a new sheet.

The biggest thing with userforms is not getting them to do what you want them to do, it's preventing them from doing what you don't want them to do.

Please explain how lRow knows. And how would i make it trigger the newTS userform so it copies the template and saves the finished sheet as .pdf?

/xfingers, the userforms i have made so far dont seem to be doing anything they arent supposed to. lucky me?

Your on change event is looking for something being in K30, which doesn't exist until your userform puts data into row 30.
You use lRow to determine the row you write to therefore.....
if lRow = 30 then it's time to name this sheet and ask about a new sheet, just like the on change would do.

I've never done the excel to pdf thing so can't comment on that.

Lucky? try entering a letter or a question mark instead of a number for the number of bbls and see if things make sense or how it effect future calculations.

ok so it would be something like

if lRow = 30 then
newTS.show
end if 'or elseif or else or whatever needs to be there...

and that would go somewhere in the script that lRow is looking for the next row and putting stuff in?

gotcha on the invalid characters where it should be numbers.

UPDATE...

NoS that worked flawlessly. I put it in were the lRow script is. I put in at the end because i new it had to fill row 30 before i made it newts.show and put a new sheet or row 30 probably would have been blank. I also commented out the worksheet_change script. Now when i get a new sheet it doesnt pop up newTS userframe 2 times for not reason at all. I also put an exit sup in the if then to bypass the call for the reset_click which resets the input userform and shows it again. Im ok with having to click the add ticket button on the new sheet.

All i have now is a few cosmetic changes/fixes/tryouts and implementing new things when they pop in my head. This is now totally useable.

Thanks for your help.

Mike

and another update...

Well i finally got it to save with the right name. Ive also been doing some cosmetic work. I have about 3 different jobs i do. When I do any one of them it is usually for a fairly long period of time, say 1-3 days, once in awhile longer. When I do each of these jobs the information is about 99.9% of the time exactly the same. I also have to write a ticket for each time I do said job in the day. My tickets come in books with about 25 numerically ordered. The books are all mixed up so after 25 numerical tickets I usually dont have the next book following the same numbers.

When I first started the info userform I made it generic for the most part. I had to put the numbers in every time...the exact same numbers...So i changed that up a bit. First off, when i add ticket it comes up displaying the next ticket number based off the last. It also grabs all the info in the last row and fills in the userform textboxes. That way because of the long stretches of same ol same ol i can just click add ticket then click calculate because its most likely to be the same. But when I am done with a run of one job i can change the info to the job im on then click calculate. It works the same with the ticket number. Always leaves the edit option open.

Next im gonna work on security. Not for people finding it, hell it would be totally confusing and useless for anyone but me and my boss. This security will be back up so in case something happens it will still be there. Im thinking a save for every ticket entry or maybe every 5. There are only 26 rows to fill but it takes 3+ days most of the time.

mike