Posts by mrfitness

  • Print Preview Of Sheets Meeting Criteria

    Re: Creating A Dynamic String In A Loop With Quotations


    It prevents the user from changing margins and setup :)

    Quote from shg

    What's the EnableChanges argument to PrintPreview, Mavyak? I can't find it in the Object Browser ...

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]Mavyak that works wonderfully!
    Where would I be able to put the following code, to enable rows 1 through 5 to be repeated incase multiple pages are printed for one sheet?:

    Code
    With ActiveSheet.PageSetup


        .PrintTitleRows = "$1:$3" 'ROWS TO REPEAT
        .PrintTitleColumns = ""


End With


    Quote from Mavyak

    See if this works for you:


  • Print Preview Of Sheets Meeting Criteria

    FYI Dave H:
    I found out how to use the quotations so I wanted to change this title to:
    "Print preview using a dynamic string array" but the changes won't apply. Is this something I do not have the ability to do as a user?



    I have 7 sheets I am looping through, in each sheet I am checking if the value of the cell CD1 if greater than 1. If it is, I want to add the worksheet name to a string that will later be used to print preview as an array.
    Here is what the array would end up looking like (if 3 sheets had a value greater than 1):

    Code
    Sheets(Array("E9AM", "E10AM", "Grd")).printpreview


    So i want to build a string that keeps adding each part of the loop, so the variable would end up being "E9AM", "E10AM", "Grd". This way I can then run the code

    Code
    Sheets(Array(stringedvariable)).printpreview


    I am having problems with the sheets array. I get a subscript out of range error.


    Below is the code I have. I thought I needed to get rid of the quotations around the variable x once the loop is done, as I got an error on the last line. But that wasn't the case.
    Any suggestions on how to solve this?
    Thanks

  • Disable All Shortcut Keys

    Re: Disable All Shortcut Keys


    Excellent idea! How would you code that to call a sub?

    Code
    Application.OnKey "{F11}", noway()


    Does this mean when a user clicks F11 it will run the sub noway() ? I was getting errors with that line.


    How can I restore user defaults and not excels defaults? I thought if I enable the command bars back they won't be visible unless they were before.


    Quote from shg

    Consider a macro that pops up a message when the user does those things that explains that they are disabled, and why.


    And remember that users will want their menu customizations restored, not set back to Excel defaults.

  • Prevent Customization Of Menus And Toolbars

    Re: Prevent Customization Of Menus And Toolbars


    This is what I did:
    on workbook open i put in

    Code
    ReallyClose = False


    On Workbook_BeforeClose I put in:

    Code
    If Not ReallyClose Then
        MsgBox "You can only close PAT by clicking the Close button ('X') on the Toolbar on the left", , "Note:"


        ReallyClose = False


        Cancel = True
        Exit Sub
    End If


    And if a user clicks the 'X' button on my custom menu, it will run this sub:


    So once the workbook opens, ReallyClose is false. If they try to close it using the Excel 'X' close button, it would go into the "If Not ReallyClose Then" On the Workbook_BeforeClose event.


    From that point they will know to use my custom close button, which in that Sub closeit() gives them the option to save or not before closing, as well as cancelling.


    If they save either way, reallyclose goes to true to allow the workbook to close on the workbook_before close event. If they cancel, reallyclose goes back to false incase they hit the Excel 'X' again it will still not allow them to close.


    I really thank you for your help on this! :)

  • Disable All Shortcut Keys

    Re: Disable All Shortcut Keys


    Totally, I am already preparing a list of people for testing purposes.
    I now realized the hard way that when you put things into excel like a custom menu, you cannot forget to take them out when a users opens another workbook! (and the same goes for removing things too!)


    The same code I used to disable all command functions, I will use again to enable.


    Quote from shg

    You're going to want to make sure this app is bulletproof before it's deployed, with extensive testing.


    And whatever you do with menus, make sure you put them back EXACTLY as you found them, or users will come hunting you in the night with dogs and torches. I would be at the front of the mob.

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]If I was a user opening up an application created for my team to make my life easier at work, I dont think I would complain if Ctrl+C wasn't working :P


    I am not sure if you read what I wrote in my original post, but I did say I would of course enable these functions back for users that open up other workbooks :)


    Quote from Dave Hawley

    REALLY bad idea! How would YOU like all YOUR short cut keys disabled after opening a Workbook?

  • Disable All Shortcut Keys

    Re: Disable All Shortcut Keys


    Well this program is for sales people. We are basically created an application through excel!My boss really wants to disable the functions since they are apt to messing things up. It's a huge file, 21MB with many sheets, references, lookups, protected sheets, etc. I found that on some pages if the user wants to do a cut and paste, copy and paste, or a drag down, it messes up all the formulas that reference those cells. We even have to restrict saving and printing on certain pages!


    This program is doing heavy calculations across various pages and in NO way can I take a chance on a user messing around with cells to either cause the program to crash, or cause improper calculations which would result in the company losing money as it is rating revenue based on user input.




    Quote from mikerickson


    Why do you want these keys disabled? Perhaps there is a gentler way to achieve your end.

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]First of all I have yet to release this to my user so I am not being cursed yet :)


    Im sorry about the file you opened when you were trying to help me, I assume it was the custom toolbars that messed up your excel? I was learning about custom toolbars when you were trying to help and did not know about disabling and deleting them on close (by the way I have that working now)


    What do you mean by me needing to 'read text on the page that I start a new thread from'? With all due respect, you have to accept the fact that some people's english and grammer are not as good as others instead of simply banning.


    Quote from Dave Hawley

    I have had the same issue with Workbooks from MrFitness and no longer open anything he attaches. His users must be cursing him and for good reason.


    MrFitness, you need to READ the text on the page you start a new thread from! It's not that hard! In fact, anymore assumptions of your means to an end will result in a 2 week ban.

  • Disable All Shortcut Keys

    I want to disable ALL shortcut key functions in my program. (i.e. if a user presses Ctrl+S it will NOT save, Ctrl+O will NOT open file, etc).


    Of course I need this enabled again in something like workbook close for users to have this ability in other workbooks.


    Your help is greatly appreciated :)

  • Prevent Customization Of Menus And Toolbars

    Re: Prevent Customization Of Menus And Toolbars


    If you go to the link http://www.jkp-ads.com/Articles/DistributeMacro04.htm you will see what I mean. If another instance of excel is open with my workbook open too...if the user tries to close my workbook it will automatically reopen excel.
    So I create a close button in my custom toolbar which calls the sub below:

    Code
    Sub CloseMeNow()
    bMeClosing = True
    ThisWorkbook.Close
