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

**Bump**

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:

='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.

Private Sub cmdViewCurrent_Click()
    '//  Find cell on sheet H1 that contains a specific date
    Dim cn As String, cn2 As String, ws As Worksheet
        cn = InputBox("Enter the Date You Are Looking For As Month Name, Day, Year")
retry:
        If cn = "" Then Exit Sub
            cn = Trim(LCase(cn))
        Dim cn3 As String
        cn3 = cn
            For Each ws In ActiveWorkbook.Sheets
                cn2 = Trim(LCase(Sheets("Daily Appointment Calendar").Range("H1").Value))
                cn2 = Replace(cn2, Chr(160), "")
        If cn2 = cn Then         'HELP, Working with Active Cell
                Worksheets("Daily Appointment Calendar").Activate    'must do this first
                Worksheets("Daily Appointment Calendar").Range("H1").Activate
        Exit Sub
     End If
  'Next ws
                cn = InputBox(cn & " -- Not Found" & Chr(10) & _
                    "Enter a Different date, or Click [Cancel]")
    GoTo retry
  Next ws
End Sub

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

"bump"

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim NoteBox As String
        If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("R8:R58")) Is Nothing Then
            NoteBox = InputBox("Input Notes For Customer:", "Notes")
        If NoteBox = "" Then 'If the note box is empty or user clicks cancel, do nothing
            ActiveCell.Value = ActiveCell.Value
        Else  'append data to what is already in the cell after a CR and a time date stamp.
            ActiveCell = ActiveCell.Value & vbCrLf & Now() & ":" & vbCrLf & NoteBox


        End If
    End If
End Sub

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

Searching the site, I found this link to an alternative to the calendar control for Mac's. http://www.vbaexpress.com/forum/show...highlight=date

Is there an alternative out there for windows PC's? I'm in need of a date picker of some sort for Excel 2016 that will work in a userform.

Ant suggestions?

Re: make new text in a textbox start on a new line

"bump"

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"

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"]

Cell Contents

Other Sheet Cell Results

INV-2502-2016

2502

INV-2503-2016

2503

INV-2504-2016

2504

INV-2505-2016

2505

INV-2506-2016

2506

[/TABLE]

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

Just tried your testbook1b and I'm not able to run it, gives me an error of "Out Of Memory"

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?