Posts by mikerickson

Re: Shifting column # in a referencing formula

Quote from The Heggins;776031

I absolutely agree with you the INDEX/INDIRECT would work very nicely, but lets just say that management likes to see that the cells are directly linked when they are reviewing.

Management that imposes those kinds of restraints on its coders isn't competent to review worksheet formulas.
[ insert rant that reminds you of the Wobblies (IWW) ]

Perhaps management would be satisfied by the use of Names.

Instead of having ='xxxxxxxxx\[Book1.xlsx]Sheet1'!$A$1 in a cell and being altered by the user changing OtherSheet!Z1
Perhaps the cell would contain =myCell and the name myCell referring to an INDEX function.

Re: Count no of time it appears in the data

It depends a whole lot on how your data is layed out, but some version of COUNTIF or COUNTIFS would probably be involved.

("How many times a potential buyer has viewed the car in a specific month"? If you've had enough potential buyers that a spreadsheet is warranted and the car has been on the market for months, I don't know that a formula will address the problem of why the car hasn't sold in all that time.)

Re: ListBox Numbers Column Alignment

Column Alignment is not a feature of Excel's listbox.
It is a constructed feature of the custom Object described in the class module.

Excel cannot be set to default to a custom object. (i.e. the ToolBox cannot be set to insert this custom object rather than Excel's built in ListBox object)

Re: ListBox Numbers Column Alignment

Do not use the .RowSource of the list box, that code demands that you fill the listbox other ways.

Fortunatly, the replacement is simple

Rather than

Me.ListBox1.RowSource = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Address(, , , True)

use

Me.ListBox1.List = Range(Sheet1.Range("A2"), Sheet1.Range("E65536").End(xlUp)).Value

I consider RowSource to be one of those "sounded like a good idea at the time" ideas that Microsoft occasionally has, somewhat like merged cells.

Re: Shifting column # in a referencing formula

Quote from The Heggins;775797

For specific reasons, I need to leave the formula in referencing format, which is why INDEX and INDIRECT wouldn't work here.

What are those reasons? The INDEX formula would work with closed workbooks.

Re: Shifting column # in a referencing formula

search and replace
'xxxxxxxxx\[Book1.xlsx]Sheet1'!$A$1
with
'xxxxxxxxx\[Book1.xlsx]Sheet1'!$B$1

might work.

This will convert the formula in someCell, but it will adjust all the other references in the formula

With SomeCell
    formStr = .FormulaR1C1
    formStr = Application.ConvertFormula(formStr, xlR1C1, xlR1C1, xlRelative, Sheet2.Range("a1"))
    formStr = Application.ConvertFormula(formStr, xlR1C1, xlA1, xlAbsolute, Sheet2.Range("b1"))
    .Formula = formStr
End With

One other question I have is whether that target workbook is open when this code runs. If so,

Workbooks("otherWorkbook").Sheets("otherSheet").Range("A1").Insert shift:=xlToRight 
formStr = someCell.Formula
Workbooks("otherWorkbook").Sheets("otherSheet").Range("A1").Insert shift:=xlToLeft
someCell.Formula = formStr

Re: Shifting column # in a referencing formula

If your column number is in A2 you could use the non-volatile

INDEX('xxxxxxxxx\[Book1.xlsx]Sheet1'!$1:$1, 1, $A$2)

IF you have hundreds of formulas, the volatile INDIRECT will take up resources.

Re: X to close a userform, open another?

Why not have one use form with a multi page rather than swapping different user forms in and out?
One page for Menu
One for Settings

You can use the MultiPage_Change event to set the size of the user form to be appropriate for the controls on that page.
Setting MultiPage1.Syle = fmTabStyleNone will hide the tabs. and force the user to navigate with between with command buttons.
The QueryClose event can cause the corner X to swap pages.

The user will be not be able to distinguish between this style and the multi-userfrom approach, while you will miss all the headaches. And passing variable from one page to another is much easier than from one user from to another. (Particularly when your Settings form get data from one instance of Menu and sends data to a different instance of Menu.)

Re: VBA code to remove specific special character with hyphen

It sounds like the built in Find & Replace might work for you.

Re: Iserror err

The first formula isn't complete, but you could use the AND function.

Re: VBA code to remove specific special character with hyphen

Select Case Mid(ce.Value, i, 1) 
     'Getting the special character string using the code
    Case Is = "& mystring", "–" 
        myString = Replace(ce.Value, Mid(ce.Value, i, 1), "-") 
        ce.Value = myString 
    Case Else
End Select

Mid(ce.Value, i, 1) will always have length = 1.
"& mystirng" will never be of length 1, neither will the variable mystring.
So the only time that case will apply will be when Mid(ce.Value, i, 1) = "-". But all that instruction does is replace the "-" with "-", ie. it will do nothing.

In words, what are you trying to do?

Re: Using a TextBox as a continuous Comment Box on a Worksheet

I'm not sure why you prefer to use an ActiveX text box, but I hope that someone who is familiar with them can help you with the syntax.

Re: Using a TextBox as a continuous Comment Box on a Worksheet

My Mac doesn't support the ActiveX text box that you used, so I substituted a different style of text box, that is supported by both Windows and Mac.

You didn't specify, but I assumed that the two text boxes on the user form were for Name and Comment. This uses the current username as its default.
I also added a Cancel button to the user form.

This is the code in the user form

' in user form code module


Function Text(Optional WithUserName As Boolean = True) As String
    Me.Show
    With AddComment
        If .Tag = "OK" Then
            Text = TextBox3.Text
            If WithUserName Then Text = Format(Date, "m/dd/yy") & TextBox1.Text & ": " & Text
        End If
    End With
    Unload AddComment
End Function


Private Sub CommandButton1_Click()
    Unload Me
End Sub


Private Sub CommandButton2_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub


Private Sub TextBox3_Change()
    CommandButton2.Enabled = (TextBox3.Text <> vbNullString)
End Sub


Private Sub UserForm_Initialize()
    TextBox1.Text = UserName
    TextBox1.AutoSize = True
    CommandButton2.Default = True
    CommandButton2.Enabled = False
End Sub


Private Function UserName() As String
    #If Mac Then
        UserName = MacScript("long user name of (system info)")
    #Else
        UserName = Environ("USERNAME")
    #End If
End Function

and this is the code that the button on the sheet calls.

' in normal module


Sub test()
    Dim uiComment As String, oldComment As String
    
    uiComment = AddComment.Text
    
    If uiComment <> vbNullString Then
        With ActiveSheet.Shapes("TextBox 1").TextFrame
            oldComment = .Characters.Text
            .Characters.Text = uiComment & vbLf & oldComment
            .AutoSize = True
        End With
    End If
End Sub

Re: Excel VBA - change colour of last row cells based on cell value

Could you attach a workbook with an example, including the desired results.

I'm not sure what you mean by expanding the Sheet1 range to A1:F6.
And I don't understand what you mean by "although I will be entering other rows in that range in sheet 1 and sheet 2" and how you want the code to react to these added cells.

Re: Excel VBA - change colour of last row cells based on cell value

Perhaps

Dim OneCell as Range
    For Each OneCell in Sheets("Sheet2").Range("A1:A6")
        If IsError(Application.Match(oneCell.Value, Application.Match(Sheets("Sheet1").Range("A1:A5"), 0)) Then
            oneCell.Interior.ColorIndex = xlNone
        Else
            Select Case Application.Match(oneCell.Value, Application.Match(Sheets("Sheet1").Range("A1:A5"), 0)
                Case 1: oneCell.Interior.Color = vbRed
                Case 2: oneCell.Interior.Color = xlrgbOrange
                Case 3: oneCell.Interior.Color = vbYellow
                Case 4: oneCell.Interior.Color = vbBlue
                Case 5: oneCell.Interior.Color = vbGreen
            End Select
        End If
    Next oneCell

Re: Message box if date in userform textbox is less than today

Then add the BeforeUpdate code to your module and put Call TextBox1_BeforeUpdate just before the Exit Sub line of the double click event.

Re: Message box if date in userform textbox is less than today

If you use this code, the TextBox1_BeforeUpdate will handle when the date is typed in.
The other routine explicitly calls TextBox1_BeforeUpdate when a value is put in the text box.
This example uses the ListBox1_DoubleClick event, but whatever event you are now using to double click from lookup listbox to load into textbox would work. The key is the explicit call of TextBox1_BeforeUpdate after the value has been put in the text box.

One potential issue is what you want to happen to the focus after the MsgBox appears. If the MsgBox is for information only, then this should be fine. If an early date is an un-allowable error that the user must fix, what do you want to happen to the focus after the MsgBox appears

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(TextBox1.Text) Then
        If CDate(TextBox1.Text) < Date Then
            MsgBox "textBox date has passed"
        End If
    End If
End Sub


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Text = ListBox1.Text
    Call TextBox1_BeforeUpdate(False)
End Sub

Re: Generate an array with positive integers that sum to a specified value

Your limitation that the number is between 1 and 1,000 is problematic.
If the target number is 20,000 there is no solution. Similarly, if the target is 100, there is no solution if any of the terms are 101.

You could use this UDF. All of the terms will be <= the target value although some might be greater than 1000, if the target is.
If the target value is in C1, the array formula =TRANSPOSE(RandomSumTo(C1, 15)) would be the formula used.

Function RandomSumTo(targetValue As Long, Optional CountOfTerms As Long = 15) As Variant
    Dim Result() As Double
    Dim tempSum As Double
    Dim i As Long
    ReDim Result(1 To CountOfTerms)
    
    Rem make random array
    For i = 1 To CountOfTerms
        Result(i) = Rnd()
    Next i
    
    Rem make result sum to targetvalue
    tempSum = WorksheetFunction.Sum(Result)
    For i = 1 To CountOfTerms
        Result(i) = Result(i) * targetValue / tempSum
        Result(i) = WorksheetFunction.Round(Result(i), 0): Rem make the result an integer
    Next i
    
    Rem adjust last term for round-off error
    Result(CountOfTerms) = targetValue - (WorksheetFunction.Sum(Result) - Result(CountOfTerms))
    
    RandomSumTo = Result
End Function

Re: Message box if date in userform textbox is less than today

If IsDate(TextBox1.Text) Then
    If CDate(TextBox1.Text) < Date() Then
        MsgBox "textBox date has passed"
    End If
End If

Re: Identify Most Frequently Occurring Combinations of 3

That idea was harder to implement than I thought.

BUT... instead of using number, use strings
on the patient sheet, create a helper column that concatenates each patient's data (a formula like =a1&b1&c1&d1&e1&f1)

001110
101100
101100
(call this the PatientColumn)

Then do the same to your triplet data
111000
110100
110010
'etc
Then substitute "0" with "?" on the triplet data
111???
11?1??
11??1?
'ect

Then the formula =COUNTIF(patientData, "111???") will return how many patients have the first, second and third conditions
and COUNTIF(patientData,"11?1??") will return how many patients have the first, second and fourth conditions.

A patient with all four conditions will be counted by both of those formulas.

With 27 conditions, you will need sub-helper columns
(e.g. one with =a1&b1&c1 another with =D1&E1&f1 and then another combining those two)

With 100,000 patients and 27*26*25 tripplets, this is going to be a big, slow workbook, but I think this approach is the way to go.