Posts by pstraton

pstraton · Apr 7th 2016

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"

pstraton · Dec 1st 2014

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]

Code

Range("A1").Locked = True

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

Code

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]

Code

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).

pstraton · Oct 24th 2014

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?

pstraton · Aug 19th 2014

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.)

Code

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

Display More

pstraton · Nov 26th 2013

Re: length of array

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

Code

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

Display More

pstraton · Mar 27th 2013

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.

pstraton · Apr 19th 2008

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:

Code

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

Display More

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

pstraton · Apr 18th 2008

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?

Code

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

Posts by pstraton

Obscure bug in UserForms ListBox support: "Exception occurred"

Unable to Set the Locked Property of the Range Class

Text Size Increases On ActiveX Controls (Control Toolbox)

Disabling The Control Drag And Fill Function

length of array

Horizontal Alignment Error in Excel 97 but OK in XP

Capture Print Zoom Value Before Printing

Capture Print Zoom Value Before Printing