Posts by Ger Plante

  • The Wizard of Oz

    Funny, I was just thinking about Dave today. I can honestly say I wouldnt have been as successful in my own career with the personal help of the Wizard of Oz!

  • Using Microsoft Query to summarize multiple workbooks

    Quote

    I learned that corporate IT department will not allow installation of Power Query add-in for Excel 2013.


    Monumental stupidity right there.


    Chances are you you will need to write some VBA... start with a blank workbook that you will use as the "Summary" workbook... You will have to point to each workbook, open it, read it and bring that data into the summary workbook. Take a read of this useful article to get you started...


    https://msdn.microsoft.com/en-us/lib...or=-2147217396



    But honestly, I would be kicking up a stink with your management team as you have a legitimate business problem and a solution already available... Ask Your corporate IT department will they support your VBA application after you develop it ;)


    Ger

  • Table Creation - £30 gbp

    Re: Table Creation - £30 gbp


    FWIW, I tested on both Office Pro Plus 2013 and MS Office 365 Pro Plus and did not notice any macro issues and was able to add a new SKU just fine, and "overtype" the data on a newly added row. No issues seen.


    That was using the code from "1.2" in post #26 above


    I had to enable Macros at the prompt and also a Enable Content at a second prompt.


    Regards
    Ger

  • User Form to copy from one Worksheet to another

    Re: User Form to copy from one Worksheet to another


    Nothing too obvious wrong with the paste function...


    Code
    Range(TxtDstCls.Value).Select
        Selection.PasteSpecial xlPasteAll


    Again, there isnt a need to "select" the range here... this could be shortened to:

    Code
    Range(TxtDstCls.Value).PasteSpecial xlPasteAll


    That being said, I dont this is the cause for formatting to be lost.


    How about just doing:

    Code
    ActiveSheet.Paste Destination:=Range(TxtDstCls.Value)


    ?


    Ger

  • User Form to copy from one Worksheet to another

    Re: User Form to copy from one Worksheet to another


    Its really odd the Workbook_open even wont trigger (even after unblocking it etc.). It just opens Excel and doesnt even show a worksheet. However, if go through VBA and run the user form it works fine.


    Anyway, did you write this yourself?


    There is nothing blindingly inefficient about the code. If its working fine, I wouldnt necessarily go taking it apart.


    There were one or two instances where you used .activate that I dont think you needed to use it... for example, when you open a workbook, then that becomes the "activewindow".

    Code
    Set src = Workbooks.Open(folder & "\" & CbSrcWrkbk.Value, True, True)
        src.Activate
        Application.WindowState = xlMinimized


    Would it better just as?

    Code
    Set src = Workbooks.Open(folder & "\" & CbSrcWrkbk.Value, True, True)
        Activewindow.WindowState = xlMinimized


    or maybe

    Code
    Set src = Workbooks.Open(folder & "\" & CbSrcWrkbk.Value, True, True)
        Activewindow.visible= false


    Also consider opening the workbook in a hidden state...
    https://stackoverflow.com/ques…-with-vba-without-display


    But honestly, not really worth bothering about it.


    Your function "Used_Range" doesnt actually return a value as part of the function name (which is what functions are for), you simply set (initialise) variables. I guess this is OK, but using it like this is effectively calling it a sub procedure.

    Code
    Function Used_Range()


    Code
    Sub Used_Range()


    Again, no real impact


    And staying with "Used_Range", since you are switching workbooks and worksheets, it would be "good practice" to tell the code what worksheet you are referring to when mentioned a range reference.

    Code
    If rng3 Is Nothing Then
        Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
        Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)



    which can be delivered as a parameter to the subroutine...


    Otherwise, as far as I know, "Cells.find" will just look at the active sheet... which can not always be 100% guaranteed


    Honestly though, code seems basically fine IMHO


    Ger

  • Find the 10 latest files in a folder then copy to new folder then rename

    Re: Find the 10 latest files in a folder then copy to new folder then rename


    You will need put it into a loop of some description to search for all files in a folder and use cells in a workbook to store the details of the filenames, paths and date modified of all files in the folder. Most of this code can be found here.
    http://software-solutions-onli…d-folders-in-a-directory/


    Quote

    - Find the 10 latest files in a folder


    Sample Code to get the modified date of a file.
    https://stackoverflow.com/ques…-of-a-text-file-using-vba


    Quote

    - Copy them to other folder


    Sample code for copying files.
    https://msdn.microsoft.com/en-…ry/2s1c774y(v=vs.90).aspx


    Quote

    - Rename all file name


    Sample code to rename files.
    https://msdn.microsoft.com/VBA…A/articles/name-statement





    Welcome to the forum


    Regards
    Ger

  • Edit Data on Userform

    Re: Edit Data on Userform


    Seems to work fine for me... selected an asset from the list, entered the transfer details and "click to update", and it entered the values on the correct row in AC to AH.


    What are you expecting to happen? Remember, we dont know how to use your software ;) , so some steps to reproduce the issue will help.


    Nice userform by the way ;)

  • BeforeUpdate event triggering as soon as textbox is clicked, in addition to after

    Re: BeforeUpdate event triggering as soon as textbox is clicked, in addition to after


    Welll..... I had a look at the form... not much use without records and workbooks etc., but I understand that it is tightly coupled to the databases etc. :)


    My bet is that the row source is effecting how the controls are behaving and triggering events on the forms unexpectedly .. for example, if the cells (content) of the rowsource range are changed (for example) on the worksheet, and the user form that contains the control is still active, then it will trigger the change event(s) of the controls that have that row source defined....


    Did you give any thought (for your own benefit), to take a copy of the entire project and strip this down to just one control that is causing pain and observe its behaviour with no other controls being triggered...


    HTH
    Ger

  • BeforeUpdate event triggering as soon as textbox is clicked, in addition to after

    Re: BeforeUpdate event triggering as soon as textbox is clicked, in addition to after


    Agree... would need to see some code... however, I dont think you are understanding/using mbevents correctly (or I could be incorrect.... :) )


    mbevents should be used to CANCEL events that you dont wish to trigger on user forms. From the language in your post its not clear to me if you know/understand this.


    for example...


    As you can see you have set mbevents = true...


    Quote

    ...The code above triggers the tb_rid_change event of the form....


    Not sure if this was a question, or statement, but the statement is correct ... Me.tb_rid.Value =.... means you are changing the value of tb_rid and it will trigger the change event for that textbox. The question you should be asking yourself is, do I WANT (or NEED) the change event to be triggered at that instance of initalising the control to a certain value. If You do NOT want the change event the be triggered, then you can cancel it by testing the value of mbevents in the actual change event code.... Whats confusing me and may confusing you, is that you are testing the negative of mbevents... which since mbevents is TRUE going into the event from the click event, will never work (it will always run the code).


    Code
    Private Sub tb_rid_Change() 
     
    Dim row_num As Long, trn_ui1 As Integer 
     :
     :
    If Not mbEvents Then Exit Sub   'this will never trigger from click event changes...


    Also, two things:
    1. Is mbEvents a global public variable (it probably should be depending on how you use it)?
    2. Also, on any form that you are using and any control on any form, is the ROWSOURCE property or CONTROLSOURCE property set to a database field or a cell or a range. This can cause untold problems with form events and after being burned a LOT I gave up using them.


    Also, the effort/act of breaking this down to its very simplest form to allow users on Ozgrid help you to debug this specific problem can often result in you understanding the problem and finding the solution... I would recommend this approach. One form, one text box and nothing else...


    HTH
    Ger

  • Retrieve data from multiple worksheets in one userform - retrieve, edit and save

    Re: Retrieve data from multiple worksheets in one userform - retrieve, edit and save


    use code tags to show VBA code... ;)


    [noparse]

    Code
    'your code goes here

    [/noparse]


    Well yes, basically when ever someone selects a new value from the drop down box (CBInvestmentLLC) this will "trapped" by an event call "change". So in that event procedure, you can take actions on all the other controls on the user form. So the steps are:
    1. find the Invement LLC value in the worksheet (find what row it appears on)
    2. initialise the controls on the user form based on the values on that row in the worksheet.
    3. do steps 1 and 2 for each relevant worksheet.


    I havent checked the names of the controls on the userform, but I presume they are called 'client', 'Address_1' and 'Address_2' etc.


    With each new worksheet in the workbook that you need to access, you have the find the row again, because the row where the Investement LLC appears might be different in each worksheet, so you need to "find" it again for each new worksheet.... so something like this, which is untested:


    Regards,
    Ger

  • Retrieve data from multiple worksheets in one userform - retrieve, edit and save

    Re: Retrieve data from multiple worksheets in one userform - retrieve, edit and save


    You have a lot of code written... and data in nicely formed tables in each worksheet, so I'm not sure exactly what the issue for you is...


    Say for example you have a textxbox on the budget tab... you can initialise this in the initialise event by referring to the textbox itself or by referring to the page that text box appears on with something like:


    Code
    DEInvestments.TextBox1.Value = "Ha ha"
