Posts by mikerickson

    Re: ListBox Numbers Column Alignment

    Let me know. Once I get the column widths issues resolve and get this thing robust, this technique is lots of possibilities. (differential column formatting, visible grid lines, variable row heights, long list items that wrap rather than disappear off the side)

    Re: ListBox Numbers Column Alignment

    In the attached, there is code for clsAlignListBox. As well as two demonstration user forms.

    To utilize this object, the developer should make a userform and put a normal ListBox in the appropriate location and set the defaults for that ListBox (.Font, .BackColor, .ColumnCount, .ColumnWidth, etc.) as they desire.

    Then, in the userform's code module, they should declare a public variable, with events as clsAlignListBox. Set the AlignListBox.ListBox property to the ListBox. Thereafter, they ignore the ListBox and deal only with the AlignListBox.
    The properties and methods used for clsAlignListbox are almost identical for using a MSForms.Listbox

    I hope this helps you

    Re: ListBox Numbers Column Alignment

    Thats very different that the impression I got from the OP.

    The OP lead me to believe that your issue was controling the alignment of the strings in a Listbox's columns. (And I've had a good time developing a custom control that does that.)

    Now it sound like there is also the (additional) desire for a TextBox that accepts number formatting.

    I'll get back to you on both of them.

    Re: ListBox Numbers Column Alignment

    I'm sorry it took me so long to reply.
    I had a lot of trouble making your "pad with spaces" approach work, that I devised my own solution.
    I'm finishing up the demo and I'll post it by tomorrow at the latest.
    Its a custom AlignListbox object that has many of the same properties as a ListBox. I hope its straight forward for you to use.

    BTW, I'm sure that you frequently use a ListBox's Change and Click events, are there any other ListBox events that you use frequently. (don't say Enter, Exit, BeforeUpdate or AfterUpdate :) )

    ONE MORE QUESTION: My Mac doesn't support RowSource, but it sounds like you are familiar with it. From what I can gather, RowSource is a string, the address of cells where the Listbox gets its values. One thing I'm not clear on is whether it establishes a two way connection. If the RowSource = "A1:A10" and one changes the ListBox1.List(2) with code, is that change reflected in the worksheet as well as in the list box?

    Re: VBA Code to Extract Unique Text Values from Strings in Each Cell


    Re: List Validation: displaying only those values from col A that have B set to value

    Since you are creating this extra list in a discrete place, you can use helper columns to avoid the performance hit from using CSE formulas.

    For example, if you create a new sheet (Blad3), you can put
    =IF(Blad1!B2=Blad2!$B$1,ROW(Blad1!B2), 99999) in Blad3!$A1
    =SMALL($A:$A, ROWS($$1:1)) in Blad3$B$1
    =INDEX(Blad1!$A:$A, B1, 1) in Blad3!$C$1

    then the name for your list would be
    Name: Musicians RefersTo: =Blad3!$C$1:INDEX(Blad3!$C:$C, COUNTIF(Blad3!$A:$A, "<99999"), 1)

    and your list source would be =Musicians

    If you are adding a hidden sheet, you might as well use the cells to make other helper columns that give you a dynamic list of Bands that adapts as you add new bands to the list and also sorts them by name.

    Hidden working sheets can make for some very slick workbooks.

    Re: VBA Code to Extract Unique Text Values from Strings in Each Cell

    You could use code like this.
    You can add punctuation marks to that string as needed. (don't introduce a space)
    You can also add to the WordsToIgnore array.

    Re: Excel VBA change shape color based on cell value in another sheet

    No I'm not sure what you mean by "visual example".

    If F9 contains a formula, the Change event isn't really the best place for your code, the Calculate event would be better.

    Also, my suggestion about putting the color code in the Activate event for the sheet with the shape... Unless there are formula dependencies that link from F9 back to the sheet with the shape, put the color testing code in the Activate event for the shape's sheet.

    Re: Excel VBA change shape color based on cell value in another sheet

    If I9 contains a formula, what are the precedents of that formula.
    Have you tried moving that code to the Calculate event?

    Edit: I just had another thought. I really doesn't matter what color a shape is unless its sheet is the ActiveSheet.
    Might it work if you put code like that in the Activate event of sheet Square? (qualify to make sure its looking at Sheet2!F9 for the color flag)
    That would reduce the number of times that the code has to run. (since its setting a shape's color every Change event, that can add up to a lot of time.)

    Re: Copy the active sheet and the sheet with the next sequentially numbered code name

    Instead of creating a new Worksheets object every time through the loop try setting it once and re-using it as the copy source

    Dim WorksheetsToCopy As Sheets
    Set WorksheetsToCopy = Worksheets(Array(currentSheet.Name, nextSheet.Name))
    i = 1
        WorksheetsToCopy.Copy After:=Worksheets(Worksheets.Count)
        p = p+1
    Loop until p = i

    Re: Absolute to Relative Conversion ends in #value!

    The FromStyle argument has to match the string given. Try either

    Selection.Formula = Application.ConvertFormula(Selection.FormulaR1C1, xlR1C1, xlA1, xlRelative) 
    ' or
    Selection.Formula = Application.ConvertFormula(Selection.Formula, xlA1, xlA1, xlRelative)