Posts by Excel Noob

    I've been searching for the last hour or so and I can't seem to find something that fits the bill.


    I'm not even sure if it's possible but if it is I know I will find the answer here.


    I have a cell that contains various data but in the cell there will be a string of characters that will begin with either ON or TN followed by numbers.


    What I would like to do is have the sheet look at that cell and if there's a TN12345 (or any combination of numbers) or an ON123456 (same idea as the TN) I would like the sheet to pull just the ON or TN number out and paste it in a specific cell. The ON would have it's own cell to be placed in as would the TN.


    Here's an example. C5 contains various data, a mix of text and numbers but it will have either an ON or a TN or both. I would like the sheet to automatically pull the TN number out and place it in A5 and the ON in B5. I would like this process to repeat all the way through the sheet to the max amount of rows since I have no way of knowing how many rows I'll need.


    Can this be done with a simple IF formula or does it have to be done in VBA or is it not possible at all.


    Thanks

    Re: Show Message If Completion Date Not Met


    Cringe2,


    Thanks for the kind words. It was alot of work but well worth it


    Reafidy,


    Thanks for all of yuor help. I kinda thought that the problem was with what I was doing. I have to figure out when you place it in the worksheet or the workbook or in a module.


    It works great. The only thing I might change is to get it so it doesn't give you the message box when there are no rows that need to be entered. I'll play with it and see if I can get it.


    Thanks again for all of your help

    Re: Msgbox When Completion Date Is Not Met



    Cringe2,


    Yes it's amazing how much has changed Needless to say my car doesn't park itself


    Here's a link to some pictures of it.


    http://s180.photobucket.com/al…view&current=21970dbc.pbw


    Reafidy,


    I'm not sure if the exisitng code that I have might be messing with yours or if I'm just putting it together wrong.


    I've attached my sheet.


    You'll notice in the first row (5) that I use for a date in C there is a date prior to today. This is when I want the MsgBox to pop up because the expected date came and went without a Actual completion date being entered in D5.


    Yes I do want multiple MsgBox's to come up for every Sched Date thats past so I can see which jobs were expected to close but for whatever reason still remain open.


    This way I can track a job so it doesn't get forgotten about.


    Listing the rows that need attention would be great. If everytime the message box pops up it shows the row it's referring to that would be ideal. I have another code in a separate workbook that has something similar so I could work it out from that but first I need the basic function to work.

    Re: Msgbox When Completion Date Is Not Met


    Quote from ByTheCringe2

    What's the car, is it a T-bird?


    It's a 1969 Pontiac GTO[hr]*[/hr] Auto Merged Post;[dl]*[/dl]


    Please forgive me but I know very little about VBA


    When you say the code will exit every time does that mean that once it finds a problem it won't restart until the problem is fixed?
    I don't see it saying before close or open or save so I'm unsure how it would start again.


    I also don't see where it references todays date meaning the sheet every time it opens should check the estimated completion date (Column C ) against the current date when it's opened. If the current date is past the estimated date and there's no Actual Completion ddate entered in Column D it should bring up the MsgBox.


    It may be there but I just don't see it due to my lack of knowledge.


    I did try your code but I couldn't get it to do anything but I wouldn't be surprised if i didn't do something right there either.


    Thanks for the effort so far it really is appreciated

    I have a spreadsheet where I have input all of my technicians service calls and installations.


    In Column C Row 5 and up to the max I assume because I'm not sure how many rows I would use in a year, I have an estimated completion date.


    In Column D and the same Rows as above I will input the Actual Completion date.


    What I want is if the current date is past the estimated completion date (Column C) and there is no date entered in the Actual Completion date (Column D) I would like a MsgBox to pop up saying "Please enter a new completion date on row _"


    "_" would be which ever row the estimated completion date has expired without an actual completion date being entered.


    Thanks in advance

    How can I make this code repeat itself on different lines?



    The code works perfectly for that one line but I need it to repeat independantly in the same columns but for rows 11 up to and including 23.


    If I need to make the message generic like " Please Enter Order Number" then that's fine.


    Thanks in advance

    I'm hoping somebody can help me here.


    I've been searching but I can't find an answer that fits.


    Here's the closest I've come


    http://www.ozgrid.com/forum/showthread.php?t=20589


    What I need is if A10 has any data in it then I want a box to pop up that instructs the user to input data into B10 they click OK and then enter the data


    Once the user inputs data into B10 then the error should be cleared. If they don't enter data then the error should keep coming back.


    This should happen on close.

    Re: Assign Function To Msg Box Buttons


    Forgive me if this is a stupid question but I am a Noob: D
    Is this how I would place it in the Macro?


    Re: Assign Function To Msg Box Buttons


    OK


    I figured out why the Open Macro wasn't working the Named Cells were deleted somewhere along the line so that problem is solved.


    The problem happens when I save the workbook under a different name. If I do not change the name the second sheet comes up fine but when I go to close Sheet2 the Msg Box pops up again and asks me if I want to open sheet 2. I would like the Msg Box to only work off of the first sheet.


    So in a nutshell here's what I need to do. I need to be able to save the workbook under a different name (The main part of the name would be the same but there would be a date string at the end. eg. VS Count 013107 the following week it would be VS Count 020707. I would like to be able to get the Msg Box to only pop up when closing the first sheet "VS Count" and only when there is a value entered into the "Used" group. Sorry I didn't notice the Names weere gone on the attachement I posted above. Don't worry about that part.(Updating on Open)

    Re: Assign Function To Msg Box Buttons


    Snow,


    That code worked perfectly....Once :( I'm not sure what's going on I went and re-did it a couple of times and did get it to work but again only once. The other thing is you have to click on the No button twice for the book to close and the Macro for updating the totals when the book opens now has a problem. I have attached the Workbook. Please look at it and let me know what I did wrong. One thing that might cause an issue is after the new data is entered by the user they will be saving the file as a different name. I'm not sure if that would cause any issues. The reason for this is that it's a inventory sheet so when they fill it out they will be putting that days date on it and saving it as that. This way they do not overwrite the original so they can go back and look at past sheets that they have done. I know that it did not make any difference to the Workbook Open Macro that was already there. Thanks

    Re: Assign Function To Msg Box Buttons


    Here's what I have right now.


    The message box opens and the buttons work but I want to assign the action to open Sheet 2 in the same workbook when Yes is clicked If No then I want the workbook to close. The message box contains 2 buttons a Yes and a No.


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("Used") = Change
    C16 = MsgBox("Do You Want To Open The VS Request Form?", vbYesNo)
    
    
    End Sub


    I have been looking around and I found something similar but it keeps ending up with Run Time Error
    Item with specified name not found


    Here is the code I added

    Code
    ActiveSheet.Shapes("Yes").OnAction = ("'Sheet2'")

    How do I assign a particular function to buttons contained in a Msg Box?


    I have set up a Msg Box on a sheet so when you go to close the sheet the box opens and asks you if you want to open another sheet contained in the same workbook. The buttons are Yes and No. If the User clicks on the Yes button then Sheet 2 will (If I can figure out how)open from the same workbook. If No is chosen then the workbook will close. I have the Msg Box set up and working but I need to find out how to assign the functions to the Yes & No buttons. Thanks

    Re: Open Sheet 2 When Closing Sheet 1



    Mrk,


    I don't expect anybody to do all of the homework for me. If I get somebody else to do it all I learn nothing. All I want is for somebody to steer me in the right direction and I will take it from there. Thanks for your help:)

    Re: Open Sheet 2 When Closing Sheet 1


    Quote from Dave Hawley

    When you say Sheet2 and Sheet1 do you mean sheets in the SAME workbook, or do you mean different Workbooks?


    Dave,


    Yes I do mean in the same workbook. Sorry I should have said that. Thanks

    Hi I am trying to figure out if there is a way to have a message box open asking if I want to open Sheet 2 if there are any values entered into a range of cells on sheet 1. The range of cells are named Used. So if there are any values entered into the Used range then upon closing the workbook I would like a message box to appear asking if the user would like to open Sheet 2. If there is a way to put buttons in that box that says OK and Close or Yes and No that would be good. If the user clicks on OK or Yes then Sheet 2 would open. If they click on Close or no then the workbook would just close. If it would be easier to just to have sheet 2 open upon sheet 1 closing then that would be fine as well. Thanks in advance for your help. If anybody wants to see the workbooK that this pertains to it is posted in the thread titled "Update Cells on Opening" posted by me. Sheet 1 is titled VS Count and Sheet 2 is titled VSRequest.

    Re: Update Cells On Opening


    Quote from Dave Hawley

    It worked fine 'as is' for me. There should be no need to brackets! I suspect you did something else wrong and accidentaly fixed it when adding them.


    The CutCopyMode = False clears the Clipboard and stops the 'marching ants' you get when you copy. However, as you are clearing a range after the Paste Special, it will do the same.


    Please start a New Thread for your new question so the Thread Title matches.


    Thanks Dave I will start a new thread. I copied it directly from what you posted and used Paste in the Macro so I didn't edit anything when I entered it. I only added the brackets because when I was writing it from scratch (following your example) thats how the reference line was showing me to enter it.


    Cheers: D

    Re: Update Cells On Opening


    Quote from Dave Hawley

    Please ensure the correct use of code tags, you forgot the closing tag, that is [/code]


    I assumed the 2 counts were single cells, not a range of cells. Try this code instead.

    Code
    Private Sub Workbook_Open()
        Range("Used,Restocked").ClearContents
        Range("EndingCount").Copy
        Range("StartingCount").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End Sub


    Dave,


    I tried your new code and it still didn't work. I played with it and was able to get it to work. I needed to add Brackets to the xlPasteValue command. I also deleted the CutCopyMode and it didn't seem to make a difference. Is there a reason for that string to be in there? I have pasted the code I put in below and it seems to do everything I want it to do.
    The only other thing I want the sheet to do is to open a message box asking if the user wants to open Sheet 2 Tilted VSRequest if they have entered a value in the Used column. If they click OK or Yes or No then the sheet would open or not depending on what they click on. Is there a way to do that and if so can you show me how? If it's easier just to make the sheet open then so be it the user can just close it if they don't want to fill it out.


    Thanks for your help so far. I have learned quite a bit. If there is a purpose to the CutCopy please let me know and I will put it back in.


    Both you and this site ROCK!!!


    Code
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Range("EndingCount").Copy
        Range("StartingCount").PasteSpecial ([xlPasteValues])
        Range("Used", ["Restocked"]).ClearContents
    End Sub