DEInvestments.MultiPage1.Pages(1).TextBox1.Value = "woohoo"


    On the change event for investment LLC drop down box, you simply initialise all the controls on the user form and each page of the multipage control... of course you need to do the lookups using Investment LLC as the unique key for each worksheet, but that would be the same as any other database application. The fact that your controls are on seperate tabs/pages of a multipage control is irrelevant. Here for example, the two text boxes are on two different pages if the multipage control.




    If you want someone to code this for you for all controls on all pages and make a proper database application from it... then consider the Hire Help forum.



    Ger

  • Inserting rows without ruining my code

    Re: Inserting rows without ruining my code


    Hi Oxaru, well, yes, that is what will happen. :) When you insert a row, it pushes all other rows DOWN one row. You have no choice in that matter. So in that regard this is working as designed. :)


    Lets assume you didnt want all rows moved down one row, and you only wanted to insert a row into that first "section" without effecting the section underneath. For this to happen, you have to insert one row in section 1 and then delete one row from the bottom of section 1 to prevent it retain the original row numbering in section 2.


    However, you could leave the code as you have it right now and allow rows to be inserted... the problem you are then trying to overcome is "how do I find the start of my second section after each time I insert rows in the first section?". Well assuming there is always at least one space between section one and section two, you could do this (Code simplified a little, you dont need to use ".select")....



    HTH
    Ger