Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: .show userform from selection in a data validation list

  1. #1

    .show userform from selection in a data validation list



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hello Mike

    Minor addition to your Worksheet_Change event will accomplish what you are asking.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'dealing with date column
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Target.Value = "" Then
           Range("A" & Target.Row).Value = ""
        Else
           Range("A" & Target.Row).Value = Now
        End If
    End If
    
    'show userform
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
        If Target.Value = "Production" Then frmProductionCost.Show
    End If
    
    End Sub
    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

  4. #4
    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.
    Attached Files Attached Files
    Last edited by testerxxx; 2014-12-15 at 10:36 AM.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  6. #6
    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

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  8. #8
    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?

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  10. #10
    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.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •