Posts by pstraton

    Re: Obscure bug in UserForms ListBox support: "Exception occurred"

    Using UserInterfaceOnly (UIFO) protection will also take care of this problem. If you are unfamiliar with UIFO protection, take a look at Chip Pearson's excellent tutorial on protecting worksheets, etc.: UIFO protection is a little tricky because you have to re-establish it at every workbook-open event (and there are some VBA sheet-change operations it doesn't allow without full un-protection), but once you get your workbook initialization system working, it's extremely handy--saving you lots of unnecessary and potentially buggy un-protect/re-protect code!http://""


    Re: Unlocking A Cell Locked Property

    Here’s a complete explanation:

    Typically, there are two causes of this error: trying to change a cell’s Locked property on a protected sheet and/or trying to change the Locked property of a single cell in a merged range.

    In the first case, you can either unlock the sheet or set UserInterfaceOnly protection for it, which is highly recommended since you then don’t have to mess with repeatedly unlocking/locking it.

    With regard to merged cells, you can’t lock a single cell that is part of a merged range but there are reasonable options:

    • If you are using cell-address notation to reference the cell/range,


    1. Range("A1").Locked = True

    [INDENT]then reference the whole merged range instead:

    1. Range("A1:A3").Locked = True 'where "A1:A3" is the entire merged range


    • If you are using a named range for a set of merged cells, by default it will be defined to reference the first of the merged cells only. You can either edit its definition to include the entire merged range or use its MergeArea property to reference its associated merged range:


    1. Range(“SomeNamedRange”).MergeArea.Locked = True

    [INDENT]But note that you can’t do both since the MergeArea property is, apparently, undefined for a range that is not a strict subset of a larger merged area!

    • Of course, you can always unmerge the merged range before setting the Locked property of an included cell and then re-merge it afterward, but I have never seen a situation in which one of the above two solutions wasn’t sufficient (and much cleaner).

    Re: Text Size Increases On ActiveX Controls (Control Toolbox)

    Quote from carlmack;307419

    One thing : If the zoom on the sheet that you have the control is different to that that is referenced as a source in the control Excel starts to have problems. Set the zoom to 100 on all pages and test.

    What does " referenced as a source in the control" mean?

    Re: Disabling The Control Drag And Fill Function

    Quote from Yaim36;343786

    I need to disable the function only in certain cells within certain sheets. Any suggestions?

    Here's how I do it. In this example, the unwanted side effect of enabled CellDragAndDrop is that, if the user double-clicks on a cell border, the selection automatically jumps to the "End" of the existing data in that border's direction in the worksheet. (This code is used in combination with a Worksheet_BeforeDoubleClick event handler that is referred to in the commentary but not shown here, since that's a different topic.)

    Re: length of array

    The above solution doesn't work for zero-based arrays. Here's a complete example.

    Re: HELP!! HorizontalAlignment Error in Excel 97 but OK in XP?

    Quote from Werner;187853

    I agree with you at 100 %. A reload of the workbook did the job, and effectively, computers can make errors too, just as humans. :)

    I know this is an old thread but, due to therecurring nature of this Excel bug (I have seen it in 2003 too), thephilosophical trailer here is actually relevant to all who take programmingseriously: The idea that its OK for computers to have bugs and "makemistakes" because humans do, or that having to restart a program is likehumans needing sleep, is a really, really lousy excuse for amateur programmingand the resulting functional failures. It is the kind of attitude thatlets Microsoft (and other purveyors of flaky code) off the hook for doing aquality job. Please, please,please do not perpetuate this myth! There is no excuse for buggy code.

    Re: Page.setup Fails Under Worksheet_activate()

    Thanks for the clarification, Bill. I am running this code under a sheet module and it does execute OK but produces the wrong result there. My version of this code is actually slightly different from the original post in that the PagesTall value is variable:

    When I run this manually, it runs fine, returning a value of 51% for the data on the particular page being evaluated. But when I run it under Worksheet_Activate() the PagesTall and PagesWide fail to be set to the specified values, and .zoom always returns 100%, regardless of what data is actually on the sheet.

    My sense of it is that the problem lies with the first call to PAGE.SETUP which seems to be failing (though it doesn't return a trappable error condition), so the PagesTall and PagesWide end up set to the default instead. (The resulting state of the sheet confirms this.) So, in a sense, the resulting zoom value of 100% is correct, but only because the Tall/Wide update call failed.

    Thanks in advance for your help!


    In a previous, expired thread, the following code was suggested as a way to capture the page-setup zoom value after setting PagesTall and PagesWide. It works fine when single-stepping in the VB Editor and when run directly by the user, via a button click or Tools=>Macros...=>Run, but fails when run under a Worksheet_Activate() event call. Does anyone understand why that is and/or have a fix or workaround?

    1. Sub X()
    2. '
    3. Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{1,#N/A})"
    4. Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})"
    5. MsgBox "Zoom factor is " & ActiveSheet.PageSetup.Zoom
    6. End Sub