Re: sum with selectin change event
Yes, he solved his problem on MrExcel and Chandoo... in other words, people on 3 forums running around just for him. That's how to do it.
Re: sum with selectin change event
Yes, he solved his problem on MrExcel and Chandoo... in other words, people on 3 forums running around just for him. That's how to do it.
Re: Get the Meaning of Unfamiliar Symbols in Code
I'd also get rid of that 'consultant' - tell him to save his money and stay in India. The code is, to put it politely, rubbish;.especially if multiple files are opened. While it will probably work as expected, it has a wonderful logic bomb that is going to cause big tears sometime.
Re: Get the Meaning of Unfamiliar Symbols in Code
Yes, you are right - both were compulsory in earlier versions of Basic and Visual Basic, but always optional in VBA. They remain to preserve backwards compatibility.
However the pound is useful to indicate visually that this refers to a file handle so helps comprehension when reading code; and why add in another conversion (to a real string) when vba can do it natively, and faster..?
Re: Get the Meaning of Unfamiliar Symbols in Code
The pound (#) sign usually means 'number' and here indicates that input is to come from the File Handle associated with the variable nSourceFile. You can have multiple files open so that identifies which file.
Not sure about the latest versions, but all functions in VBA used to have 2 variants, your example of InputBox could be written as InputBox$ or just InputBox. The $ version returns a string, the 'plain' version returns a Variant.
More things for you to read about.
Re: Change data source for all pivot tables with slicers simultaneously
Also posted on MrExcel.
Re: Help altering function that checks timestamp of file.
According to the people who wrote it, it returns the date/time the file was created or last modified. Whichever is lthe latest.
Re: Help altering function that checks timestamp of file.
Use the old DIR & FileDatetime functions - less hassle than the FileSystemObject
This expects a fully qualified file name, or mask. "c:\temp\filename.csv" or "c:\temp\book*.csv" and returns the date and time of the newest matching file.
Re: Set cell value as the VBA variable
I understand what you are doing - but posting a line of code containing variables is hard to intrepret with knowing what those variable refer to.
My answer was just to show that, if you replaced your variables with actual values, the concept can work - and that really is all I can say about it based on what you have posted and described.
Re: Set cell value as the VBA variable
It's possible - but, on its own, hard to determine if what you posted is correct. For example you have a sheet name 'ClientWorkbench' and a variable named 'ClientWorkbench' (...Range(ClientWorkbench...)
However, if you substitute real values, and put a 1 in cell A1 of the worksheet
If will coimpile and run, and displays the message box.
If you are having problems you need to test each portion. For example, add a Break to the line and run the code. When it stops copy this part to the Immediate window
Add a ? to the start, move to the end of the line and press enter. It should print the value of that cell, but I have a feeling it will error.
Re: generate combobox
You can add as many comboboxes as you want, or system resources will allow - what you haven't said it what the combos will contain, where that source data comes from or even the logic behind this.
Re: User Form adding, searching, editing and deleting data in a different book
Attached seems to work (But I don't know the app enough to check fully) - Can search, view, add, edit with the 2nd address.
This only is for the Customers screen - there is just too much other stuff where other sheets are updated (send information to invoice/quote/receipt...)
The appearance has changed slightly, I was just playing a little while picking up on other issues. For example, the Phone & Cell fields are numeric. if any non-numberic text is added to those text boxes (606-5655, for example) the field will simply not update - it needs a little more work.
Re: User Form adding, searching, editing and deleting data in a different book
Maybe i didn't include all of the changed in that last, but I can add, exit and view the 2nd address line.
Nearly midnight though, so I'll check in the morning and upload a copy of the workbook
Re: User Form adding, searching, editing and deleting data in a different book
The changes described below for you to have a go... try it on a copy of the 2 files.:
All other location references (2, 3, 4, 5...) below inc by 1
It might also be an idea to take the chance to update the Tab order so the users can use the Tab keys to move from control to control in order. This is easy to do, you just hold down the Ctrl key and then click on each control in the order you want to access them. Include Labels in that as well. For example, You click the 'Invoice/Quote type' label followed by the Dropdown below it; then the 'Sales Staff' label followed by the drop down below that and so on. I'd skip the 'Close' button and leave that until last. You won't be able to Ctrl-Click on controls in a frame (so include the holding frame in the selection to start with). Once you got them all, go to the TabIndex Property in the Properties Box and type a 0 - The TabIndex for all the controls you've selected will be set sequentially from the number enterd in the order you selected them. Go back and do the same for the controls in any Frame you couldn't select earlier and finally set the TabIndex of the close button to 999 so it will be acccessed last.
Re: User Form adding, searching, editing and deleting data in a different book
Do you mean you want to store a 2nd address line or just 'wrap' the existing address when printing?
You need to explain a little clearer, there seems to be 55 dozen print routines
Re: Taken taken minus breaks over midnight
Sorry - I just concentrated on the one cell - if I get time I'll have a look later.
Re: Taken taken minus breaks over midnight
I think your formula in I7 is wrong - You're checking the Start time in B7 <= what looks like the end time in I3? Is the same for C7 & I2
Changing that around and another little additon to account for the midnight changeover gets
=IF(AND($B7<=I$2,$C7>=I$3),I3+(I2>I3)-I2,0)
Which seems to give your 1:30 result with 08:00 in I7
Re: Add last edit date to each Worksheet
The Change made by code is causing a Change event to fire which updates the cell which causes the Change event to fire which updates the cell which causes the Change event to fire and so on until Excel gets dizzy and falls down...
I'm surprised Excel does not ABEnd.
Although if you can live with the time in a fixed cell on all sheets, then this in the ThisWorkbook code module will save you having to add code to every worksheet and will automatically include any future new sheets.
Re: Insert new rows with formatting.
Also posted Excelforum
Re: Web query Looping from table selection
Quote from Lehoinabar;770387I just read it
So, as discussed on that page, I suppose you will be adding the link to your thread on StackOverflow?