Posts by BruceMutton

Re: Check Spelling Method For Single Cell

Hmm, that seems a very long winded way of doing it.
Why not define one named range for all the cells to be checked on each sheet. eg 3 sheets, 3 names, then run the spell check three times.
Then your code would be about 5 lines, and no union function reqd.

Also, you've used relative references (no $ signs) which may make the behaviour a bit erratic

Re: Disable Scroll Not Saving When Sheet Is Closed

Here is an example of what I use...
Think its much the same as yours, just uses a different event to fire it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'   Limit scrolling area of worksheet
    On Error GoTo ErrorHandler:
'    Debug.Print "shtTimesheet selection change event fired"
        Me.ScrollArea = "$A$12:$G$65536"
ErrorHandler:
    If Err.Number <> 0 Then
        Me.ScrollArea = ""
    End If
End Sub

Maybe you need to use $ signs to make the reference absolute.

Make sure

Application.EnableEvents = True

and that the code resides in the codemodule of the workSHEET it is to act on.

Re: Workbook Protect Userinterfaceonly Fails When Shared

On reflection, the above behaviour is predictable, and in accordance with the documentation on sheet protection and workbook sharing.

I'll have to get over it.

I have a workbook in which all sheets are protected on opening, with UserInterfaceOnly := TRUE and vba is able to reliably overide the protection as is intended.

When the workbook is shared, (against my better judgement) users other than the initial user, find that vba can not overide the protection, and a runtime error is generated.

On searching this forum I found one thread that touched on this issue., more or less as follows...

Quote from jmhans

... using UserInterfaceOnly, would I be able to share the workbook and still run code effectively? Let me know if you know anything.

Quote from Aaron Blood

I suspect it 'might' work, but you'd have to test the idea and report back.

Workbook sharing is so wrought with problems I never recommend it, at least not without the default, "Peril Ahead" warning.

So, between us have we found another Excel bug, or is there a work around?
Any other experiences or suggestions?

Re: Show autofilter criteria

XLDude
Not sure if Daves post answers your questions.
I forgot to mention that you also need to declare the range variable with the UDF declaration.

Here is my old code that is a bit more long winded than the Ozgrid code, and somewhat tailored to a particular situation but has the same general structure. I like the single variable passing approach, rather than the two I use. Mine converts date serial numbers to a format you and I can understand.

Call the code below with
=StampCriteria(A11:A12,1)

in the cell formula.
I place this criteria formula in all the cells in the row above the headers, and the range called includes the header and the top cell in the filtered data. The second parameter is the number of the column in the filtered data. (I know, the Ozgrid example makes this redundant)

Then I Conditional Format the criteria cells to be BOLD and BLUE if they do not = "-ALL-" and the filtered columns now stand out in the same way as the filtered rows!

With a bit of wrap text and row auto fit the criteria row height is always as small as possible, and it all fits nicely in a table format without all the white space.

This has worked fine for some years on Excel 97, 2000, 2002 and 2003 so should be fairly robust.

Option Explicit


'***********************************************************************
Function StampCriteria(ByRef rngCell As Range, ByVal iC As Integer)
'rngCell = any cells in autofiltered list, allows Excel to detect change in precedent cells
'iC      = filter number
'Assumes that when iC=1, there may be a date to reformat...
'Debug.Print "StampCriteria UDF called"
Dim f As Filter
Dim c1 As Variant
Dim c2 As Variant


Const cNotSet As String = "-All-"


If rngCell.Parent.AutoFilterMode Then
    Set f = rngCell.Parent.AutoFilter.Filters(iC)
    If f.On Then
        c1 = f.Criteria1      ' "'" &
        If iC = 1 Then
            'need to format dates
            c1 = XtractValue(c1)
        End If
        If f.Operator Then
            Select Case f.Operator
            Case 1
                c1 = c1 & " & " & String(1, 10)
            Case 2
                c1 = c1 & " OR " & String(1, 10)
            Case Else
                c1 = c1 & " " & CStr(f.Operator) & String(1, 10)
            End Select
            c2 = f.Criteria2    'RBM 160704 moved outside If iC...
            If iC = 1 Then
                'need to format dates
                c2 = XtractValue(c2)
            End If
            On Error Resume Next    'Top 10 autofilter causes error
            c1 = c1 & c2 'f.Criteria2
            On Error GoTo 0
        End If  'f.Operator
    Else    'NOT f.On
        c1 = cNotSet
    End If
