Posts by mycomputerguy-w

    Re: Search through all sheets in a workbook looking for a date input into an input bo


    OK.... I can do that but because a sample workbook would look like mine as it is now, its in multiple pieces and not fully functional. I could post screen shots with arrows and pointers that may do a better of explaining it. Its a very simple thing but hard to explain. I hope the attached screen shot with explanation will help.

    Re: Search through all sheets in a workbook looking for a date input into an input bo


    Thank you for the code Max, Just by looking at it, I believe it will do what I need it to do. As far as the list box, I SORT OF got that working. This listbox shows as follows: column 1 is a time value in 30 min slots, column 2 shows the customer name IF that customer has an appointment booked. If not, it, and columns 3 and 4 are empty meaning those are open slots. Basically what I was wanting to do, which I accomplished, is to fill the list box with info from the active sheet, the first column has a time value. I have it so when a user clicks on the open appointment line, it populates a textbox with the time value from column one of that specific line the user clicked. This is where the SORT OF comes in. The one thing im having issues with is the find method for either the column 1 listbox time value or the textbox that was populated with the time value. What id like it to do is after the user clicks the listbox line and gets the time populated in the textbox and the rest of the information in the userform filled out, they click an "OK" button at which time I would like the userform to close, got that no problem, and at the time of closing the userform, find the time value of the textbox that was populated or the 1st column value of the line that was clicked in the listbox and activates that cell on the worksheet so the user can verify the appointment time was populated and is right. For example. The user sees in the listbox that a 9:00am appointment is open, they click that line in the list box, it populates the textbox giving them that time slot, all other info is filled out, they click OK and are taken directly to 9:00am on the worksheet and verifies that the slot was taken and the customer info populated. (maybe some sort of conditional formatting highlights the line until clicked away?) I've got everything else working except this part. this is not crucial to the operation but more of a checks and balances for the user to be sure the appointment is right. I hope this makes a little more sense for you now. Thank you for helping me out with this. Im still learning here and have learned A LOT for all of you who have helped. someday I hope to be the one able to offer the help....... Thanks again!

    Re: Search through all sheets in a workbook looking for a date input into an input bo


    Thank you Max, Very much......I didn't expect someone to make the code from scratch but thank you........ and Yes, a macro to name the sheets by each date for the month is what I'm looking to do. Just so we are on the same page, I'm building a userform that I'm calling Administration. This is where I will run all this from. I already have commands for printing appointments and filling an appointment template, and an invoice at the same time and a few other commands. One other thing that I may end up having trouble with that you may be able to offer a bit of guidance on. I have a userform that is used to schedule/fill the appointments on the actual sheet and on that userform I've got a list box that I haven't tackled yet. Ultimately, What I want it to do is, fill with what will be all the available appointment times in a given day. like I said, I haven't started on the development of this yet mainly because I want to research to see if what I want is possible which I THINK it is. IE: ask the customer which day they are looking to schedule the appointment on, input that given day on the form, it goes out to search the appointments and finds the ones that have not been filled and shows them to me them in the list box.
    Sorry to be rambling here...... that was actually going to be my next project on this and I thought id ask later if I needed help. so to be clear, I'm just looking for a little guidance for right now. Again...... Thank you for the code. I'm going to put it in place here now and see if it works like I expect it to. have a great night!!


    EDIT: WOW...... it works perfect Max, Thank you again and I'd also like to say that this code is MUCH more efficient then what I had. Thanks again!


    2nd Edit: I did find a problem. The current sheet I have is setup with formatting, formulas, and times, headers and banding already in them. Is it possible to make a COPY of the current sheet, name it with the date, and put it at the end?

    Hello Guys, im currently building a scheduling system. In a nutshell, what im trying to do, at this point, is pop an input box asking for a specific date to search for then if that is found, activate the sheet that the date was found on. The date will always be in H1 of a sheet. I found the following code online and attempted to tweak it to my needs. It pops the inputbox and allows the user to enter the date but never finds it. the format in H1 is "November 10, 2016". It gets that date from a formula:

    Code
    ='Daily Appointment Calendar'!H1+1

    (The sheet names will probably change and be the actual date)


    Some other questions here would be. (1)The way it looks in this code, the input needs to be exact. Is there a way to tell it to just look for the date entered no matter what format it is in?


    also, if that date isn't found, id like to pop another box asking if the user would like to add a sheet with that date as the name.




    Any help / ideas are all greatly appreciated.


    EDIT: is there also a way to create multiple sheets at the same time giving a name to each sheet of the current date for the first sheet then the next day for the next sheet and so on fo the whole month? The workbook will then be saved as the month name but I haven't got that far yet! Thanks again!

    Re: Store persistent information within a userform


    Norie, I didn't see your post right away but wanted to reply to it. The data im trying to work with is one cells worth of data. I have multiple cells that contain data. This just happens to be a notes field as stated below, Im just having trouble with that particular cells data. But thanks for the reply!

    Re: Store persistent information within a userform


    ok...... maybe I didn't actually say the words "THANK YOU" but all help is ALWAYS appreciated! As far as the comment box being tacky, I didn't mean it sound like I didn't appreciate the suggestion. I just thought there'd be a better way. If not, I'm ok with that. I am always ok with an answer of, "That's Not Possible" that's how I learn. so to everyone that has made a suggestion or given any advise, THANK YOU VERY MUCH and please always know that I do appreciate any and all help.


    Now that we have that out of the way, do you think I'd be better to try this in access?


    Basically what I'm trying to do is build a simple customer management interface. The main reason I wanted to use excel is so I could have a complete interface that would hold all the customer info, any notes generated, a daily calendar for scheduling appointments/keeping track of time spent, and an invoicing system. I know how to set up excel to work like a database and have most of the rest of the system setup and working but just needed a way to collect the notes in a fashion that will allow me to "Append" new notes into the existing notes and keep them as a running diary so to speak.... the problem I'm having is I have freeze panes on the top so if I turn on word wrap, the cells height grows and eventually all other rows below will disappear below the freeze panes. If I could have VBA make the row height stay constant even with word wrap on, the data in the cell could remain in the cell. But then what id like to see is if the cell is activated, have the existing data populate a textbox on a userform and have the ability to append that data with a new note, save it and make it the new data in the cell. The new note would start on the next line and have a date and time stamp on it. If this is something that is possible, that's great but any and all suggestions are welcome.

    Re: Store persistent information within a userform


    ok....... yes the data is being entered into the cell. and I knew it couldn't be saved in a variable forever.....and as far as using a cell comment...... that's tacky...... usable.... but tacky. The main reason I don't want all the text in the cell. with word wrap the cell gets huge and pushes everything down..... its just not appealing to the eye. That's why I wanted a pop up window type thing to be able to read the notes part but not have all the clutter in the cell...... so I guess ill have to figure out a different way to do this.


    I'm thinking now that maybe access would work better for me on this type of project.

    Re: Store persistent information within a userform


    if this helps someone to get me over the hump..... I did manage to figure it out this far:



    I managed to get it to where it will do everything except store the active cell contents as a variable or something similar. then I need the cell to be populated with "MORE DETAIL" as, im thinking, a hyperlink to maybe just a pop up a window of some sort and populate it with the contents of the variable???


    Any better suggestions??


    EDIT: well... that doesn't work either for some reason.... if the cell is empty then it works great.... if the cell already has data in it, it does nothing..... doesn't append or even clear the cell. (not that I want it to clear it but simply does nothing if the cell contains data of any sort)

    Re: use a pop up userform with a textbox as a "More Details" area


    The way its set up now it IS the cell contents but that's what id like to change.... what I would like it to be is just a trigger to open this text box that will hold all the notes..... so you click the cell, a window opens that has a text box with all the notes in it...... each entry is on a different line and starts with a time / date stamp. The time date stamp on a new line would have to be triggered when the userform opens. Does that make sense??


    EDIT: now I understand what you are asking...... yes, the contents of the userform textbox that is triggered from that particular cell would need to be held as a variable SPECIFIC to that cell. That way, im thinking, I can set it up with an IF statement to populate the CELL with a hyperlink or something similar that will say "MORE DETAIL" if there is anything in that variable. Then when the user clicks the "MORE DETAIL" a window will open and show the contents of the variable?.........basically its going to be an ongoing notes field.


    As I type this in and think about it, I may need to do this a different way. Suggestions at this point would be great. maybe make it a listbox? ......

    Im not sure how I should word the title but here is what im trying to do:


    I have a userform that is attached to a range of cells. When a cell in that range is selected, a userform pops up and has just a text box and an OK button on it. I have this part working fine. What I would like to happen is this:
    When the userform pops up, id like to have the text THAT IS ALREADY IN THAT BOX REMAIN, have the cursor start a new line with a date and time stamp and a ":" then allow the user to add more text to the text box and save everything wand close when the OK button is clicked.


    I hope this makes sense as to how id like it to work. ive looked around through many user forums but have not been able to find a way to do this so im hoping this is possible. the text DOES NOT need to be entered into any cells anywhere its basically just a memo field for more details.....



    Thanks in advance for any help or suggestions....

    im guessing this is an easy fix but as usual, im having trouble finding the answer.


    What im wanting to do is as follows: The following is the code I have now. This code takes whatever is typed into an unbound textbox, sends it to a secondary textbox with a time and date stamp on it. If I exit the program and come back into it to add a second line, it will go on a second line. if I DONT exit the program, the next input will be on the same line
    so the out put I get if I don't exit is:


    "10/19/2016 9:53:20 PM : this is a test line 10/19/2016 9:53:20 PM : this is a 2nd test line 10/19/2016 9:53:20 PM : this is a 3rd test line"


    Code
    Private Sub Text229_AfterUpdate()
    Me.Notes = Me.Notes & vbCrLf & Now() & vbCrLf & ": " & Me.Text229 & Chr(13)
    Me.Text229 = ""
    Me.Text229.SetFocus
    End Sub


    What I want to happen is no matter if I exit the program or not. any entry after I hit enter, needs to be on the next line.
    so the output I want should look like (minus the quotes of course):


    "10/19/2016 9:53:20 PM : this is a test line"
    "10/19/2016 9:53:20 PM : this is a 2nd test line"
    "10/19/2016 9:53:20 PM : this is a 3rd test line"


    Any Suggestions??

    Re: Trim cell contents from both left and right


    Quote from jolivanes;770820

    mycomputerguy-w
    Just wondering. Did any of the suggestions help you?


    Sorry, I forgot to get back on here and leave a comment, yes the suggestions did help. I tweaked the code to suit my needs and got the job done.


    thank you again for all the help. You can close this thread if you want.

    I'm thinking this is a very simple formula (or VBA) that will trim this down to what I need.


    The entry will ALWAYS have "INV-XXXX-2016" as the cell contents, The part I need is the "XXXX" to be trimmed out then it automatically gets entered into another cell on a different sheet.
    if this is possible with VBA, id be open to that also.


    Any variables will be the "-2016" will change with the year and the "XXXX" will eventually become "XXXXX" or even "XXXXXX". (5 to 6 digits) But the last 5 characters, "-2016" will always be 5 and the first 4 characters, "INV-" will never change.



    Here is a primitive example:
    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Cell Contents

    [/td]


    [td]

    Other Sheet Cell Results

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2502-2016

    [/td]


    [td]

    2502

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2503-2016

    [/td]


    [td]

    2503

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2504-2016

    [/td]


    [td]

    2504

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2505-2016

    [/td]


    [td]

    2505

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2506-2016

    [/td]


    [td]

    2506

    [/td]


    [/tr]


    [/TABLE]

    Re: Error handling for ListBox1.ColumnCount = UBound(vData, 2)


    First and foremost, I want to say Thank You for all your help and attention. However, I think I need to restate my ultimate goal here that has seemed to have been lost in the type and code. This code is going to be used on a workbook that is EXTREMELY larger than this test book with a lot more sheets and data. I like the way the above code lists the data in the list box and It seems to work perfectly if I'm using it for only look up of any data already existing in the sheet. The only thing that wasn't working is if I tried to enter data that wasn't in the list. It crashed the userform.


    Maybe if I try to explain what it is that I'm building, that may help.


    This is going to be an intake program for my business, I own and operate a computer repair shop. (Ironic huh??) A summary of what I need this program to do is; collect personal data and transfer it to a sheet, (Name, Address, Phone, etc), collect all data about the incoming unit and transfer it to a sheet, (Brand, model, type, serial number, amt of ram, HDD size, OS and so on) create and Keep track of work orders, invoices and estimates. They have to be linked so to speak.... 1 person can have multiple units, 1 Unit can have multiple invoices and workorders but 1 unit can only have one owner so the lookup has to be centered on the customerID (the combobox) the primary key is the CustomerID on everysheet and everything is linked to it. Invoices, work orders, estimates. As of right now, there will be at least 3 userforms that will be used for different entry/lookup. The forms (WO, Invoices, estimates, etc.) will be created, filled from the many sheets of data and printed all from within the workbook. The reason for the list box is to be able to see each different unit and its data IF there are more than one. If the unit has never been in my shop, it needs to be added along with the owners info, the work order for the work, the invoice for work and the parts used, and so on.


    For an unbelievably high cost, I could very easily purchase a program that would do all this and more but I'm trying to not only tailor this to what I need, but I'm also trying to learn at the same time. All that being said, I'm having trouble opening and understanding your code, I THINK, because of the code and procedure differences from the cross platform. However, I'm interested in how you are doing this on a mac because if anyone out there is trying to do something similar on a mac, it would work for them too.

    Re: Find exact match(s) and display select cells of the row in a list box


    First and foremost, I want to say Thank You for all your help and attention. However, I think I need to restate my ultimate goal here that has seemed to have been lost in the type and code. This code is going to be used on a workbook that is EXTREMELY larger than this test book with a lot more sheets and data. This particular bit of code maybe used in a couple different places for data search/lookup and data entry and may be modified to look at data over more than one sheet. If I'm looking at this correctly, this code seems to concentrate on a single sheet at this point and I don't see where I can really modify it to work over more than that 1 sheet. I like the way it lists the data in the list box and It seems to work perfectly if I'm using it for only look up of any data already existing in the sheet. Maybe if I try to explain what it is that I'm building, that may help.


    This is going to be an intake program for my business, I own and operate a computer repair shop. (Ironic huh??) A summary of what I need this program to do is collect personal data and transfer it to a sheet, (Name, Address, Phone, etc), collect all data about the incoming unit and transfer it to a sheet, (Brand, model, type, serial number, amt of ram, HDD size, OS and so on) create and Keep track of work orders, invoices and estimates. They have to be linked so to speak.... 1 person can have multiple units, 1 Unit can have multiple invoices and workorders but 1 unit can only have one owner so the lookup has to be centered on the customerID (the combobox) the primary key is the CustomerID on everysheet and everything is linked to it. Invoices, work orders, estimates. As of right now, there will be at least 3 userforms that will be used for different entry/lookup. The forms (WO, Invoices, estimates, etc.) will be created, filled from the many sheets of data and printed all from within the workbook.


    For an unbelievably high cost, I could very easily purchase a program that would do all this and more but I'm trying to not only tailor this to what I need, but I'm also trying to learn at the same time. All that being said, I'm interested in your suggestion above, Can you elaborate a little bit and also give me a little bit of direction as to what I'm suppose to do with the above code. Is this 2 separate procedures and I use one or the other or do they work together?