Posts by Batman

    Re: Adding to a range of cells progressive results


    Hi,


    Apologies, hadn't noticed that I had left the STOP command in my post.


    You are correct in that I put the STOP command into the program so that I could step through the program one command at a time (F8) to double-check that the program was indeed calling itself.

    Re: Adding to a range of cells progressive results


    Hi,


    To avoid the Worksheet_Change event calling itself when it makes any change, I would suggest temporarily turning off event handling:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        Stop
        If Target.Column = 4 Then
            Application.EnableEvents = False
            Target.Offset(, 1).Value = Target.Value + Target.Offset(, 1).Value
            Application.EnableEvents = True
        End If
    End Sub

    Re: Preventing Combo Box drop down list from activating when not in use


    Hi,


    Have a look at this test workbook. The ComboBox has Change event code to display a message box whenever it is triggered. There are two named ranges set up, both using formulas: rngData uses OFFSET, and rngIndexData uses INDEX. The data range itself is populated with static values, so the data within the table will never change.


    If you assign rngIndexData as the ListFillRange, and make a change to either worksheet, the event code is not triggered. If you change the ListFillRange to rngData, any change that you make to either worksheet will trigger the event.


    I'm not sure whether this suggests that there is a problem specifically within your workbook.

    Re: Preventing Combo Box drop down list from activating when not in use


    As I haven't been able to fully resolve the problem with my approach, due to time available, I thought I'd best update you on progress.


    To try to determine what order events are happening in, I added a Debug.Print statement to the Worksheet_Change, Worksheet_Calculate, and ComboBox_Change event procedures. It seems that, when you make a change to either of the two worksheets, the first event that fires is the ComboBox_Change event.


    I'm not sure why that is the case, and I want to look to see if I can build a similar structure and replicate that.

    Re: SET Range - separate sheet


    You haven't referenced the Cells objects within the Range to any particular worksheet. Try

    Code
    With wss
        Set Range1 = .Range(.Range("E2"), .Range("E2").End(xlDown))
    End With


    As it stands your code is trying to set a range in sheet 'Data' where the range of cells (because there is no link to the sheet) is in the active sheet, which presumably is not Data.

    Re: Excel 2013 table ends and not all rows are displayed


    Hi Arianna,


    I'm not sure that I can resolve your issue, but I did just want to let you know that I might have come across something similar, which you will probably be able to replicate for yourself (I don't want to upload a workbook as it will probably crash Excel).


    In a recent project, I found that if you have a Table in Excel, and add one of the fields of the table as the RowSource for a ComboBox in a VBA userform (e.g. RowSource = Table1[HDR1]), if you then add a row to the table, either in VBA or in Excel by tabbing from the last row of the table, you will crash Excel. You can create a very simple test to try this for yourself.


    I have not been able to find a resolution for this problem. Although it might not be exactly the same as your issue, it does seem to highlight that Excel may occasionally have technical problems when handling Table changes.


    For information, I got round my problem by changing the Table to a Dynamic Named Range. This might, or might not, be an option for you.


    Hope this helps.

    Re: MAX IF AND Array Formula


    Try


    =MAX(--(ISNUMBER(SEARCH("Dec-15",Data!$C$1:$C$187)))*--(Data!$L$1:$L$187="Core")*Data!$F$1:$F$187)


    If that doesn't work, you will probably need to upload a sample of your actual data.

    Re: MAX IF AND Array Formula


    Assuming that your data in column C is in numeric/date format, try:


    =MAX(--(MONTH(Data!$C$1:$C$187)=12)*--(YEAR(Data!$C$1:$C$187)=2015)*--(Data!$L$1:$L$187="Core")*Data!$F$1:$F$187)


    It's not clear from your original post whether you have entered your formula in array format, but it is an array formula and as such needs to be confirmed with Shift+Ctrl+Enter.

    Re: SUMPRODUCT Formula works, include date format


    What you are not explaining is HOW it doesn't work. What results are you expecting, and what are you seeing that are wrong?


    My guess would be that you are seeing incorrect results where the date by itself would put the item into one category, but the addition of the time throws it into a different category.


    What you need to bear in mind is that dates and times are simply numbers. One day equals one whole number, and times are represented by fractions of the number 1 (e.g. 01:00 1:00am = 1 divided by 24). Today's date, 6 January 2016, is represented by the number 42375. Midday on Jan 6 would be represented by 42375.5.


    Your formula is taking the contents of cell I4635 and deducting 30, presumably to find items that are 30 days old. Including or excluding the time in those calculations is therefore in some instances going to impact on the result.


    If you want to ignore the time in your calculations, based on Carim's suggestion, you can use something like
    =SUMPRODUCT(--($E$2:E4635="NPR"),--(INT($N$2:N4635)=INT(N4635)),--(INT($I$2:I4635)<=INT(I4635)),--(INT($I$2:I4635)>=INT(I4635)-30))


    INT would just remove the decimal portion of the number.

    Re: Preventing Combo Box drop down list from activating when not in use


    Given that [H3] is also used in the definition of the DropDownList range name, then it would probably be a good idea, but it might depend on which solution you opt for. As Rory's solution only populates the list at the point that you click into the ComboBox, it avoids the problem affecting the drop-down at other times.


    With regard to replacing the formulas for [H3] and [H4], if that's what you decide to do, without knowing exactly what you are using the Product_Database columns for, it does seem that the formulas are a little over-complicated. Even if you opt for Rory's solution, it might be a good idea to avoid using OFFSET, as the recalculation will still happen every time you change a cell. Depending on the size of your live table, that might or might not represent a problem. Using the field names in conjunction with INDEX and/or MATCH would probably suffice.

    Re: Counting how many times a task takes between dates and then counting how many tim


    There are a number of different ways to do it, the simplest probably being:


    Go to the Formulas ribbon, and under the Defined Names section click Name Manager. Select the Categories name and, in the Refers To box at the bottom of the screen, change the cell references within the sheet to be $E:$E, i.e. the entire column. Don't use the arrow keys when making the change, as you will get unwanted cell references appearing; use the mouse to position the cursor for the changes. Click the tick mark to the left of the box to accept the change.


    Repeat for the Months name, this time changing to $D:$D.


    By way of explanation for the 'new thread' request, thread titles are used to enable others to search for problems similar to their own, so answering multiple different questions within one thread effectively 'hides' possible answers for others, even if they occur within the same workbook/project. However, as the second question was linked to my resolution for the first, no need for a new thread this time.

    Re: last column to start in the first column if column is empty


    Hi megatronixs,


    Unless the information is used elsewhere in your program, there are a few bits of unnecessarily complicated coding. Try this

    Re: Unlock/lock cell range based on textbox input


    It's likely that what you are trying to change is protected. Don't forget that, even if you have unprotected one row, other rows in the same column will be protected, and you can't change their column width. Try setting some properties of the worksheet protect statement, e.g.

    Code
    Activesheet.Protect AllowFormattingColumns:=True

    Re: Unlock/lock cell range based on textbox input


    Are you planning for your users to enter the ID in the textbox, or cell B16? From the layout of the sheet, I assume you would enter the ID in the textbox.


    There is a problem that, if you are entering the ID in the textbox, the Change event is triggered as soon as you type in the first character of the ID. Would it not be better to get the user to select their name and/or ID from a drop-down list, either in a cell or a ComboBox control?


    There is currently also a high probability that one of your events will trigger another, and the Worksheet_Change event is not testing to determine which cell has been updated.

    Re: Unlock/lock cell range based on textbox input


    Hi,


    Within the Textbox1_Change procedure, you have this portion of code:

    Code
    With Range("B16") 
            .ClearContents 
            .Locked = True 
        End With


    So, when your user enters an ID in cell B16, the code behind the form clears the user ID from the cell and locks it. Your code is therefore trying to change the contents of a locked cell.


    That said, I'm actually not quite sure how you manage to enter another ID in order to call the code that is erroring. I'm also perplexed as to why the code to clear B16 doesn't generate another change in the textbox, and call the change program again (or perhaps that is what is happening). You might need to upload a sample workbook, and explain what you are attempting to do with the user ID.


    The reference to the range C5:J7 refers to my previous post, when I said that you need to lock all the cells in the range that the users might use, before unlocking the ones relating to the specific user. Unless there is a more appropriate point at which to lock those cells, it seems to me that doing it after the user ID has been changed, and before they are able to select anything, is the best time. However, I have just noticed that I mis-typed the range and put J7 instead of JC7.

    Re: Unlock/lock cell range based on textbox input


    Hi ejm9,


    Welcome to the Ozgrid forum.


    You need something in your code to lock the cells that you are not going to use. You first need to lock all the cells in the useable range, and then unlock the ones relevant to the user ID entered.


    You have a redundant 'End Select' in your code, so the program won't compile and run as it stands.


    You don't need individual 'Select Case' statements for the various ID tests - one will do.


    I would also suggest a test to make sure that a valid ID has been entered.


    Is there anything else in your code that unlocks cell B16, as you lock it thereby preventing a change of user ID?

    Re: Preventing Combo Box drop down list from activating when not in use


    Hi MattRNR,


    Welcome to the Ozgrid forum.


    The problem, I believe, relates to a chain of events, starting with the formula within the [H4] field of the Product_Database table, and ending with the ComboBox1 event code.


    The formula for the [H4] field contains the OFFSET function. OFFSET is a 'volatile' function, which recalculates on any Excel recalculation (as happens when you change any cell). This forces the Product_Database table to change. The named range 'DropDownList' is built using the Product_Database table, so that too changes. 'DropDownList' is used as the ListFillRange for ComboBox1, so the change invokes the ComboBox1_Change event code. The event code contains the command .Dropdown to display the list.


    If you change the [H4] formula to avoid using OFFSET, you should avoid the problem.