End Sub


    If you cannot open the link, this is the explanation there:
    A special situation arises when only the workbook with the code is to be closed (e.g. when an add-in is uninstalled). Then the Workbook_BeforeClose code runs, sets the OnTime event and the workbook closes. But immediately Excel will load the workbook again to process the scheduled OnTime event. It will even show the enable macros dialog again.


    To shut down the utility itself, one needs to change the value of bMeClosing to true and subsequently close.

  • Prevent Customization Of Menus And Toolbars

    Re: Prevent Customization Of Menus And Toolbars


    I see what you mean, I get the same results!


    So is there any way to get rid of the titlebar (blue bar at the top) without using displayfullscreen?


    If not, how about altering the close button ('x') on the title bar to call a sub I created that closes the workbook without reopening it?

  • Prevent Customization Of Menus And Toolbars

    Re: Prevent Customization Of Menus And Toolbars


    I called it in the workbook activate event and it worked, thanks!...but now I noticed with displayfullscreen = true my worksheet tabs are hidden. I cannot have this...so I disabled that. Now I have the Titlebar showing...is there anyway I can remove that?

  • Prevent Customization Of Menus And Toolbars

    I have a custom toolbar created in a workbook. I have coded my workbook so that the full screen is displayed, removing the title bars, status bars etc

    Code
    Application.DisplayFullScreen = True


    However, there is still another menu option I am not able to remove. It is the "Add or Remove Buttons" and when you click that the "Customize..." button also is enabled.


    I looked up some code on microsoft and only found code in office97 that gave me errors in 2003:

    Code
    Sub DisableCustomize()
        Application.CommandBars("Tools").Controls("&Customize...").Delete
        CommandBars("Toolbar List").Enabled = False
   End Sub


    The quote from the site says "To prevent others from changing the menus and toolbars in a custom application, remove or turn off the Customize option on the Tools menu and turn off the Toolbar List shortcut menu."


    I get an error saying invalid procedure when I run that.


    Any suggestions on how to disable the button or prevent users from choosing it?