Posts by Andy Pope

    Re: custom Tab not appering when distributed as addin

    The reason the tab sometimes appears is due to the file containing both CustomUI and CustomUI14 sections.

    If you open the file in xl2007 the tab will appear.
    If you open the file in xl2010 or newer it will not. This is because excel detects both CUI and CUI14 sections and uses the CUI14 one, which has no xml code.

    You can use the editor to delete that section and then it should function on all versions from xl2007 to latest.

    Re: Produce Hybrid Of Scatter Chart & Bubble Chart

    Thank you.

    The value in cell M8 is the maximum size of the bubble, same as in F1 when doing it by formula for Area sizes.

    For those size charts 41 is the largest a bubble gets.

    Why calculating the by Area method only the maximum size is required. Minimum is always 2

    Re: Produce Hybrid Of Scatter Chart & Bubble Chart

    Actually the bubble chart is set to be by Area and my code does by width so that's why there was some differences. But there are always going to be differences as the appraoch is limited by the size you can set a data marker to.

    I have updated the existing routine and added a new one. So now you have code for Area and Width sizing.

    The whole process is restricted by the limit of marker sizes. There is no size smaller than 2 so particularly with the Area type the marker are just not small enough.

    The other limit is that marker sizes happen in integer increments and do not have the unlimited adjustment to radius as bubbles do.

    As to how to use this I think I confused you by providing too much information and not enough explanation.

    I have provide code to do the resizing. The routines do require some minimal information such as,
    • Range containing sizes
    • largest marker size
    • for ByWidth version smallest marker size

    All the other formula and values on the worksheet are only there to illustrate what the code is doing.

    Also attached xl2003 version, although AAE you might want to go and download the file format convertors for 2007.…BB74CD1466&displaylang=en

    Re: Auto-Extending Graph/Chart Range

    Create another series based on a range that has a formula.
    The formula should output #N/A for all but the cell that contains the last point.

    Apply data labels to the series. Only the valid points, which is 1, will display the label.

    Re: Insert Row Data Into Listbox

    If you use Additem to populate a listbox you are restricted to 10 columns.

    If you load the information into an array you can, but you will need to store an exact copy of the data to be displayed in the array. That is all the columns and all the rows of information.

    Re: Code Incompatible Office 2000

    The compatibility of the file refers only to the structure and contents of sheets and objects rather than the code produced.

    You have a line of code that is completely new in xl2007, which is the SetElement.
    Also because you have used Select statements what worked in 2007 actually now deletes the new created chart.

    try this revision to the last section of your code

    ActiveChart.SetSourceData Source:=Range(BuscarFecha.Offset(0, 5), BuscarFecha.Offset(IterarRetornoAcumulado - 1, 6))
    ActiveChart.ChartType = xlLine
    ' not know before 2007
    'ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Text = "Retorno Acumulado"
    ' this actually delete newly created chart.


    Re: Bulleting Within A Single Cell

    Try this, select a cell with multiple lines and run the macro

    Sub MakeBullets()

    Dim vntLines As Variant
    Dim lngIndex As Long
    Dim strTemp As String

    vntLines = Split(ActiveCell.Value, vbLf)
    For lngIndex = LBound(vntLines) To UBound(vntLines)
    If Len(Trim(vntLines(lngIndex))) > 0 Then
    strTemp = strTemp & ChrW(9643) & " " & vntLines(lngIndex) & vbLf
    strTemp = strTemp & vbLf
    End If
    ActiveCell.Value = Left(strTemp, Len(strTemp) - 1)

    End Sub

    Re: Interesting Integer Overflow

    Alternatives would be

    Select Case CLng(2)

    Select Case 2&

    or store the value in an appropriately typed variable.

    Interesting potentional error though.

    Re: Secondary Horizontal Axis Values Chart

    If you select the chart you can use the cursor keys UP/DOWN to cycle through the chart elements.

    Or you could use the Dropdown selected in the Chart Tools > Layout > Current Selection group.