Forum Widgets
Latest Discussions
Checkboxes: currently unreconcilable differences between Excel Online and Excel 2024
I started working on a database that I thought would be simple. I start with a product list. Each inventory item in that list needs a quality control check from each of 6 departments. In each row, I wanted to set a color range from red to green on the background depending on how far along the check process is coming: for example, the row is red when 0 checks have been completed, at any 3 out of 6 checks complete it's at yellow, and if all 6 checks are fulfilled/complete, the item background changes color to green. So when I visually scroll the list, with my dyslexia I can more easily see at what phase the progress is at. Those are the basics behind why I decided on checkmarks. I started with Excel 2021 and had some limitations regarding checkboxes. I had to study how to switch to developers mode to access checkboxes in the first place, checkbox placement is less than user friendly, and 1600 rows and 6 columns of checkboxes lagged my PC so badly I had to wait about a minute after scrolling the document before the screen caught up. I began to suspect I was missing some new functions with my older Excel, so then decided to try Excel Online mostly for functionality testing. Excel Online worked flawlessly from the get-go. First off, I could select the whole column and select checkbox on the whole column with one click, which was super slick. I got my 6x conditional formatting setup almost on the first attempt super easy. Then, I went to set a floating watch window so could monitor real-time values of which departments were getting more checks completed. Bummer, Excel Online doesn't support floating watch window. Since I also really wanted to finish work on this file locally offline, I decided to buy the newest version of Office 2024 thinking I was missing some functions with an older version. After installing Office 2024, I downloaded the excel file from Excel Online to my PC, and opened it with my shiny new Excel, and the first thing it did was change all my checkboxes to boolean true or false. After much online searching, it also appears the most efficient way to add checkboxes to 1600 rows in 6 columns in Excel '24 is to learn how to use VBA macros to automate the process. I WAS able get my floating watch window up and running with my autosum values from each column, so there's that... I am a pitiful beginner to Excel. Having said that, I keep reviewing, looking for something I'm missing, but a reasonable time-efficient solution is just out of my reach. Why would Excel '24 not let me work on my database locally the same way I was working with it in Excel Online? I would hardly believe the dev team overlooked this. Or has my solution been misdirected from the start? I'll appreciate any input and thanks for reading my novel. BR, X
Excel VBA Object Variable or With Block Variable Not Set Error
Hello! I'm trying to figure out where I'm going wrong with my code. Basically I want my code to decide what protections to initiate and how to proceed based on the data in column C on the 'LOA' tab. My table starts at A8 (headers) and is dynamic. My code should be allowing user entry from C9 to last row and only allowing users to edit columns A-B & H-O if there is data selected in Column C for that row. I had to adjust some of the ranges and that's where I started receiving the error. My original code allowed users to edit the headers because there was data in C8 so I tried to update the code to prevent that. My current code is below: Private Sub Workbook_Open() Dim ws As Worksheet Dim wsRef As Worksheet Dim lastRow As Long Dim lastRowRef As Long ' Set worksheet references Set ws = ThisWorkbook.Sheets("LOA") Set wsRef = ThisWorkbook.Sheets("References") ' Unprotect workbook and sheets ThisWorkbook.Unprotect "123456" ws.Unprotect "123456" wsRef.Unprotect "123456" ' Sort data on LOA sheet lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("C8:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ws.Sort .SetRange ws.Range("A8:O" & lastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Set up data validation for Employee Name lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row With ws.Range("C9:C" & lastRow).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef End With ' Set formulas for columns D-G ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)" ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)" ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)" ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)" ' Find the last row with data in column C lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Set the range for column C Set dataRange = ws.Range("C9:C" & lastRow) ' Loop through each cell in column C For Each cell In dataRange If Not IsEmpty(cell) Then ' Unlock corresponding cells in A, B, and H-O ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = False Else ' Lock corresponding cells in A, B, and H-O ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True ws.Range("H" & cell.Row & ":O" & cell.Row).Locked = True End If Next cell ws.Range("C9:C" & lastRow).Locked = False ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True ' Protect References sheet wsRef.Protect "123456" ' Protect workbook ThisWorkbook.Protect "123456" End Sub Option Explicit Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _ "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work." Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim lastRow As Long On Error GoTo ErrorHandler lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row If Not Intersect(Target, Me.Range("C9:C" & lastRow)) Is Nothing Then Application.EnableEvents = False Me.Unprotect "123456" UpdateCellLockStatus Target Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End If ExitSub: Application.EnableEvents = True Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbCritical, "Error" Resume ExitSub End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.CutCopyMode = False Then Select Case Application.CutCopyMode Case xlCut ShowWarningMessage "cut" Case xlCopy ShowWarningMessage "copy" End Select End If End Sub Private Sub UpdateCellLockStatus(ByVal Target As Range) Dim cell As Range Dim affectedRange As Range For Each cell In Intersect(Target, Me.Range("C9:C" & lastRow)) Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _ Me.Range("H" & cell.Row & ":O" & cell.Row)) affectedRange.Locked = (cell.Value = "") Next cell End Sub Private Sub ShowWarningMessage(ByVal action As String) MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning" End Sub Private Sub Worksheet_Change_Paste(ByVal Target As Range) If Not Application.CutCopyMode = False Then ShowWarningMessage "paste" End If End Sub Option Explicit Sub AddNewRowToTable() Dim ws As Worksheet Dim tbl As ListObject Dim table_object_row As ListRow ' Set references to the worksheet and table Set ws = ThisWorkbook.Worksheets("LOA") ' Replace "Sheet1" with your sheet name Set tbl = ws.ListObjects(1) ' Replace "Table1" with your table name ' Unprotect workbook and sheets ws.Unprotect "123456" Set table_object_row = tbl.ListRows.Add table_object_row.Range(1, 1).Value = "" ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End Sub My old code that worked is below: Private Sub Workbook_Open() Dim ws As Worksheet Dim wsRef As Worksheet Dim lastRow As Long Dim lastRowRef As Long ' Set worksheet references Set ws = ThisWorkbook.Sheets("LOA") Set wsRef = ThisWorkbook.Sheets("References") ' Unprotect workbook and sheets ThisWorkbook.Unprotect "123456" ws.Unprotect "123456" wsRef.Unprotect "123456" ' Sort data on LOA sheet lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ws.Sort.SortFields.Clear ws.Sort.SortFields.Add Key:=ws.Range("C9:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ws.Sort .SetRange ws.Range("A9:AL" & lastRow) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Set up data validation for Employee Name lastRowRef = wsRef.Cells(wsRef.Rows.Count, "A").End(xlUp).Row With ws.Range("C9:C" & lastRow).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=References!$A$2:$A$" & lastRowRef End With ' Set formulas for columns D-G ws.Range("D9:D" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",2,FALSE)" ws.Range("E9:E" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",3,FALSE)" ws.Range("F9:F" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",4,FALSE)" ws.Range("G9:G" & lastRow).Formula = "=VLOOKUP(C9,References!$A$2:$G$" & lastRowRef & ",5,FALSE)" ' Find the last row with data in column C lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Set the range for column C Set dataRange = ws.Range("C1:C" & lastRow) ' Loop through each cell in column C For Each cell In dataRange If Not IsEmpty(cell) Then ' Unlock corresponding cells in A, B, and H-AL ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = False ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = False Else ' Lock corresponding cells in A, B, and H-AL ws.Range("A" & cell.Row & ":B" & cell.Row).Locked = True ws.Range("H" & cell.Row & ":AL" & cell.Row).Locked = True End If Next cell ws.Range("C9:C" & lastRow).Locked = False ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True ' Protect References sheet wsRef.Protect "123456" ' Protect workbook ThisWorkbook.Protect "123456" End Sub Option Explicit Private Const WARNING_MESSAGE As String = "Warning: You are attempting to {0} data." & vbNewLine & _ "That is not allowed in this spreadsheet. Please press 'ESC' to return to your work." Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler If Not Intersect(Target, Me.Range("C:C")) Is Nothing Then Application.EnableEvents = False Me.Unprotect "123456" UpdateCellLockStatus Target Me.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End If ExitSub: Application.EnableEvents = True Exit Sub ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbCritical, "Error" Resume ExitSub End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.CutCopyMode = False Then Select Case Application.CutCopyMode Case xlCut ShowWarningMessage "cut" Case xlCopy ShowWarningMessage "copy" End Select End If End Sub Private Sub UpdateCellLockStatus(ByVal Target As Range) Dim cell As Range Dim affectedRange As Range For Each cell In Intersect(Target, Me.Range("C:C")) Set affectedRange = Union(Me.Range("A" & cell.Row & ":B" & cell.Row), _ Me.Range("H" & cell.Row & ":AL" & cell.Row)) affectedRange.Locked = (cell.Value = "") Next cell End Sub Private Sub ShowWarningMessage(ByVal action As String) MsgBox Replace(WARNING_MESSAGE, "{0}", action), vbExclamation + vbOKOnly, "Data Modification Warning" End Sub Private Sub Worksheet_Change_Paste(ByVal Target As Range) If Not Application.CutCopyMode = False Then ShowWarningMessage "paste" End If End Sub Option Explicit Sub AddNewRowToTable() Dim ws As Worksheet Dim tbl As ListObject Dim table_object_row As ListRow ' Set references to the worksheet and table Set ws = ThisWorkbook.Worksheets("LOA") ' Replace "Sheet1" with your sheet name Set tbl = ws.ListObjects(1) ' Replace "Table1" with your table name ' Unprotect workbook and sheets ws.Unprotect "123456" Set table_object_row = tbl.ListRows.Add table_object_row.Range(1, 1).Value = "" ' Protect LOA sheet ws.Protect "123456", UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True End Sub I'm also trying to add code that will delete all of the data in a row (without removing data validations or formatting) when the data in Column C is deleted. I tried the following code, but I wasn't sure how to implement it. Option Explicit Private Const WARNING_MESSAGE_DEL As String = "Warning: You are attempting to delete data in Column C." & vbNewLine & _ "This action will clear the entire row. Do you want to proceed?" Private Sub Worksheet_BeforeDelete(ByVal Target As Range, Cancel As Boolean) Dim intersectRange As Range ' Check if the deletion involves Column C Set intersectRange = Intersect(Target, Me.Columns("C")) If Not intersectRange Is Nothing Then If MsgBox(WARNING_MESSAGE_DEL, vbExclamation + vbYesNo, "Deletion Warning") = vbNo Then Cancel = True End If End If End Sub Private Sub ClearRowExceptColumnC(ByVal rowNumber As Long) ' Clear cells to the left of Column C Me.Range("A" & rowNumber & ":B" & rowNumber).ClearContents ' Clear cells to the right of Column C Me.Range("D" & rowNumber & ":" & Me.Cells(rowNumber, Columns.Count).Address).ClearContents End Sub I'm very much a novice when it comes to VBA, but I love the customizability and am trying to learn more. If you have any other advice it would be very much appreciated!
#NAME? error continues to appear
I am constantly getting #NAME? error on files that appear to have correct formulas. This generally happens if I work on a file in the browser and then open it in excel. Example below shows an xlookup formula error. The formula is referencing a tab in the same workbook. This also happens on other formulas. Ive never seen this happen before until recently. Is there something I can do to prevent this from happening?
Counting unique values with multiple criteria
I'm a pretty basic Excel user, and trying to gather some data about our application's clients. Suppose I have this: MONTH IRRELEVANT1 CLIENT IRREL2 SITE STATUS JAN X 1 Y 101 ACTIVE JAN X 1 Y 102 CLOSED JAN X 2 Y 201 ACTIVE JAN X 3 Y 301 CLOSED JAN X 3 Y 401 ACTIVE JAN X 4 Y 402 CLOSED JAN X 4 Y 403 ACTIVE JAN X 4 Y 405 CLOSED JAN X 4 Y 406 ACTIVE JAN X 5 Y 501 ACTIVE FEB X 1 Y 103 CLOSED FEB X 2 Y 201 ACTIVE FEB X 3 Y 301 CLOSED FEB X 2 Y 202 ACTIVE FEB X 1 Y 102 CLOSED FEB X 2 Y 203 ACTIVE MAR X 3 Y 302 ACTIVE MAR X 4 Y 402 CLOSED MAR X 5 Y 502 ACTIVE MAR X 6 Y 601 CLOSED MAR X 4 Y 403 ACTIVE MAR X 3 Y 305 CLOSED MAR X 2 Y 205 ACTIVE MAR X 1 Y 102 ACTIVE MAR X 2 Y 201 CLOSED MAR X 3 Y 302 ACTIVE (Note the "irrelevant" columns interspersed) Questions I want to answer: By month, what are the number of clients that have at least 1 active site By month, what are the number of active sites (across all clients) Every Monday, I'm going to be adding in about 10,000 rows. I had something working for both of these that was using a FILTER with multiple criteria (matching on MONTH and matching on STATUS=ACTIVE) but after about 3 months, when I load in new data, Excel runs out of resources with those filters. So that's not gonna work. I got kind of close with this: =COUNTIFS(C2:C27, UNIQUE(C2:C27), A2:A27, "JAN", F2:F27, "ACTIVE") That generates a spill range: 1 1 1 2 1 0 I don't actually understand how the first 2 parameters work, TBH. Output seems to be saying "client 1 occurs 1 time, 2 occurs 1 time, 3 occurs 1 time, 4 occurs 2 times, 5 occurs 1 time, 6 occurs 0 times). So it's close...Note that I cannot do this: =COUNT(COUNTIFS(C2:C27, UNIQUE(C2:C27), A2:A27, "JAN", F2:F27, "ACTIVE")). I have to generate the results into a table, and then count that. Not ideal. Any Excel wizards out there able to point me in the right direction?
Send emails from a multiple user spreadsheet
Hi I'm not a proficient VBA user so apologies in advance if my request isn't clear. We have a spreadsheet that users can log 'issues' into. The spreadsheet is on SharePoint and is not check in/out, to allow multiple users to update/add new rows at the same time. I've been requested to set up a macro that will send out an email each time when either a new row (issue) is logged or a previous issue has been updated by determining the 'status' column changes. For example, if a new issue is logged the 'status' is set the 'New', send an email to the issue owner, but if an issue status on a different row is then set to 'In-Progress', send another email to perhaps a different issue owner. Owners are also logged on the spreadsheet so easy to pickup. Sounds simple but here are my concerns. My code identifies when data on a row has been updated but how do I identify when a particular cell has been updated? I have written some code that on the event 'BeforeSave', however the spreadsheet is set to AutoSave therefore sends the emails multiple times before the users has finished adding all the columns of data. Is there a different event that can be used or should I be looking for a different option for sending emails? If the user has started to enter a row (issue) but they haven't yet entered their email address as the owner, how do I delay sending the email until the row of information has been fully completed. Many thanks for any advice. All advice appreciated.
Power Pivot Tables not Refreshing
I have a problem with Power Pivot in Excel. When I try to refresh the table it hangs and never completes. It was working fine yesterday. I have tried the following: 1. Check for Data Source Issues Ensure that the source data is accessible and hasn't changed (e.g., moved, renamed, or permissions altered). 2. Check for Large Data Volume or Memory Limit Try refreshing a smaller dataset first (e.g., filter down data in the source before loading). 3. Restart Excel & Try Again Close Excel completely (including any background processes in Task Manager) and reopen it.
Auto-Populating Data From One Sheet To The Other
My structure is 2 separate tabs in the excel sheet. In one sheet, my "Devops Tasks" sheet, I have a status of "COMPLETE", and currently, everything besides 2 are set to "INCOMPLETE". When I set the status to "COMPLETE" my formula populates this data over here to my other sheet which is labeled as "Handover Report" within my "Completed DevOps Tasks This Shift": Right now though, it only populates as #NAME? - What exactly am I doing wrong here in this formula? =@IF(COUNTIF('DevOps Tasks'!C[6],"COMPLETE")>=4,INDEX('DevOps Tasks'!C[2],@AGGREGATE(15,6,@ROW('DevOps Tasks'!C[6])/(@'DevOps Tasks'!C[6]="COMPLETE"),4)),"") As a side note, but maybe not entirely relevant - one way I'm auto-populating is through what I do within JS: const completedTasksTable = handoverSheet.addTable({ name: 'CompletedTasks', ref: 'A' + (handoverSheet.rowCount + 1), columns: [ { name: 'ID', filterButton: true, width: 15 }, { name: 'Title', filterButton: false, width: 50 }, { name: 'Completed By', filterButton: true, width: 25 }, { name: 'Sprint', filterButton: true, width: 20 } ], rows: Array(10).fill().map((_, index) => [ { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!C:C,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!D:D,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!B:B,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` }, { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,"COMPLETE")>=${index + 1},INDEX('DevOps Tasks'!A:A,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H="COMPLETE"),${index + 1})),"")` } ]) }); const completedTasksHeaderRow = handoverSheet.getRow(completedTasksTable.headerRow?.firstRow || +1); if (completedTasksHeaderRow) { completedTasksHeaderRow.font = { bold: true }; completedTasksHeaderRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF0F5FF' } }; } completedTasksRow.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1E4D8C' } }; completedTasksRow.font = { color: { argb: 'FFFFFFFF' }, bold: true };
Dealing with VSTACK with empty arrays
Hi all Question - do you know how to still display data when there is an empty array within VSTACK? Currently my formula works but displays CALC when an array is empty. I have two tables, both with data filtered based on a particular month. The spreadsheet example works but needs maybe LET & ISERROR to ensure one empty array of results still displays the remainder. Can anyone advise please? =IFERROR(SORT(VSTACK(FILTER(FILTER(A3:E6,(D3:D6<=C9)*(E3:E6>=C9)),{1,1,0,1,0}),FILTER(FILTER(H3:L6,(K3:K6<=C9)*(L3:L6>=C9)),{1,1,0,1,0})),3,1,FALSE),"ERROR") Thank you
