Posts by MD7

    Re: Resolve Addlabel auto size issue where some text hidden on zoom

    Making some progress...

    Turns out that it has something to do with the row-height - even though I'm making no reference to cells or cell-references.

    My row height was set quite small (5) when I experienced the problem. I've tried running the tool again with a much bigger row-height (100) and the second line of text remains visible at all zoom levels.

    So, not an issue with my code as such - just a stupid excel2007 artefact.

    Increasing the row height is not a solution though - it's treating the symptom rather than the cause. I need small row-heights later on in the project to be able to accurately hide sections of the drawing area.

    Now that we know what is causing the behaviour, anyone know how to make a multi-line text box/label visible at all zoom levels in cases where the row-height is small (I.e. Rows narrower than the multi-line text box/label)?

    I've an excel2007 tool that plots autoshapes and uses the Addlabel method to add a text box to each one.

    The text is invariably split across two lines with a chr(10) new line.

    The textframes have Autosize=true set and this (kinda) works.

    What happens is that the second line of text is only visible at worksheet zoom levels of around 80% and greater magnification. Below this, only the text before the chr(10) line break is visible.

    What's really annoying me is that if you right click on a label shape and follow the Format shape dialogue to the Textbox->autofit section, the checkbox is ticked. If you now cycle this checkbox off and back on, the size of the box does not change, but the second line of text now becomes visible at all zoom levels.

    I've gone so far as to write a teeny routine to cycle through all my labels at the end of processing to flip Autosize off and back on again, but this doesn't work - there seems to be no substitute for manually flicking the Autofit checkbox.

    Final part of the conundrum - my tool exports the finished shapes up PowerPoint as an Enhanced Metafile picture. The pasted picture has no issues - I.e. The second lines of text are always visible even if Excel was refusing to show them at the zoom level I copied them from...

    Re: Data Validation Drop Down Not Showing

    >So if you're validation lists magically stopped working, check to make sure you aren't deleting the drop down button by accident.

    I so wish I'd found your reply an hour ago!

    This is exactly what I was doing 'wrong'. I was programmatically deleting all shapes on a certain sheet with the exception of a command-button. As part of this, the drop-down validation arrows disappeared - but would magically reappear if the workbook was saved.

    I have amended my code to ignore both the command button and drop-down shapes - and it works a treat. I suspect many people who have the 'missing drop-down' button issue have also somehow managed to empty the Shapes collection on a specific worksheet...

    Re: SpellNumber() in Bold

                   With acell.Characters(Start:=3, Length:=4).Font
                        .Bold = True
                        .Size = 14
                        .Subscript = True
                    End With

    This is a snippet of a bit of code I used a while back to format *part* of a text string. Being as you seem to have a fairly structured string (i.e. you can find the start and end positions of the text you want in bold fairly easily), it would seem to be a reasonable way to approach the problem.

    FWIW, acell is defined as a Range (For each acell in Range("A1:B5")).

    Re: Comparing 2 Columns + Copy Value

    My solution attached:

    Re: Data for Pivot Table

    I tried creating a pivottable from your test data (in XL2003) and found no issues.

    If it is a justification issue, why don't you select ColB and force the justification to whatever you feel is appropriate???

    Easy-peasy to do in VBA - or via the Macro Recorder for that matter...

    I use Getpivotdata quite extensively to build and present data tables as I want them, but I have run into a (presumably not uncommon) problem.

    (How) Can I use wildcards or pattern matching on pivotfields and data?

    Typically, I extract all of the specific data I'm interested in:

    intClosed(intCounter) = .GetPivotData(.PivotFields(1), .PivotFields(1).Name, _
                            strPee, .PivotFields(2).Name, "Closed").Value
                intFixed(intCounter) = .GetPivotData(.PivotFields(1), .PivotFields(1).Name, _
                            strPee, .PivotFields(2).Name, "Fixed").Value
                intOpen(intCounter) = .GetPivotData(.PivotFields(1), .PivotFields(1).Name, _
                            strPee, .PivotFields(2).Name, "Open").Value

    ...and then want to hoover the rest up into one or more buckets.

    I'd *like* something similar to:

    intChangeReqs(intCounter) = .GetPivotData(.PivotFields(1), .PivotFields(1).Name, _
                            strPee, .PivotFields(2).Name, "CR*").Value total up anything matching the string-pattern CR....

    Can this be done easily? The only way I can think of doing it is to extract all of the pivot data items into an array (say), whittle these down using some pattern matching, and then loop through the processed array with Getpivotdata...

    Sounds like a plan, but has someone thought of a better approach?

    Excel 2003.

    I've had an infrequent problem with Excel when running some of my VBA projects and I think I've finally tracked it down - I was wondering if there's a better fix out there though...

    ...every now and then, when running a chunk of code (it uses the ADO library - an early version (2.1?) for compatability reasons), I get an empty pane overlaid on the main Excel window which obscures the worksheet underneath. The pane has no buttons, title etc. and can't be closed. Right-clicking on it brings up the toolbar menu...

    Once it has appeared, there is no way to get rid of it short of closing Excel down. The spreadsheets continue to work fine - it's just that you can't see the left most couple of columns...

    I've finally been able to get this as a reproducible problem - I had a general PC crash and Excel recovered some docs as a result. The recovered docs pane is exactly the same width as the mystery pane I sometimes see...

    ...I told Excel to keep the recovered docs as I needed to check them later - and now I always get the mystery pane each time I start Excel and run my code...

    'Obviously', I'd be best off dealing with the recovered docs - but is there any way of sorting this out properly - or killing the pane without having to restart Excel...?

    Re: Amalgamation one or more lines based on criteria

    OK, well I've attached one solution based on one interpretation - which is that you are only interested in the 35801 values.

    I haven't done a huge amount of testing on it, but it seems to work.

    HTH for now. Regds, MD7

    Re: Amalgamation one or more lines based on criteria

    Yes - fairly easy to achieve.

    Is '35801' the only case where you want to amalgamate the lines, or do you want to do it for any line where there are more than one repeated entry in that column...?

    <edited to remove an irrelevance relating to sorting...>

    Re: SQL call with MS Query via ADO - return unique entries only


    SELECT DISTINCT Version.description
    FROM Neighbourhood.dbo.Issue Issue, Neighbourhood.dbo.Product Product, Neighbourhood.dbo.Version Version
    WHERE Product.product_id = Issue.product_id AND Version.version_id = Issue.version_id AND ((Product.description In ('Sirius for Broking','Sirius for Insurance')) AND (Version.description Like '1._._'))

    The DISTINCT keyword solves my problem. Yay!

    I 've been quite happily hoovering data into Excel from a SQL database for a while now. I use the MS Query wizard (in Excel) to construct a SQL query that I then use with with ADO.

    My current project requires me to pop-up a userform to allow the user to restrict the search criteria - the database currently contains something like 10,000 records with multiple data fields.

    I want to find a method to efficiently create the list of unique items from one of the field entries.

    FWIW, the field I want to build the list from contains data of the form 1.X.Y (single digits with a '.' seperator - it's the version number of software under test).

    At the moment, I am generating one call to the database to return this field when it is 'like 1._._', processing this in Excel to find the unique values, populating the listbox and then allowing the user to query the db once again to return all of the data associated with the relevant records.

    This works - but the initial database call and processing is inefficient with 10k records - given that there are perhaps a dozen unique 1.X.Y version numbers in total.

    Is there a way of getting SQL to give me the list of unique entries (i.e. the dozen in this case) and cut down on the amount of data transferred in the query???

    FWIW, the first SQL query is:

    SELECT Issue.issue_id, Product.description, Version.description
    FROM Neighbourhood.dbo.Issue Issue, Neighbourhood.dbo.Product Product, Neighbourhood.dbo.Version Version
    WHERE Product.product_id = Issue.product_id AND Version.version_id = Issue.version_id AND ((Product.description In ('SW Prod 1','SW Prod 2')) AND (Version.description Like '1._._'))

    I have searched (briefly) in vain...

    I must be missing something simple - I'm playing around with listboxes for the first time and I can't work out how to prevent a horizontal scrollbar appearing in the event that Excel decides the text is too wide...

    The data I am populating my test listbox with is only a few characters long, but Excel seems to demand that the listbox be approximately 2.5 times the width of the text before it will stop forcing a scrollbar on me.

    Any ideas?