Posts by Grimes0332

  • Get the Meaning of Unfamiliar Symbols in Code

    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.

  • Get the Meaning of Unfamiliar Symbols in Code

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

  • Get the Meaning of Unfamiliar Symbols in Code

    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.

  • Help altering function that checks timestamp of file.

    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.

  • Set cell value as the VBA variable

    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.

  • Set cell value as the VBA variable

    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

    Code
    1. If ThisWorkbook.Sheets(1).Cells(1, Range("A" & ThisWorkbook.Sheets(1).Cells(1, 1).Value)).Value = 1 Then
    2. MsgBox "1"
    3. End If


    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

    Code
    1. ThisWorkbook.Sheets(ClientMatrixSht).Cells(MetricRw, 1).Value


    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.

  • generate combobox

    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.

  • User Form adding, searching, editing and deleting data in a different book

    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.

  • User Form adding, searching, editing and deleting data in a different book

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




    • Inserted Column headed 'Address 2', Col D on Customers sheet in SourceData.xlsx
    • Inserted Column headed 'Address 2', Col Q on Customers sheet in SuperiorForms
    • Named Range 'outdata' extended by 1 column (To W, I think, but 1 column nonetheless)
    • New Textbox named Emp3A on customer userform, Edit Customer panel. Placed directly under 'Emp3' Other controls moved slightly to place it.
    • New Textbox named Address2 on customer userform, New Customer panel. Placed directly under 'Address'


      • The placement of those controls is entirely down to you.




    • lstCustomers_Click event modified to load the new field. Added the emp3a line




    Code
    1. ' Previous as was
    2. Me.Emp3.Value = Me.lstCustomers.Column(2, i)
    3. Me.emp3a.Value = Me.lstCustomers.Column(3, i)
    4. ' Remainder as was



    All other location references (2, 3, 4, 5...) below inc by 1




    • SQL string in cmdEdit_Click (customers form) edited:




    Code
    1. ' Previous as was.
    2. "[Address]=" & Chr(34) & Emp3.Text & Chr(34) & ", " & _
    3. "[Address2]=" & Chr(34) & Emp3a.Text & Chr(34) & ", " & _
    4. "[Phone Number]=" & Chr(34) & Emp4.Text & Chr(34) & ", " & _
    5. ' Remainder as was




    • SQL in cmdAdd_Click procedure changed to save data from the new textbox.




    Code
    1. sSQL = "Insert INTO [Customers$] ([ID],[Name],[Address],[Address2],[Phone Number],[Cell Number],[Email Address],[PST],[PST Number],[Copies]) Values (" & _
    2. lngNextID & ",'" & _
    3. Me.txtName.Text & "','" & _
    4. Me.txtAddress.Text & "','" & _
    5. Me.txtAddress2.Text & "'.'" & _
    6. (remainder as was)





    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.

  • Taken taken minus breaks over midnight

    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

  • Add last edit date to each Worksheet

    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.