Else    'NOT AutoFilterMode
    c1 = cNotSet
End If 'AutoFilterMode
StampCriteria = c1
'Range("Criteria").EntireRow.AutoFit    'TOO SLOW to run every recalc
End Function


Function XtractValue(ByVal strText As String) As String
'Find first recognisable number in a text string, convert to date and keep preceeding characters
'Only change strText if strValue is
Dim strValue As String
Dim bFound As Boolean
Dim i As Integer
Dim strchar As String * 1
Dim intPrefixLength As Integer
    strValue = ""
    strchar = ""
    bFound = False
    For i = 1 To Len(strText)
        strchar = Mid(strText, i, 1)
        If (Asc(strchar) > 47 And Asc(strchar) < 58) _
            Or strchar = "." Then 'its a digit 48 to 57
            strValue = strValue & strchar
            bFound = True
        Else
            If bFound = True Then  'have gone beyond first number
                Exit For
            Else
                intPrefixLength = i
            End If
        End If
    Next i
    If Len(strValue) = 5 Then   'assumes a valid date serial number is 5 digits
        XtractValue = Left(strText, intPrefixLength) & Format((CSng(strValue)), "d mmm yy")
    Else
        XtractValue = strText
    End If
End Function

Good luck

Re: Show autofilter criteria

Just a passing comment...
The AutoFilter_Criteria function posted earlier in this thread, and featured in this months OzGrid Excel June 2006 Newsletter, is remarkably like one I wrote a couple opf years ago.

The volatile statement can result in the functions executing needlessly many thousands of times, making non-trivial autofiltered lists run very slowly, especially if code to do other things is piggy backed onto this function.

I got around this by ensuring the function was called from the worksheet like this...

Quote

=AutoFilter_Criteria (C3:C4)

where C3 is the header row. The inclusion of C4 acts as a "changing precedent cell", therefore forcing a recalc of the cell containing the formula only when one of the filters in the autofilter changes, making the worksheet run much faster.

I've not tested it, but I think the code posted above will run fine with the volatile statement removed and no other changes, provided a 2 cell range is passed to it as I described.

Does anyone know how to implement a similar function to highlight when a pivot filter is filtering data??

Re: Solver VBA not setting cell references!

This thread may be relevant...
http://www.ozgrid.com/forum/showthread.php?p=234253#post234253

Re: VBA solver doesn't run but 'steps into' custom function

This thread may be relevant...
http://www.ozgrid.com/forum/showthread.php?p=234253#post234253

Re: Automate Solver with VBA

...further.

QuickDraw's link to MS's KB on this problem is the key. To reiterate, MS have a hotfix for Solver that works only(?) with Excel 2000 SP3, but you need to actually phone, fax or email them to get it. The whole process took 5 minutes from dialling to verifying it worked. Beautiful!
KB Article Number(s): 821430, or search for "Excel Solver vba hotfix"

My sordid history on this issue.
Almost 10 years ago wrote a spreadsheet/vba application used in around 20 sites, in Excel 5... it worked fine, provided users could figure out how to install Solver.
Upgraded to Excel 97. ...it worked fine, but sometimes on some machines Solver would quietly do nothing when called from vba. Could not resolve other that to use a different computer, which almost always worked.
Upgraded to Excel 2000. I suspect Solver ALWAYS quietly did nothing when called from vba, but luckily it was not needed much, and as the writer I at least could figure out how to incorporate a manual Solver run in the use of the workbook.
Eventually I thought I'd try to fix it a few days ago. I spent a hell of a lot of time debugging and tracking variables and monitoring and deleting hidden names, and learnt a quite a few things about ways to pass variables. I eventually decided the Solver addin was faulty. A few others on various forums have had the same problems, but it's only on OzGrid that I found the answer (which is not to say that it is not elsewhere, I just looked hardest here)

I 'spose now I should see how it goes on 2002 and 2003.
Duh!

Solver VBA Isssues

Just to let you all know that this thread has helped confirm my suspicions that the intermitant problems I have been having with solver when activated via vba are with solver and not with my code
Thanks

In the Dec 2005 Ozgrid newsletter there is some sample code to disable commandbar controls.

