Posts by rory

    Re: Listboxes won't update properly when screen updating is turned off

    It's hard to test since the lookup sheet isn't in your workbook, but I would alter the code behind the listbox sheet to:

    Re: Help with a small VBA problem

    You have two issues:
    1. In a worksheet code module, a call to the Range property always refers to the sheet containing the code, unless you specify a sheet.
    2. You should never perform a Find operation and attempt to manipulate the result range in the same line, since there may be no range found.

    With that in mind:

    Re: Summing Numbers Of Multiple Cells That Also Contain Text

    Based on your original formula, which appears to involve regularly spaced cells and assuming a space between the numbers and the letters:
    =SUMPRODUCT((MOD(ROW($D$5:$Q$23)-5,18)=0)*(MOD(COLUMN($D$5:$Q$23)-4,13)=0)*IF(ISNUMBER(SEARCH(" ",$D$5:$Q$23)),--LEFT($D$5:$Q$23,SEARCH(" ",$D$5:$Q$23)-1),IF(ISNUMBER($D$5:$Q$23),$D$5:$Q$23,0)))

    An additional column/sheet would indeed be easier and more efficient though! :)

    Re: Summing Numbers Of Multiple Cells That Also Contain Text

    You need to apply the format to the cells you are summing - i.e. D5, D23 etc. then you can have actual numbers in them. For example, if you format D5 as 0" HOL" and type 8 in it, it will display as '8 HOL' but still work in the SUM formula.
    Without knowing your workbook layout I have no idea if that will help you.

    Re: Workbook with two active windows - event generated when one window is closed?

    Try this code in the ThisWorkbook module of your workbook:

    Re: VBA to change the Font Color within a Cell Segment

    You could try this: