Posts by nmutimer

    Thanks in advance to all that try to assist.

    I have attached a sheet where we use the following VBA code to determine the last recorded row of data in column G. Normally this sheet would have some 3k plus rows, but i have only shown the first few to keep the file small etc.

    VST3DR = [match(2,1/(g7:g2506<>""))] + 7

    VST3DR is a declared variable. This holds the result to be used in a print routine to determine how many rows require printing.

    My issue is that some of the users need to print before all or even any entries are entered into column G.

    We could use the old fashioned method of simply highlighting the required cells and go to file then print in normal excel operations. However we need to control what printers are used hence wish to automate this option and control at the same time.

    I need to find a way coding wise to be able to determine the last row where data is displayed, bearing in mind that all the rows contain formulas that display BLANK so the ISBLANK functions do not work. Nor do i wish to be printing 3k+ rows if only 100 are actually holding visible data.


    I have another issue that is beyond my current knowledge set for VBA coding relating to Pivot Tables.

    The attached file has a Data tab, with current month data in the range A1 to V1076, this has been pivoted onto the Free Format by User tab, what i am trying to do in addition to what is already there is have a column to the right of the Grand Total showing the Total number of journals processed - i.e. the number of lines of data for that user/system/role which can then be used to ascertain what % of that total the number of free format entries made up and shown as the fourth column.

    I have tried to do a mockup in the first couple of lines

    All coding for the pivot table creation is in the attached file also.

    I can achieve the values using formulas, but as they do not move with the table or recalculate as the table is opened then they are not the required solution

    I would appreciate any assistance to resolve this issue.


    I have an issue that is beyond my current knowledge set for VBA coding relating to Pivot Tables and uncertain if it is even possible.

    The attached file has a Data tab, with current month data in the range A1 to V1076, this has been pivoted onto the Risk Score by User tab, what i am trying to do in addition to what is already there is have three more columns after the Total risk Score column that show the total risk score for the user, system and role in the pivot table for the three previous months. I have tried to do a mockup in the first couple of lines

    The previous months data is also in the Data tab but in the range A49999 to V56560

    All coding for the pivot table creation is in the attached file also.

    I have tried using multiple occurrences of the Risk Score column and then filtering so that the column only shows the data for the month concerned, by changing the source data to include all rows in the data tab, but this did not work or i did it incorrectly, but i would appreciate either any assistance to resolve this issue, or if it cannot be resolved alternative solutions,


    Thanks to all in advance for attempting to help, as it has been a long time since i had to play with VBA

    There are two sections of code below, both are creating Pivot Tables into existing worksheets within the workbook.

    The first section works perfectly time after time and i can run it multiple times without getting any errors. The second section is an almost identical copy leaving aside the destination is a different worksheet using a different table name. The code falls over with a run time error 5 at the point of trying o actually create the pivot table.

    Any helps to resolve is much appreciated as i have another six pivot tables to put into the same workbook, mos of which read form the same data tab.

    This piece works fine.

    This next section is the one with the issue where i am unable to work out the issue.

    Hopefully someone will be able to assist. In both cases i have had to cut the code after the create section due to character restraints here.


    To both gijsmo and royUK many thanks for the suggestions and options made available.

    I actually ended up doing something similar to what you are suggesting Roy, but in my case a little easier as for this case we are only interested in the last day of the month, so a simple drop down for month, and a second for year allows me to easily define the day where required during hte printing phase.

    Much appreciated guys.

    I recently installed the Date and time picker control from Microsoft for Excel, i am using Excel 2016.

    This works fine with no issue, and the usage is also not a problem, but when i email the file to colleagues they are getting an error message about a missing control which could be resolved by installing the date and time picker on the other PC, but as the file will end up going to many colleagues i am looking for an alternative solution. The DTP is used within a userform should this make any difference.

    Is there a way of including the Date and time picker control within the file being distributed.

    Thanks in advance to anyone trying to assist.

    Re: Use GetEnabled and GetVisible in same file

    Hi Rory, attached should be a file.

    I have removed all the background coding and left only the ribbon options. Select the inventory option, then aged stock, by clicking on the Create Report option the two items currently not enabled should become enabled, but the ribbon disappears.

    I have also had to remove the images to keep below the file size.

    As the title suggests, my query is whether both the GetVisible and the GetEnabled routines can be used in the same file. I have examples of each one individually that work without issue, so it is not the basic functionality at query here, but i need to be able to use both if possible to try and ensure user cannot hit certain buttons without having done other functions first, ie they cannot generate a report if they have not downloaded the data extract. If it is possible then what are the tricks to making it work.Many thanks to those in advance that help.

    Re: Error 91 when testing ActiveWorkbook.Name

    Hi cytop

    Thanks for the response, my code is in an Excel AddIn hence why there are no active workbooks if the user closes them, forgot to give that bit of info.

    Tried your code adapted for my needs and no luck, so tried it without any changes again no luck, the error handling was just not being called.

    On doing some further research online, i came across a suggestion from a VB rather than VBA forum site where the suggested solution that worked was shutting down the pc. Not a restart but a physical shutdown, leave it for approx 10 minutes and then start it up again. Tried this, and on the restart not only did your code work, but so did mine.

    Why a shut down and start up again, not a restart, resolves this issue i really have no idea, but it is resolved now.

    I have written some code where at the end the user clicks the "Exit" button. I need to test or check the active workbook name as i need to set the save as filename depending on the name.

    Previously i just checked to see what it was and then ran the appropriate code, but since i turned on Options Explicit, that piece of code no longer works and errors out with the object not set or error code 91. This only occurs if there is NO active workbook open something i need to allow for in case the user closes it manually. I have tried to error handle this using the on error goto syntax but it does not go to the error handler.

    Either i am doing something wrong, something is not set correctly, or there is another way of doing it, but despite spending much of today searching i have not been able to find the answer, so hoping someone here can turn the light on.

    The code i am trying to use is simply

    If Left(ActiveWorkbook.Name,2)="Co" Then

    I have searched this site for an answer with nothing quite the same, although several to do with csv imports.

    I have a number of excel files (sample attached) where there are text connections set up to import data from the designated file. Due to the companies use of active directory and windows directory structures, then the Application.DefaultFilePath that is used quite extensively is not set in the query as the source is effectively hard coded the first time it is set up by excel. This will obviously change on every users machine as it incorporates the users windows login id.

    What i am struggling to achieve is to come up with a vba solution that on opening the workbook will update the queries before trying to use them to update the data. I know there is a workbook_open event that could be used, but it is the vba bit i am having difficulty with. In the attached file it is the updating of the 46Parts, 46New, 47Parts tabs where the problem lies.

    If there is another even simpler or smarter solution then i am happy to be pointed in the direction of being educated.

    Thanks in advance for all those that try to help

    Over the years i have written many add-ins for various friends at companies etc, primarily in Excel 2003. They have a need to upgrade several of their systems and MS Office is one of those, that are looking at either moving to 2010 or 2013.

    My problem here is that in almost all cases the add-ins had their own menus, which in 2003 would have contained code like this:

    Set mymenubar = CommandBars.ActiveMenuBar 
    Set newMenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True

    This errors in both 2010 and 2013 and i guess would in 2007 with the move to the ribbonx with object library errors.

    Whilst i have taught myself how to in 2010 and 2013 create the menus, i am struggling with their manipulation (switching buttons on and off) but more importantly do not have the time now to go through each and every add-in.

    Is anyone aware of either a library file that can be installed to restore that functionality or any other quick method to make them work for the short term while i work out how to do it properly and then fix it using the ribbon.

    Re: Variable time delay required between file transfers.


    Apologies for the delay in replying, but was out of things for a couple of days, this worked perfectly once i actually got my head screwed on right, it is 10 years since i really played with VBA and beginning to realise i have forgotten most of it.

    Re: Variable time delay required between file transfers.

    ok, yes i did misunderstand what the original piece of code was going to do. There are times when my ability is not as good as i thought it was :oops:

    I understand in theory what you are saying, but i am struggling to see how the loop would be coded, as if i try to check to see if the checkfile return value is FALSE then i get an "Argument not optional" error.