Posts by mikerickson

Re: Multiple Criteria SUMIF Error

Another way to write that would be

=IFERROR(U230/SUMPRODUCT((SUMIFS($R$22:$DP$22, $R$15:$DP$15, U$225:W$255&"*")), "N/A")

Re: Highlight alternate days

If the dates are in column A, you could use conditional formatting with the formula =(MOD($A1,2)<1)

Re: ListBox Numbers Column Alignment

That is a built in xlRGBColor constant. At least in Excel 2011. It may have been depreciated in other versions.

replace that with RGB(175, 238, 238)

(And leave your Option Explicit

Re: ListBox Numbers Column Alignment

"Position sizer"?? Do you mean ColumnAutoFit or AutoFit ? (look in the grey box next to the left of the button)

Or something else?

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

' in userform code module


Public WithEvents AlignListBox1 as clsAlignListBox


Private Sub Userform_Initialize()
    Set AlignListBox1 = New clsAlignListBox
    Set AlignListBox1 = Me.ListBox1
    ' ...
End Sub


Private Sub Userform_Terminate()
    AlignListBox1.Delete
End Sub

I hope this helps you

Re: Setting data default of spreadsheet in Excel 2010

Try
https://support.office.com/en-…83-4e3e-a668-8254aeff6a0f

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

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

The code in post #9 is a replacement for the atest sub in post #7.

The function SortDelimitedString from post #7 is also needed.

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

Try

Sub atest() 
    Dim inputRange As Range, outputRange As Range 
    Dim inputString As String, outputString As String 
    Dim OutPutWords As Variant 
    Dim WordsToIgnore As Variant 
    Dim i As Long, oneCell as Range
     
    Const Punctuation As String = ".,;:?" 
    WordsToIgnore = Array("the", "or", "not", "to", "in", "being", "while", "on", "were", "but", "had") 
     
    Set inputRange = Sheet1.Range("A1").CurrentRegion.Columns(1) 
    Set outputRange = inputRange.Offset(inputRange.Rows.Count + 1, 0) 
     
    For Each oneCell in inputRange.Cells


        inputString = " " & LCase(CStr(oneCell)) & " " 
     
        For i = 1 To Len(Punctuation) 
            inputString = Replace(inputString, Mid(Punctuation, i, 1), " ") 
        Next i 
        For i = LBound(WordsToIgnore) To UBound(WordsToIgnore) 
            inputString = Replace(inputString, " " & LCase(WordsToIgnore(i)) & " ", " ") 
        Next i 
        inputString = WorksheetFunction.Trim(inputString) 
     
        outputString = outputString & " " & SortDelimitedString(inputString, Delimiter:=" ", Unique:= True, Descending:= False)
    Next oneCell


    outputString = SortDelimitedString(outputString, Delimiter:=" ", Unique:=False, Descending:= False)
    OutPutWords = Split(outputString, " ") 
     
    outputRange.Resize(UBound(OutPutWords) + 1, 1) = Application.Transpose(OutPutWords) 
End Sub

Re: Pulling Data from a cell that remains after filtering-- Formula needed without VB

That's a pretty general question. I'd look at SUBTOTAL and perhaps the data base functions like DGET.
Without further details its hard to give a better suggestion.

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: Excel VBA change shape color based on cell value in another sheet

Take the code that you have and put it in the Worksheet_Calculate event of the sheet with the formula.

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.

Sub atest()
    Dim inputRange As Range, outputRange As Range
    Dim inputString As String, outputString As String
    Dim OutPutWords As Variant
    Dim WordsToIgnore As Variant
    Dim i As Long


    Const Punctuation As String = ".,;:?"
    WordsToIgnore = Array("the", "or", "not", "to", "in", "being", "while", "on", "were", "but", "had")
    
    Set inputRange = Sheet1.Range("A1").CurrentRegion.Columns(1)
    Set outputRange = inputRange.Offset(inputRange.Rows.Count + 1, 0)
  
    inputString = " " & LCase(ConcatRange(inputRange)) & " "


    For i = 1 To Len(Punctuation)
        inputString = Replace(inputString, Mid(Punctuation, i, 1), " ")
    Next i
    For i = LBound(WordsToIgnore) To UBound(WordsToIgnore)
        inputString = Replace(inputString, " " & LCase(WordsToIgnore(i)) & " ", " ")
    Next i
    inputString = WorksheetFunction.Trim(inputString)
    
    outputString = SortDelimitedString(inputString, Delimiter:=" ", Unique:= True, Descending:= False)
    OutPutWords = Split(outputString, " ")
    
    outputRange.Resize(UBound(OutPutWords) + 1, 1) = Application.Transpose(OutPutWords)
End Sub


Function ConcatRange(aRange As Range, Optional Delimiter As String = " ") As String
    Dim oneCell As Range
    For Each oneCell In aRange.Cells
        ConcatRange = ConcatRange & Delimiter & CStr(oneCell.Value)
    Next oneCell
    ConcatRange = Mid(ConcatRange, Len(Delimiter) + 1)
End Function


Function SortDelimitedString(aString As String, Optional Delimiter As String = " ", _
        Optional Unique As Boolean, Optional Descending As Boolean) As String
    Dim Words As Variant
    Dim strLeft As String, strPivot As String, strRight As String
    Dim i As Long
    
    If aString = vbNullString Then Exit Function
    
    Words = Split(aString, Delimiter)
    strPivot = Words(0)


    For i = 1 To UBound(Words)
        If (Words(i) = strPivot) Then
            If Not Unique Then strRight = strRight & Delimiter & Words(i)
        ElseIf (Words(i) < strPivot) Xor Descending Then
            strLeft = strLeft & Delimiter & Words(i)
        ElseIf (strPivot < Words(i)) Xor Descending Then
            strRight = strRight & Delimiter & Words(i)
        End If
    Next i


    strLeft = Mid(strLeft, Len(Delimiter) + 1)
    strRight = Mid(strRight, Len(Delimiter) + 1)


    If strLeft <> vbNullString Then strPivot = SortDelimitedString(strLeft, Delimiter, Unique, Descending) & Delimiter & strPivot
    If strRight <> vbNullString Then strPivot = strPivot & Delimiter & SortDelimitedString(strRight, Delimiter, Unique, Descending)


    SortDelimitedString = strPivot
End Function

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: ListBox Numbers Column Alignment

Set the .ColumnWidths property before clicking any of the option buttons.

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