Posts by pstraton

You must explicitly define the contained controls' event handler class(es). For an example of how to do that, see:

MsForms.Control Event Class

and

Events (Microsoft Forms)

Office VBA reference topic

learn.microsoft.com

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.: http://www.cpearson.com/excel/Protection.aspx. 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://"http://www.cpearson.com/excel/Protection.aspx"

http://"http://www.cpearson.com/excel/Protection.aspx"

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,

[INDENT]

Range("A1").Locked = True

[/INDENT]
[INDENT]then reference the whole merged range instead:
[/INDENT]
[INDENT]

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

[/INDENT]

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

[INDENT]

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

[/INDENT]
[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!
[/INDENT]

• 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.
Carl

What does "...is 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.)

Dim SaveDragAndDrop As Variant  'For persistence, is declared at module level


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'NOTE: This event fires first, then the Worksheet_BeforeDoubleClick event.
    '
    'WARNING: Setting a breakpoint in this event will, in effect, cancel any BeforeDoubleClick event, so you can't
    '         single-step through the whole sequence!
    
    'To prevent unwanted jumping to the "End" of a data-set if the user accidentally double-clicks onto the cell
    'border (which is an effect of CellDragAndDrop), disable that functionality while in the range where that
    'behavior is a problem.
    
    If Not Intersect(Target, Range("MyProtectedRange")) Is Nothing Then


        If IsEmpty(SaveDragAndDrop) Then
            SaveDragAndDrop = Application.CellDragAndDrop
            Application.CellDragAndDrop = False
        End If
    Else


        If Not IsEmpty(SaveDragAndDrop) Then
            Application.CellDragAndDrop = SaveDragAndDrop
            SaveDragAndDrop = Empty
        End If
    End If
End Sub

Re: length of array

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

Option Base 0  'Un-comment for zero-based arrays
'Option Base 1   'Un-comment for one-based arrays


Sub ShowArrayLen()
    Dim abcarray() As Variant


    ReDim abcarray(3)   'i.e. some run-time array size value


    MsgBox "Range: " & LBound(abcarray) & " to " & UBound(abcarray)
    MsgBox "Length: " & UBound(abcarray) - LBound(abcarray) + 1
    MsgBox "Ubound: " & UBound(abcarray)    'does not = length for zero-based arrays!
End Sub

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:

MinPrintZoom = 46
    
    With Application.ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = "$A:$B"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .Zoom = 101     'Set to large default value


        PagesTall = 0
        Do
            PagesTall = PagesTall + 1
            PagesTallZoom = .Zoom       'Capture current zoom value
            
'...
                        Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{1," & PagesTall & "})"  '{Wide, Tall}
                        Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})"            '{Wide, Tall}
        
                        MsgBox "Pages Tall = " & PagesTall & ", Paper Size = " & PaperSize & _
                            ", Orientation = " & Orientation & ", Zoom = " & .Zoom  'Debug
'...


            'Check of a minimum allowed zoom value has been reached or if no more progress can be made with these settings
        
        Loop Until .Zoom >= MinPrintZoom Or .Zoom = PagesTallZoom


        MsgBox (.Zoom >= MinPrintZoom) & ": Zoom = " & .Zoom  'Debug
    End With

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!

Peter

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?

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