The example code crashes when it trys to disable the copy button on the Clipboard menu (Excel 2000) and ONLY the clip board menu. All others are fine.

'Disable all Copy menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
            oCtrl.Enabled = False
     Next oCtrl

Any ideas why?

Re: Cannot Empty the Clipboard

A little late I know, and might be off topic

I found a few years ago that copying many charts overloaded Excel. I solved my problem by deselecting the font autosize checkbox for each chart area as soon as I created each chart.

The autosize feature significantly increases the storage required for charts it seems.

Re: worksheet_calculate specific cell

A couple of things.
1. The code does not refer to the subject of the With Block, so the With Block serves no purpose as it stands
2. The first If Statement only tests if B2 contains something, and not if its value has changed, therefore the second If statement only comes into play if B2 is empty

To be able to test for changes you will need to maintain a variable for each cell, that contains the cells previous value. Only update the variable after the value changes and you have updated the counter. In practice for a large range you will need to use an array, the size of which matches the size of the range of cells monitored.

Re: Screen Pixels and Document Points Conversion

Oh dear, this is getting too complicated.

I tweaked Chip Pearsons demonstration workbook to be modeless (to make it more interactive) and also to move the formatting toolbar to the same position as the userform, along the lines that Barry suggested.

What more have I learnt? Or think I’ve learnt?
1. The following all affect the conversion from pixels to points when using the builtin PointsToScreenPixelsX and PointsToScreenPixelsY methods.
-rowheadings
-number of toolbar rows in the docking area and
-the zoom factor

2. The datum for position properties returned as pixels is the top left of the screen.
The datum for position properties returned as points, when converted to pixels, is the VIRTUAL top left corner of the document. (ie if you’re looking at cell IV65536 then the datum is several metres up and too the left) but when its initially returned in points it’s the top left of the application window.

3. Chip’s workbook seems to work very well most but certainly not all of the time, and somehow he’s done it all without using the PointsToScreenPixels conversions. His method still gives the apparent result that pixels for commandbars and for userforms are different sizes. Ie Plot them at 0,0 and they both coincide at top left of screen (OK) Plot them near a cell midscreen and they do not conincide.

4. Zoom factor affects the size of this discrepancy, both with Chip’s method, and using the PointsToScreenPixels methods. So, there is probably a simple scale factor that depends on the zoom factor.

All I have been aiming for is to have a Commandbar or userform display near the top right hand side (probably just outside if there’s room) of the active document window, (and possibly both, to give the appearance that they are attached to each other) Chip’s approach comes close to this, and could be modified, but is still far too loose. (That doesn’t mean to say that I’m not very impressed with the effort he went to.)

Unless anyone has some inspiration I’m going to abandon this as too time consuming.

Re: Screen Pixels and Document Points Conversion

OK, Thanks for the feedback All

Thanks to xlite for” a location indicator, you can download FormPosition from CPearson:” I’ll look at it shortly.
http://www.cpearson.com/excel/download.htm

Sorry for the double post. My connection was up and down and in the process I must have inadvertently sent my initial cut of the message.

I’ll have a go at attaching my rough userform workbook for exploring positioning.

Seems to me that there are at least two sets of different sized pixels returned, one for UserForms and Applications,
And one for Commandbars and Document Windows (once converted from points)-although these are not always equal to each other or in each direction.

The workbook builds a commandbar (which is a distraction to this issue)
The BLANK button on the right shows the modless userform, and passing the mouse over it updates the values.

Change the windowstate of the application and the document window, and move them, the commandbar and the userform around to see the effect on the reported positions.
Try lining up the edges to see the discrepancies.

In trying to align commandbars etc (for which .Left and .Width returns pixels from left of screen) with document windows (for which .Left and .Width returns points from left of application window) I am having some difficulty.

The conversion function

ActiveWindow.PointsToScreenPixelsX(ActiveWindow.Left)

gives results that are inconsistent.

Anyone resolved this issue?

In trying to align commandbars etc (for which [code].Left [code]and [code].Width [code]returns pixels from left of screen) with document windows (for which [code].Left [code]and [code].Width [code]returns points from left of application window) I am having some difficulty.

The conversion function
[code]ActiveWindow.PointsToScreenPixelsX(ActiveWindow.Left)[code]
gives results that are inconsistent.

Anyone resolved this issue?